Dlookup a GUID field...

[rhic]

I'm having trouble reading GUID datatype columns with the Dlookup function.

For example:

dim vartest as variant

vartest = stringfromguid (dlookup("someguidcolumn", "sometable", "somecriteria"))

I keep getting '????????...' as a result.

Anyone have an idea?

 

[rhic]

Anybody of you guru's?

 

[wadb]

Check this out:

http://support.microsoft.com/?scid=kb%3Ben-us%3B282977&x=8&y=11

 

[Calvin]

belated 2-cents worth

I was searching for an example of StringFromGUID to better explain to a co-worker/programmer and was surprised to only find this one thread with no resolution.

So here's my 2-cents worth, belated as it is over 2 years later, but thought an answer would be good for the archives.

Private Function LongExplanation()

    'GUID's (UniqueIdentifier) stored in SQL Server are stored in binary format and when
    'passed to MSAccess (ADP project) they are naturally converted to a Byte(16) Array
    'if you put a Watch on the field value Me![GUID] you can see the array.
    
    'This is why there are the two functions StringFromGUID() and GUIDFromString() in VBA,
    'the former (StringFromGUID) is used to help read the Guid from the field,
    'the latter (GUIDFromString) is used to help write a string (properly formatted)
    'to the field.
    
    
    'use the StringFromGUID function to read the GUID and convert/pass to a string
    Dim MyGUID As String
    MyGUID = StringFromGUID(Me![Guid])

    'the above will actually return the string with a little junk in it,
    'which is probably why some people have problems with it.
    'EXAMPLE: MyGUID = "{guid {E6919771-152B-463D-AC48-FFA22AB088FC}}"
    
    'I've found that to use in an ADP project you simply need to trim the junk of both ends of the string
    MyGUID = Mid(MyGUID, 8, 36) 'staring at the 8th position, return 36 characters

    'the string will now contain the following
    'EXAMPLE: MyGUID = "E6919771-152B-463D-AC48-FFA22AB088FC"

    'the string MyGUID can now be used in Form.Filter or Form.RecordSource properties, or others

    'Example One: Open Form with filter
    stLinkCriteria = "[GUID]='" & MyGUID & "'"
    DoCmd.OpenForm "MyForm", , , stLinkCriteria

    'Example Two: Set Form.RecordSource
    DoCmd.OpenForm "MyForm"
    Forms![MyForm].RecordSource = "Select * From MyTable Where [GUID]='" & MyGUID & "'"

End Function

Private Function InShort()

    Dim MyGUID As String
    MyGUID = StringFromGUID(Me![Guid])

    MyGUID = Mid(MyGUID, 8, 36)

    DoCmd.OpenForm "MyForm"
    Forms![MyForm].RecordSource = "Select * From MyTable Where [GUID]='" & MyGUID & "'"

End Function

 

[mokingham]

Hi, I had the exact same problem. I tried using the StringFromGUID() function mentioned in other forums, but this result was still ????????

'Here is what I was trying:
Dim userGuid As String
userGuid = StringFromGUID(DLookup("[userGUID]", "Users", "[UserID]=" & G_USerID))
'This always returned '????????' instead of a GUID

So, instead, I opened a DAO recordset and pulled the GUID out that way and it worked fine. The resulting GUID comes out a little funny, so I had to trim some charaacters from the front and back, but it worked!

Dim MySet As Recordset
Set MySet = CurrentDb.OpenRecordset("Select userGUID from USERS where UserID = " & G_USerID, dbOpenDynaset, dbSeeChanges)
userGuid = MySet!userGuid
'At this point, the String userGuid looks like this
'{guid {94EB9C32-5WCD-4287-A704-8348A24C580E}}
'so next I had to trim the front and back parts a little
userGuid = Right(userGuid, Len(userGuid) - 6)
userGuid = Left(userGuid, Len(userGuid) - 1)
MySet.Close

In my case, I was using Access 2000 and the table was a linked SQL Server table.

I hope that helps someone else stuck on this problem!

 

[JFuhr00000]

WORKAROUND-
Create a query like this...
My_GUID SQL:
SELECT Table.fname, Table.theGuid
FROM Table
WHERE (((Table.fname)=[Forms]![Profile]![UserID]));

Then use:
DFirst("[theGuid]","My_GUID")

instead of doing the DLookup()