dlookup not working with certain dates

russiver

Registered User.
Local time
Today, 08:19
Joined
Dec 19, 2003
Messages
41
Can't figure out why dlookup will not work for me in a query for certain dates?

Simple example to highlight the problem:

Table1 -

Read_Date----Read_Value
31/07/14-------10
01/08/14-------20
03/08/14-------30
20/08/14-------40

Query based on this table with the following calculated field:

Expr1: DLookUp("[Read_value]","Table1","Read_Date=#" &[Read_Date]& "#")

Query output -

Read_Date-----Expr1
31/07/14--------10
01/08/14
03/08/14
20/08/14--------40

Will not return a value for 01/08/14 or 03/08/14 (dd/mm/yy)

If you try it with many dates its skips several and I cannot see a pattern.

Can anyone advise where I'm going wrong?

Thanks,

Russ
 
Try both:
Code:
Expr1: DLookUp("[Read_value]","Table1","Read_Date = " & [Read_Date])

Expr1: DLookUp("[Read_value]","Table1","Read_Date = " & Format([Read_Date], "\#mm\/dd\/yyyy\#"))
By the way Table1 could be given a more meaningful name.
 
Many thanks and take your point about the name, but it was only an example.

I had tried with "Read_Date = #" & Format([Read_Date], "dd/mm/yy") &"#"), but with no luck.

I guess the problem lies in getting the date format acceptable when passed over to SQL. Why do you need the back slashes as well?
 
Have you tried exactly what I wrote in my last post?
 
Apologies, did not make myself that clear.
Yes I have tried your solution and it works perfectly. Many thanks.

I was just trying to understand your format argument in the format function "\#mm\/dd\/yyyy\#", particularly the back slashes.
 
Oh ok. It forces the character after it to be taken as a string literal and dates in Access are in the US format so when searching it's best to use that format.
 

Users who are viewing this thread

Back
Top Bottom