DLOOKUP 1

[maria28gr]

I use DLOOKUP to check the dates in a table.
I use the following:
ch = DLookup("dateholid", "tblholid", "dateholid=#" & newdate & "#&quot

Some dates doesn't seem to work, i.e. when I check the dates 06/02/2001, 08/02/2001 11/05/2001, 12/05/2001 and some others, the ch returns NULL, even though the above dates are existing in the table tblholid. Some others like 13/02/2001 or 25/12/2001 are ok.

I would like to know if somebody else has faced a similar problem and If there are any ideas on how to overcome it.

Maria28gr
maria.karakiza@usa.net

 

[jfhewitt]

Assuming your syntax is OK, I have an uneasy feeling about having the expression value field the same as the criteria field. Do you need a lookup table for this? Why not a parameter query?

 

[RickSpr]

I notice that the dates that don't work can all be valid interpreted as either mm/dd/yyyy or dd/mm/yyyy. I further notice that the successful dates can only be interpreted as dd/mm/yyyy.

Is it possible that your Windows system is not set up with dd/mm/yyyy as the standard date form? Check your Control Panel's Regional Settings applet, on the Date tab. Rick Sprague

 

[Terpsfan]

It seems as if your DLookup formula is correct, but it's either the fore-mentioned solution with your system date format, or you might want to check the date format for the field that you are basing your DLookup on. Perhaps that field is set up as dd/mm/yy.

 

[maria28gr]

I see that the above remark (dates that don't work can all be valid interpreted as either mm/dd/yyyy or dd/mm/yyyy) specifies the problem. I checked that if I ADD at the table tblholid the dates that have the problem as mm/dd/yyyy (i.e. I already have 02/06/2001 and i add 06/02/2001) DLOOKUP work fine.
Any ideas on how to solve this problem??

ps. I checked the Windows system and it is set up with dd/mm/yyyy as the standard date form. Same for the the date format of the field where I use DLookup.

maria28gr

 

[RickSpr]

I don't have experience with using European date formats, but I think all you should have to do is ensure that you always enter dates (ambiguous ones, at least) in whatever order is set in the control panel of the sytem you're using. Any dates that you've already entered that have been interpreted incorrectly you need to re-enter.

If your application is likely to be used by people who prefer different date formats, you should avoid using custom date formats in table and form Format properties. Instead, opt for the predefined formats; those automatically reflect the control panel settings. Also, review any code that processes strings containing dates. Usually, you can use DatePart to extract components of the date. DatePart takes the system date format into consideration in extracting month, day, and year. Rick Sprague

 

[maria28gr]

After checking different date formats I have end up to the following: If I have the date format as mm/dd/yyyy at the Control Panel -> Regional Settings, which means that I follow the same format in Access, then everything works perfect.
On the contrary, if I have the date format as dd/mm/yyyy at the Control Panel -> Regional Settings, then the dates in table tblholid that can be valid interpreted as either mm/dd/yyyy or dd/mm/yyyy, are interpreted from the system as mm/dd/yyyy. Therefore, if I have to enter the date 4th June 2001, I have to write it as 06/04/2001.

Maria K.

 

[RickSpr]

Maria, once a date is stored in a date/time field, it shouldn't be interpreted ambiguously any more. It will be displayed differently depending on the CP settings, but if you use the Long Date format, you should always get the same thing regardless.

What you're reporting sounds like you may be storing the holidays as text rather than date/time. That is a problem, because each time you convert it to a date (by using it in an expression, for example), the CP settings are used to interpret it. Can you convert the stored dates to date/time? (Make sure you have CP set to the format you used when entering them, before you change the data type, because Access will interpret them one last time when you do it.) Rick Sprague