Date Problem: 5th Jan becomes 1st May (1 Viewer)

[hooi]

Hi,

I've a Macro with a conditional check for a date entered by user on a form ([ServiceDate]), which checks againsts a table (uHoliday) which has a date field [HolidayDate]:

DLookUp("[HolidayDate]","uHoliday","[HolidayDate]= #" & [ServiceDate] & "#") Is Not Null

The HolidayDate is a Date field in Short Date format.

I have an entry in the uHoliday table for 1st May 2004 as a public holiday, which is saved in the table as 1/5/2004.

The problem is that when I enter a date such as 5/1/2004 (ie. 5th January), the system assumes that it is 1st May 2004. Again if I try to enter 1/5/2004 (ie 1st May), the system assumes that it is 5th January 2004.

What should I do? Thanks for helping.

 

[Mile-O]

What should I do?

Change your regional settings to reflect the format you want.

 

[hooi]

My regional setting is already set to ddmmyyyy actually, that's why it is so puzzling

 

[Rich]

DLookUp("[HolidayDate]","uHoliday","[HolidayDate]= #" & Format([ServiceDate],"mm/dd/yyyy") & "#") Is Not Null

 

[hooi]

Oops! my conditional check should be:

DLookUp("[HolidayDate]","uHoliday","[HolidayDate]= #" & Format([ServiceDate],"mm/dd/yyyy") & "#")

Problem remains though...

 

[hooi]

Resolved at last, the solution is to change the Regional Options to Short Date Format of: dd-MMM-yy.