DLookup Syntax Error

[Mechele]

Private Sub PatientName_AfterUpdate()
Dim varX As Variant

varX = DLookup("[GroupID]", "Data", "[SSN] = " & Me.SSN)
Me.EmployerTemp = varX

End Sub

I'm getting the following error from the above code:
"Syntax error (missing operator) in query expression "[SSN]='

What is wrong with the code?

 

[wrek]

Hi,

if SSN is non-numeric ie. String, you might want to try

varX = DLookup("[GroupID]", "Data", "[SSN] = '" & Me.SSN & "'")

(single inverted comma ' around the me.ssn variable)

 

[Mile-O]

(single inverted comma ' around the me.ssn variable)

single inverted problems can cause problems in themselves, probably not in this case (if I knew what SSN stood for) but:

varX = DLookup("[GroupID]", "Data", "[SSN] = """ & Me.SSN & """")

is my preference.

I've read Pat Hartman saying she uses a public constant called QUOTE declared thus:

Public Const QUOTE = """

Then, in this case, she would use:

varX = DLookup("[GroupID]", "Data", "[SSN] = " & QUOTE & Me.SSN & QUOTE & ")

 

[wrek]

i'll defer to the gurus

(if Mile and Pat are going with QUOTE, EVERYONE should be going with QUOTE )

didnt think of that...i guess the ASCII chr could be used as well

 

[Mechele]

Private Sub PatientName_AfterUpdate()
Dim varX As Variant
Public Const QUOTE = """
varX = DLookup("[GroupID]", "Data", "[SSN] = " & QUOTE & Me.SSN & QUOTE & ")

Me.EmployerTemp = varX
End Sub

SSN is defined as a long integer.

I'm getting the following errors:

For Public Const QUOTE = """"
The error message is: Constants, fixed length strings, user-defined types and declare statements not allowed as public members of object module.

For varX = DLookup("[GroupID]", "Data", "[SSN] = " & QUOTE & Me.SSN & QUOTE & ")
The error message is: Expected: list seperator or)


Also, when I used the following code I got an error message: Data type mismatch in criteria expression.

Private Sub PatientName_AfterUpdate()
Dim varX As Variant

varX = DLookup("[GroupID]", "Data", "[SSN] = """ & Me.SSN & """")
Me.EmployerTemp = varX
End Sub

 

[Mile-O]

Public Const QUOTE = """"

goes in a module. Not a form's Class module.




As for Data Type Mismatch, what data type is your SSN field in its original table? I suspect that it is not a Variant so you can afford to be more explicit when dimensioning the variable.

 

[Mechele]

In the Data table, SSN data type is autoNumber.
In the transaction data (where the me.ssn comes from) the data type is Number.

Is this what is causing the data mismatch?

 

[Mile-O]

Try this:

Dim lngX As Long

lngX = DLookup("[GroupID]", "Data", "[SSN] = " & CLng(Me.SSN))
Me.EmployerTemp = lngX

End Sub

Or, more simply:

Me.EmployerTemp = DLookup("[GroupID]", "Data", "[SSN] = " & CLng(Me.SSN))

 

[Mechele]

I got the error message, "Invalid use of null." using the following code:
Private Sub PatientName_AfterUpdate()
Me.EmployerTemp = DLookup("[GroupID]", "Data", "[SSN] = " & CLng(Me.SSN))
End Sub


Please don't give up on me.

 

[Mile-O]

So, we add the Nz() function as DLookup's can return Null values which, if not dealt with, generate errors.

You can replace the 0 with any value you want, should the lookup not find any information.

Me.EmployerTemp = Nz(DLookup("[GroupID]", "Data", "[SSN] = " & CLng(Me.SSN)), 0)

 

[Mechele]

I'm still getting the "Invalid use of null" error message.

If the form's underlying table is a query will this cause all the errors I'm getting? The query joins the data and transaction tables in a one-to-many relationship.

The employertemp is a unbound text box.

 

[Cosmos75]

Public Const QUOTE = """

Then, in this case, she would use:

varX = DLookup("[GroupID]", "Data", "[SSN] = " & QUOTE & Me.SSN & QUOTE & ")

varX = DLookup("[GroupID]", "Data", "[SSN] = " & Chr(34) & Me.SSN & Chr(34))

Chr(34) = "
Chr(39) = '


Would this work?