GUID value is ??? problem

  • Thread starter Thread starter ToniS
  • Start date Start date
T

ToniS

I am trying to do a simple select and part of the selection criteria is a
GUID column from a SQL table. How do I get the value and not the ??? Below
is what I have

Strsql = "SELECT ExhibitorAddressID " & _
"FROM ShowPacketsSent " & _
"WHERE ExhibitorAddressID = " & Me.TxtExhAddressID.Value

I have this text field on the screen and for debugging purposes I have the
visible property set to TRUE, the correct information displays on the form.
When I do a msgobx of the above select statement the value of
me.txtexhaddressID.value is ????

I also tried the following: me.txtExhAddressID
and formName.txtExhAddressID.value
and FormName.TxtExhAddressID

Any ideas on what I need to do to get the correvt value?

thanks
ToniS
 
The Guid are binary values of 16 bytes long (Dim g(15) as Byte, starting
from index 0) and as such, cannot be displayed directed. In VBA, you must
convert them to a string using the StringFromGUID function:

Msgbox StringFromGUID (Me.TxtExhAddressID)

which will give you a string of the following form:

{guid{937C9DEC-4BDB-49B6-BF6B-3F6385289CE4}

Some JET calls will take this canonical format directly; however, when
dealing with SQL-Server in ADP, I suppose that you must use the standard way
of writing a GUID in SQL-Server by stripping the {guid{ and the } and
enclosing the result between single quotes:

select * from testGuid where GuidValue =
'937C9DEC-4BDB-49B6-BF6B-3F6385289CE4'

or by using the hexadecimal format:

select * from testGuid where GuidValue =
0xEC9D7C93DB4BB649BF6B3F6385289CE4

You can convert a GUID to its hexadecimal form by converting it to a
varbinary(16):

select Convert (varbinary (16), GuidValue) from ....

To convert a string back to a GUID in VBA, use the GUIDFromString VBA
function. Using GUIDs in their native form is a lot of trouble, so some
people prefer to convert them to a string of 32 characters:

select Replace (newid(), '-', '')

This has the effect of doubling the space requirement from 16 bytes to 32
bytes and will slightly reduce the speed of your requests. To compensate
for that and make things even simpler, other people are not ashamed of
cutting the resulting string in half.
 
Thank you so much for all of the information!! I plan to try your
suggestions as soon as I can!!

Thanks
Again
 
Back
Top