DLookUp syntax problem

[bearatc]

I want to return a value from a separate query and have that show up in a field on my main form. The field name from the query is "Six Month Date" and the query name is "DT6Monthqry". I want to return the value displayed in the"Six Month Date" from the query, matching the CustomerID in the query result to the CustomerID that is being displayed on the main form. The CustomerID is a text field.

This is the code I placed in the Control Source property for the field I want to be displayed on the main form:

=DLookUp("[Six Month Date]","DT6Monthqry","CustomerID=’" & [CustomerID])

The result of this displays a flashing "Error" in the field, even when I go to a Customer that I know has a result in the "DT6Monthqry".

I know it is something simple, but it's driving me crazy. Any suggestions/

 

[pbaldy]

This should help:

http://www.mvps.org/access/general/gen0018.htm

 

[Solo712]

I want to return a value from a separate query and have that show up in a field on my main form. The field name from the query is "Six Month Date" and the query name is "DT6Monthqry". I want to return the value displayed in the"Six Month Date" from the query, matching the CustomerID in the query result to the CustomerID that is being displayed on the main form. The CustomerID is a text field.

This is the code I placed in the Control Source property for the field I want to be displayed on the main form:

=DLookUp("[Six Month Date]","DT6Monthqry","CustomerID=’" & [CustomerID])

The result of this displays a flashing "Error" in the field, even when I go to a Customer that I know has a result in the "DT6Monthqry".

I know it is something simple, but it's driving me crazy. Any suggestions/

For starters, you have an extra single quote (or a missing one) in the criteria formula, i.e.
"CustomerID=’" & [CustomerID].

Best,
Jiri

 

[bearatc]

Thank you for your suggestion.

I tried adding a single quote in front of CustomerID and also removing all the single quotes and both gave me the error- #Name?

 

[bearatc]

pbaldy,

I used your suggested examples "referring to a form control" and I keep getting an invalid string/syntax error.

=DLookUp("[Six Month Date]","DT6Monthqry","[CustomerID] = '” & forms!MainTestForm!CustomerID & “’”)

 

[pbaldy]

Review the link; your value being text requires delimiters before and after.

 

[pbaldy]

The odd quotes can cause problems. Try

=DLookUp("[Six Month Date]","DT6Monthqry","[CustomerID] = '" & forms!MainTestForm!CustomerID & "'")

If that doesn't work, can you post the db here?

 

[bearatc]

That did it!!!!!!!!!!!!!!

Thank you so much, pbaldy!!!!!!

 

[pbaldy]

Happy to help!