LookUp Function Question

[jayC]

I am using forms to create customer invoices.

I would like to use the Lookup function in a text box to automatically fill
in a field on the form for each customer.

The "Invoices Query" query, on which the invoices form is based, contains a
field
called IN DATE.
I created a table called "DATATABLE" that has a field called INDATE and a
field called BILLINGDATE. The text box is supposed to show the data in the
BILLINGDATE field based on the corresponding data in the INDATE field for
each customer.

INDATE is a text field. BILLINGDATE is also a text field.

This is what I inputed:


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

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

Thanks

 

[Allen Browne]

You don't have the correct delimiters for a Text field.

You could try:
=DLookUp("[BILLINGDATE]","[DATATABLE]","[INDATE] =""" &
[Forms]![Invoices Query]![INDATE] & """")

For an explanation, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If that still fails, double-check whether you have the spaces correct. Of
course, the form must be open.

Not sure why you are using a Text field instead of a Date/Time field. You
could probably avoid the entire issue if you use a date field, as you could
calculate the desired billing date.

Alternatively, it might be possible to add the DATATABLE to the query that
feeds your report, so you can have the BILLINGDATE field available without a
lookup.

 

[jayC]

You don't have the correct delimiters for a Text field.

You could try:
=DLookUp("[BILLINGDATE]","[DATATABLE]","[INDATE] =""" &
[Forms]![Invoices Query]![INDATE] & """")

I am still not getting a correct output.

For an explanation, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If that still fails, double-check whether you have the spaces correct. Of
course, the form must be open.

I left no spaces except before and after "&".

Not sure why you are using a Text field instead of a Date/Time field. You
could probably avoid the entire issue if you use a date field, as you
could calculate the desired billing date.

InDate is a code for month. It appears as: 208-02 or 208-03, etc.. It is a
text field in all the tables.
BILLINGDATE appears as: February 1, 2008 or March 1, 2008, etc.. It could
be changed to a date field.

Alternatively, it might be possible to add the DATATABLE to the query that
feeds your report, so you can have the BILLINGDATE field available without
a lookup.

I d rather not have to manually fill in BOTH INDATE AND BILLINGDATE. I
also wanted to use the lookup function to automatically fill
in another field that relates to InDate.

Thanks

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using forms to create customer invoices.

I would like to use the Lookup function in a text box to automatically
fill
in a field on the form for each customer.

The "Invoices Query" query, on which the invoices form is based, contains
a field
called IN DATE.
I created a table called "DATATABLE" that has a field called INDATE and a
field called BILLINGDATE. The text box is supposed to show the data in
the
BILLINGDATE field based on the corresponding data in the INDATE field for
each customer.

INDATE is a text field. BILLINGDATE is also a text field.

This is what I inputed:


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

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

 

[Allen Browne]

Replies in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

InDate is a code for month. It appears as: 208-02 or 208-03, etc.. It is a
text field in all the tables.
BILLINGDATE appears as: February 1, 2008 or March 1, 2008, etc.. It
could be changed to a date field.

As far as TEXT goes, "208-02" it not the same as "February 1, 2008"
This seems to be the core reason why you are not getting the results you
want.

 

[jayC]

Not sure I understand.
So what if the two fields are different?

In what way do they have to be "the same" for the expression to work?

 

[Allen Browne]

If you have 2 text fields containing the text, with the text "jayc" in one
and "jc" in the other, and you try to join or filter one by the other, they
don't match.

In your case, you have 2 text fields that contain very different
representations of the date (as text), so it would not be surprising if they
don't return matching records.

Or perhaps I have no idea what you are talking about in this thread.

 

[jayC]

In the table DATATABLE, there are two columns (fields). One is called
INDATE and the other is called BILLINGDATE. Each row of DATATABLE would
have one entry for INDATE and one for BILLINGDATE.
I am trying to use the lookup function to lookup the info for each record in
the table 'Invoices Query.' If the record has a 208-03 in the field INDATE,
then I would like it to put the corresponding data from the field
BILLINGDATE, March 1, 2008, into the invoice. I don t see why the two
fields have to be the same format - or maybe they do and I am missing
something. They are not filtering each other or joining each other as they
are in two distinct columns. The only thing they have in common is that
they are on the same row.

Thanks

 

[Allen Browne]

Okay: the 2 parallel columns in the same table don't need to match.
You're right about that.

They do need the quote delimiters suggested in the earlier reply.
Not sure what else is causing a problem here.

 

[Evi]

Why are so many people now using text fields for dates? Just puzzled.

Evi

 

[jayC]

Thanks for your webpage. It is helpful.

You don't have the correct delimiters for a Text field.

You could try:
=DLookUp("[BILLINGDATE]","[DATATABLE]","[INDATE] =""" &
[Forms]![Invoices Query]![INDATE] & """")

For an explanation, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If that still fails, double-check whether you have the spaces correct. Of
course, the form must be open.

Not sure why you are using a Text field instead of a Date/Time field. You
could probably avoid the entire issue if you use a date field, as you
could calculate the desired billing date.

Alternatively, it might be possible to add the DATATABLE to the query that
feeds your report, so you can have the BILLINGDATE field available without
a lookup.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using forms to create customer invoices.

I would like to use the Lookup function in a text box to automatically
fill
in a field on the form for each customer.

The "Invoices Query" query, on which the invoices form is based, contains
a field
called IN DATE.
I created a table called "DATATABLE" that has a field called INDATE and a
field called BILLINGDATE. The text box is supposed to show the data in
the
BILLINGDATE field based on the corresponding data in the INDATE field for
each customer.

INDATE is a text field. BILLINGDATE is also a text field.

This is what I inputed:


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

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