Dlookup Mismatch error in access

[patso]

Hello,

Iam running the following code but it returns an error of mismatch. Staff_Name is a text, users is the table name and User_Name is a text.

GvUserName = DLookup("[Staff_Name]", "[Users]", "[User_Name]='" & Me.UserName & "'")

[zmbd]

You've stumbled upon one of my pet peeves by building the criteria string within the command - and it's not your fault because that's how a majority of examples show how to use the command.

Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.
So to use your code:

DIM strSQL as string
strSQL = "[User_Name]='" & Me.UserName & "'"
'
'now you can insert a debug print here for troubleshooting
' - press <ctrl><g> to open the immediate window
' - you can now cut and paste this information for review!
'
debug.print "Your criteria = " & strSQL
'
'now use the string in your code:
GvUserName = DLookup("[Staff_Name]", "[Users]", strSQL)
 

If you will make these little changes and post back the resolved string we can help you tweak the code.
---