Date problems

[yhgtbfk]

No, this isnt about problems with my romance life.

I am having strange date problems.

The code is as follows:

currpost = DLookup("currpostdate", "currpost")

Do While 1 = 1
checkalloc = DLookup("allocationID", "allocation", "nightrent < #" & CDate(currpost) & "# AND status = 'current'")

....etc...

The following works:

currpostdate = 1/01/2005
nightrent (3 records) = 1/12/2004, 1/12/2002, 3/12/2004

The following doesnt work:

currpostdate = 1/02/2005
nightrent (3 records) = 5/01/2005, 5/01/2005, 7/01/2005

There may be a possibility it is reading it as an american date

But when I manually type in 1-Feb-2005, it automatically converts it to 1/02/2005

Any ideas?

 

[gecko_1]

Try formatting the date in the DLookup.

checkalloc = DLookup("allocationID", "allocation", "nightrent < #" & format(CDate(currpost),"dd mmm yyyy") & "# AND status = 'current'")

I do the same thing when saving dates, so that I dont get a US date which stuffs things up.

Todd

 

[yhgtbfk]

Try formatting the date in the DLookup.

checkalloc = DLookup("allocationID", "allocation", "nightrent < #" & format(CDate(currpost),"dd mmm yyyy") & "# AND status = 'current'")

I do the same thing when saving dates, so that I dont get a US date which stuffs things up.

Todd

That worked perfectly. Thank you