Format criteria for text field

[jayC]

I am writing an text box expression in a report as follows:

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

The field INDATE is a text field. BILLINGDATE is a text field also. What
delimiter do I need to add to the statement to make it work on a text field?

I am getting garbage out. Must I change it to a numeric or date field to
make it work?

 

[M.L. Sco Scofield]

jayC,

One has to ask why you are storing dates in a Text field instead of a
Date/Time field.

If you must keep the dates in a Text field, the format of the dates needs to
be the same or you won't get a match. This is why a Date/Time field is
preferred. The display format doesn't matter. Access will look for the
internal representation of the date for the match.

Try:
= DLookup("[BILLINGDATE]", "[DATATABLE]", "[InDate] = """ &
[Forms]![InvoicesQuery]![InDate]) & """"

If you change InDate to a Date/Time field, you would use:
= DLookup("[BILLINGDATE]", "[DATATABLE]", "[InDate] = #" &
[Forms]![InvoicesQuery]![InDate]) & "#"

Good luck.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005
Denver Area Access Users Group Past President 2006/2007 www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
This email made with 100% recycled electrons!
Miscellaneous Access "stuff" at www.ScoBiz.com

 

[fredg]

I am writing an text box expression in a report as follows:

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

The field INDATE is a text field. BILLINGDATE is a text field also. What
delimiter do I need to add to the statement to make it work on a text field?

I am getting garbage out. Must I change it to a numeric or date field to
make it work?

Well, if the [InDate] field in the "DataTable" is a text datatype
field, what does the text look like?
"2/20/2008" or "Feb. 20, 2008" etc.
If it was a Date datatype then the format wouldn't matter.
So the question is, why isn't the [InDate] field a Date datatype?

 

[jayC]

I am getting invalid syntax message when entering the first statement. ( I
tried taking out all the spaces- except for before and after the "&" -and
still no luck.)

Where in Access help can I find the information about the delimiters to
change the expression for a text field? I cannot locate it

jayC,

One has to ask why you are storing dates in a Text field instead of a
Date/Time field.

It is not a date. It is a code for a date that I use.

If you must keep the dates in a Text field, the format of the dates needs
to be the same or you won't get a match.

The format is the same for all.

This is why a Date/Time field is

preferred. The display format doesn't matter. Access will look for the
internal representation of the date for the match.

Try:
= DLookup("[BILLINGDATE]", "[DATATABLE]", "[InDate] = """ &
[Forms]![InvoicesQuery]![InDate]) & """"

If you change InDate to a Date/Time field, you would use:
= DLookup("[BILLINGDATE]", "[DATATABLE]", "[InDate] = #" &
[Forms]![InvoicesQuery]![InDate]) & "#"

Good luck.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005
Denver Area Access Users Group Past President 2006/2007 www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
This email made with 100% recycled electrons!
Miscellaneous Access "stuff" at www.ScoBiz.com

I am writing an text box expression in a report as follows:

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

The field INDATE is a text field. BILLINGDATE is a text field also.
What delimiter do I need to add to the statement to make it work on a
text field?

I am getting garbage out. Must I change it to a numeric or date field to
make it work?

 

[John W. Vinson]

I am writing an text box expression in a report as follows:

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

The field INDATE is a text field. BILLINGDATE is a text field also. What
delimiter do I need to add to the statement to make it work on a text field?

A single quote... and of course the InDate on the form must exactly match,
character by character. If your table InDate is "02/20/2008" and the form
control contains "2/20" or "02/20/08" or "2/20/2008" it will, of course, not
match.

Try "[InDate] = '" & [Forms]![Invoices Query]![InDate] & "'"

or... better... store date information in a date field. Don't make your own
job harder for no good reason!

 

[fredg]

I am getting invalid syntax message when entering the first statement. ( I
tried taking out all the spaces- except for before and after the "&" -and
still no luck.)

Where in Access help can I find the information about the delimiters to
change the expression for a text field? I cannot locate it

jayC,

One has to ask why you are storing dates in a Text field instead of a
Date/Time field.

It is not a date. It is a code for a date that I use.

If you must keep the dates in a Text field, the format of the dates needs
to be the same or you won't get a match.

The format is the same for all.

This is why a Date/Time field is

preferred. The display format doesn't matter. Access will look for the
internal representation of the date for the match.

Try:
= DLookup("[BILLINGDATE]", "[DATATABLE]", "[InDate] = """ &
[Forms]![InvoicesQuery]![InDate]) & """"

If you change InDate to a Date/Time field, you would use:
= DLookup("[BILLINGDATE]", "[DATATABLE]", "[InDate] = #" &
[Forms]![InvoicesQuery]![InDate]) & "#"

Good luck.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005
Denver Area Access Users Group Past President 2006/2007 www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
This email made with 100% recycled electrons!
Miscellaneous Access "stuff" at www.ScoBiz.com

I am writing an text box expression in a report as follows:

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

The field INDATE is a text field. BILLINGDATE is a text field also.
What delimiter do I need to add to the statement to make it work on a
text field?

I am getting garbage out. Must I change it to a numeric or date field to
make it work?

Not in Access help but in VBA help files:
Restrict Data to a Subset of Records
Towards the bottom of the page you'll find links to how to write the
criteria for the various datatypes.