Dlookup Error.

[Mick Peters]

Sorry if this has been asked before but I cannot get the syntax right for this DLookup. I am at the start of this project converting something I have done in Excel to Access. I have checked several threads but just can't get it right. It does not error when I check the compile but errors when it fires (After Update).
I have a form called frm_input on that form all fields are unbound, Date,Time ,Employee and Location. I have a field called employee where I enter a name and a field named Location where I want to have the VBA (Dlookup) fill in the location of the employee from the table T_Employee.
The table called T_Employee has 5 fields. One is Employee and one is Location.
The VBA I have is

Me.Scan_Date.Value = Date
Me.Scan_Time.Value = Time
Me.Location.Value = DLookup("Location", "T_Employee", "Employee = " & Forms![frm_Input]![Employee])
End Sub

This fills in the Date and time but fails on the DLookup
Any help would be appreciated.
Thanks,
Mick.

 

[xenou]

If you are entering a name then probably its a string value ('David','Ellen','Marco','Misha'). If so, then all string literals have to be enclosed in quotes for your DLookup where criteria (which is basically a SQL where clause without the where keyword):

Me.Location.Value = DLookup("Location", "T_Employee", "Employee = '" & Forms![frm_Input]![Employee] & "'")

 

[Mick Peters]

If you are entering a name then probably its a string value ('David','Ellen','Marco','Misha'). If so, then all string literals have to be enclosed in quotes for your DLookup where criteria (which is basically a SQL where clause without the where keyword):

Me.Location.Value = DLookup("Location", "T_Employee", "Employee = '" & Forms![frm_Input]![Employee] & "'")

That worked perfect Thank you

Still not sure what I was missing though was it just the &"'" at the end?
I think I understand the consept.
DLOOKUP( "Name of field in the table where result comes from", "Name of table where to look", "What I am looking for in the table to match" & thefield on the form"'"

Is that correct?
Thank you again
Mick.

 

[xenou]

Hi,
your understanding of DLookup is correct but just keep in mind that the syntax is similar to raw SQL and basically that all the arguments in DLookup functions are merely strings (even if you are creating those string based on form control values).

So, column names or table names that have spaces in them should be in square brackets, and (in particular) in the "WHERE" part you must follow two rules:

raw strings enclosed in quotes
raw dates enclosed in octothorpes

Example:
FieldZ is a string: DLOOKUP("FieldX","TableY","FieldZ = 'abc'")
FieldZ is a date: DLOOKUP("FieldX","TableY","FieldZ = #1/1/2017#")
FieldZ is a number: DLOOKUP("FieldX","TableY","FieldZ = 1")

 

[Mick Peters]

Hi,
your understanding of DLookup is correct but just keep in mind that the syntax is similar to raw SQL and basically that all the arguments in DLookup functions are merely strings (even if you are creating those string based on form control values).

So, column names or table names that have spaces in them should be in square brackets, and (in particular) in the "WHERE" part you must follow two rules:

raw strings enclosed in quotes
raw dates enclosed in octothorpes

Example:
FieldZ is a string: DLOOKUP("FieldX","TableY","FieldZ = 'abc'")
FieldZ is a date: DLOOKUP("FieldX","TableY","FieldZ = #1/1/2017#")
FieldZ is a number: DLOOKUP("FieldX","TableY","FieldZ = 1")

Many Thanks , I will endeavor to keep that in mind going forward. thank you again for your help and explanation,
Mick.