Lookup function not working

[jayC]

I am creating customer invoices using a form bound to a query called
"Invoices Query". Invoices Query is bound to the "Customer Table".

I would like to use the Lookup function in a text box to automatically fill
in the billing date on the form for each customer.
I created a table called "DATATABLE" that has a field called IN DATE and a
field called BILLINGDATE. The "Invoices Query" query also contains a field
called IN DATE. The text box is supposed to show the data in the
BILLINGDATE field based on the corresponding data in the IN DATE field for
each customer.
This is what I inputed (Access automatically inserted brackets in some parts
of the statement):


=DLookUp("[BILLINGDATE]","[DATATABLE]","[IN DATE] =" & [Forms]![Invoices
Query]![IN DATE])

The above is returning #NAME? error when the First 10 RecordsPreview is
clicked.

 

[Klatuu]

You formatting of the criteria is for a numeric field. For a date field, you
need to use the # as delimiters:

=DLookUp("[BILLINGDATE]","[DATATABLE]","[IN DATE] = #" & [Forms]![Invoices
Query]![IN DATE]) & "#"

BTW IN DATE is not a good name. You should not use spaces in names.

 

[jayC]

"In Date" is a text field since I am using my own code. BILLINGDATE is
also a text field, though I could change it to a date field.
What would be the expression for a text field?

Thanks

You formatting of the criteria is for a numeric field. For a date field,
you
need to use the # as delimiters:

=DLookUp("[BILLINGDATE]","[DATATABLE]","[IN DATE] = #" & [Forms]![Invoices
Query]![IN DATE]) & "#"

BTW IN DATE is not a good name. You should not use spaces in names.


--
Dave Hargis, Microsoft Access MVP

I am creating customer invoices using a form bound to a query called
"Invoices Query". Invoices Query is bound to the "Customer Table".

I would like to use the Lookup function in a text box to automatically
fill
in the billing date on the form for each customer.
I created a table called "DATATABLE" that has a field called IN DATE and
a
field called BILLINGDATE. The "Invoices Query" query also contains a
field
called IN DATE. The text box is supposed to show the data in the
BILLINGDATE field based on the corresponding data in the IN DATE field
for
each customer.
This is what I inputed (Access automatically inserted brackets in some
parts
of the statement):


=DLookUp("[BILLINGDATE]","[DATATABLE]","[IN DATE] =" & [Forms]![Invoices
Query]![IN DATE])

The above is returning #NAME? error when the First 10 RecordsPreview is
clicked.