Problem Formatting Date

[rosieb]

I'm comparing two dates and I know I'm getting a mismatch between UK and US date formats - it works fine after the 12th of the month!

I can't work out the syntax to force the format

varX = DLookup("[HolDate]", "PublicHolidays", "[HolDate] = #" & strDate1 & "#")

strDate1 is in "yyyy-mm-dd" format.

I've tried everything I can think of to format HolDate similarly, but I'm missing something.




Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[hneal98]

Sorry, I don't know how to do the Code window like you did above, but here is what you can do:

varX = DLookup("[HolDate]", "PublicHolidays", "format([HolDate],"mm/dd/yyyy") = format(strDate1,"mm/dd/yyyy")"

Not sure if that is the specific format you want, but you can look in help to find the specific format.

 

[rosieb]

hneal98

Thanks, but it doesn't work , it gives a compile error "Expected: list separator or)" Which is what I seem to get whatever I try!

(The code window is just [ignore]

nnnn

[/ignore])


Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[hneal98]

Thanks for the tip. I learned something from you.

Try changing the "" to '' inside of the main quotes. Like this:

varX = DLookup("[HolDate]", "PublicHolidays", "format([HolDate],'mm/dd/yyyy') = format(strDate1,'mm/dd/yyyy')"

 

[hneal98]

Sorry, it should be like this:

varX = DLookup("[HolDate]", "PublicHolidays", "format([HolDate],'mm/dd/yyyy') = format(strDate1,'mm/dd/yyyy')")

 

[rosieb]

Thanks again, but still the same error message.

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[hneal98]

Paste the code Exactly as you have it.

 

[rosieb]

Not sure what I've done here,I changed a few things and am now getting an automation object error with your code - clearly my fault.

However, I've solved the problem by formatting strDate1 to "mm/dd/yyyy" before performing the DLookup.

But thanks for your help and patience, I wouldn't have thought of using the mm/dd/yyyy format - I was so focussed on yyyy-mm-dd.

(I'll poke around this later and work out exactly what's been happening. At the moment I think I need food!)

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[hneal98]

Ok. Let us know if you have difficulty figuring that out.