dlookup problem

[Dave]

Hi,
I have a table named tblWeek with 2 fields: Week (text) and FirstDate
(Date/Time).
I have a form which calculates the first date of a week based on a users
input, then displays the corresponding week from tblWeek based on the result
in field [txtFDOW].
The dlookup statement is structured as:

DLookup("[Week]", "tblWeek", "FirstDate = #" & [txtFDOW] & "#")

In some cases I get a result, but in most, it is null. I know there is a
matching date in the table for these null results, but it's not showing up on
the form. I've tried me.repaint, me.requery, me.refresh and me.recalc all to
no avail...

Can anybody point me in the right direction please?
Thanks in advance.
Dave

 

[Dave]

PS. I forgot to note that the matches returned are ones where MM/DD/YY
matches an equivelent DD/MM/YY. For example 9-May-04 returns the week for
5-SEPT-04 because they both appear in the table. 12/12/04 works too. It
appears as though the function translates the date to MM/DD/YY format - not
what I want to happen.
Is there a way around this?

 

[Wayne Morgan]

Access uses MM/DD/YY when it does a comparison such as this. You can enter
the data the other way in the textbox, but you'll need to format it for the
DLookup. This is required in queries, I'm guessing it is required in DLookup
also. I'm in the US, so my settings are mm/dd/yyyy already.

Try:
DLookup("[Week]", "tblWeek", "FirstDate = #" & Format([txtFDOW],
"mm/dd/yyyy") & "#")

 

[Dave]

Thanks Wayne.
I tried the formatting in a different spot which didn't work. Yours worked a
treat.
Regards,
Dave

Access uses MM/DD/YY when it does a comparison such as this. You can enter
the data the other way in the textbox, but you'll need to format it for the
DLookup. This is required in queries, I'm guessing it is required in DLookup
also. I'm in the US, so my settings are mm/dd/yyyy already.

Try:
DLookup("[Week]", "tblWeek", "FirstDate = #" & Format([txtFDOW],
"mm/dd/yyyy") & "#")

--
Wayne Morgan
MS Access MVP

PS. I forgot to note that the matches returned are ones where MM/DD/YY
matches an equivelent DD/MM/YY. For example 9-May-04 returns the week for
5-SEPT-04 because they both appear in the table. 12/12/04 works too. It
appears as though the function translates the date to MM/DD/YY format -
not
what I want to happen.
Is there a way around this?