Compile Error in query expression in Access 2010

I put an internal messaging system in my access database so the users can send me (Admin) a message if need something. When I click on my frm_ReceiveMail, it gives me the Compile Error in query expression '(((TBL_Message.To)=CurrentUser()) AND ((TBL_Message.DateReceived) Is Null)'

I have a query that has To and DateReceived. The critera for the To field is CurrentUser() and DateReceived criteria is Is Null.

Does anyone know what this error is with a solution?
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
CurrentUser() is still a valid function call.  Are you building this SQL statement in code?  If not, then you most likely have broken references.  Open a module, bring up the debug window and type:

? CurrentUser()

and hit return.  You should get admin back.  If you get an error, then it's references for sure.

 If you are building this select in code, then your syntax needs to be different.

Kelvin SparksCommented:
I suspect this is the CurrentUser() bit. This always used to related to the logged in user when Access had security. The concept of users has now gone - unless you still use mdb's and mdw's.

You would need code to pickup the windows username and put that in its place.

lauriecking0623Author Commented:

How would I do this code to pick up the windows username? I am still trying to learn how to call these references in VBA?

lauriecking0623Author Commented:
@JDettman, this worked. I do not get this error. I get a new error; however, I am going post this as a new question. Thank you for your assistance.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Here's the code for getting the user name (or station name).

You'll need to add the PtrSafe for the 64 bit.


Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetUserNameA Lib "advapi32.dll" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function WhoAmI(bReturnUserName As Boolean) As String

        ' Function returns either user name or computer name

        Dim strName As String * 255

10      If bReturnUserName = True Then
20        GetUserNameA strName, Len(strName)
30      Else
40        GetComputerNameA strName, Len(strName)
50      End If

60      WhoAmI = left$(strName, InStr(strName, vbNullChar) - 1)

End Function
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.