DLookup Previous Date

[damian]

Hi

I'm on the verge of successfully using a DLookup function (with the help of previous posts) in a query in order to obtain the value (volume1) of the previous record based on a primary field which happens to be a date (iddate).

The following results in sporadic success:

Expr1: (DLookUp("[volume1]","tbldailylog","[iddate]=#" & [iddate]-1 & "#"))

The iddate field is sorted and contains consecutive days.

Any help would be greatly appreciated.

 

[damian]

I've possibly just discovered the problem - at the point where the date was 01/08/05 it returned the value for 08/01/05.

I hope the solution is in the formatting of the date - any assistance would still be appreciated.

 

[mresann]

I don't see anything wrong with your DLookup format. When you peform operations that involve dates, to avoid ambiguity between US and International formatting standards, convert the date to include a month part that is alphabetic (for instance, "Jan 7 2005*, instead of *7/1/2005" for international or "1/7/2005" for American).

 

[damian]

The date field is the primary field for the underlying table and I sort it so formatting in this way would mess up the table. I suppose I could always sort the query using a calculated value based on my date field but I'm struggling anyway to 'format' this syntax - further help would be appreciated.

 

[mresann]

I'll be honest... I rarely use a date format that can have ambiguous interpretation, such as 1/7/2005, which can be interpretated two different ways, depending if you are in London or Long Beach. In my case I would always use #Jul 1 2005# or #Jan 7 2005#, so I perhaps am not up to speed on the date problems many people have.

In your case, I'd check the regional settings on the computer, specifically the
"Short Date" setting. If you import a database from another region, such as an American DB to Britain or vice versa, in SOME cases the date formats do not automatically convert (and if the date field is a text field, it NEVER converts), so you can have problems in that fashion.

More indepth explanations are given here. Pat Hartman is very knowledgable on these matters, and I defer to her expertise.