String Variable for DLookup Criteria (1 Viewer)

LiziM

New member
Local time
Today, 02:15
Joined
Feb 3, 2021
Messages
21
Hi all :) I have learned that the syntax for passing a string variable to dlookup criteria argument is the following:
DLookup("[field-to-return]","table","[criteria-field]=' " & string-variable & " ' ") . I have tried to use this syntax to obtain "maxID" in the code below, however it returns an error: "Compile error: Syntax error". The second line for getting "maxID" does work, so I can use that, but I would prefer to use the input variable passed to the function.

"varProductSerialNumber" is taken from a form control, however, it is saved to the variable, "varProductSerialNumber", before it is passed as an input to the below function- maybe that could contribute to the error?

Code:
Public Function CheckScanOut(varProductSerialNumber As String) As Boolean 'receive ProductSerial as input and output=true/false (true if OUT_employee exisits, false if not or
Dim maxID As Long
Dim varOUTEmployee As Variant

'maxID = DMax("[Transaction_ID]", "tbl_Transaction_Master", "[ProductSerialNumber]='"&varProductSerialNumber&"'") **THIS IS THE LINE I'D LIKE TO USE THAT PRODUCES AN ERROR
maxID = DMax("Transaction_ID", "tbl_Transaction_Master", "ProductSerialNumber=[Forms]![frm_scan_IN]![tb_ProductSerialNumber].Value")
varOUTEmployee = (DLookup("[OUT_Employee]", "tbl_Transaction_Master", "[Transaction_ID]=" & maxID))
'Debug.Print (varOUTEmployee)

    If IsNull(varOUTEmployee) = True Then 'if out_employee is a null value return false
    CheckScanOut = False
    Else
    CheckScanOut = True 'elseif out_employee is not a null value return true
    End If
   
End Function

Hope you can help. Let me know if I haven't given enough info!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:15
Joined
Feb 19, 2013
Messages
13,542
no spaces either side of the &'s

'"&varProductSerialNumber&"'"

when you get this sort of error, remove some of the code until you don't get a syntax error, then rebuild

also this code

If IsNull(varOUTEmployee) = True Then 'if out_employee is a null value return false
CheckScanOut = False
Else
CheckScanOut = True 'elseif out_employee is not a null value return true
End If

can just be replaced with
CheckScanOut= not IsNull(varOUTEmployee)
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:15
Joined
Sep 21, 2011
Messages
9,276
Create a string variable and put the "[ProductSerialNumber]='"&varProductSerialNumber&"'" into that, and use that instead.
Before using it, Debug.Print the variable.
That should show you the error, if you can't see it, post back the results of the debug.print
 

LiziM

New member
Local time
Today, 02:15
Joined
Feb 3, 2021
Messages
21
no spaces either side of the &'s

'"&varProductSerialNumber&"'"

when you get this sort of error, remove some of the code until you don't get a syntax error, then rebuild

also this code

If IsNull(varOUTEmployee) = True Then 'if out_employee is a null value return false
CheckScanOut = False
Else
CheckScanOut = True 'elseif out_employee is not a null value return true
End If

can just be replaced with
CheckScanOut= not IsNull(varOUTEmployee)
Unfortunately still battling with the syntax error (I have no spaces), but that you for that other line of code- so much quicker!
 

LiziM

New member
Local time
Today, 02:15
Joined
Feb 3, 2021
Messages
21
Create a string variable and put the "[ProductSerialNumber]='"&varProductSerialNumber&"'" into that, and use that instead.
Before using it, Debug.Print the variable.
That should show you the error, if you can't see it, post back the results of the debug.print
I can see the erro... Will give this a go anyway, thanks.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:15
Joined
Feb 19, 2013
Messages
13,542
still battling with the syntax error (I have no spaces)
point is you should have spaces

'" & varProductSerialNumber & "'"
 

Users who are viewing this thread

Top Bottom