dlookup not working with certain dates

[russiver]

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

 

[vbaInet]

Try both:

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.

 

[russiver]

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?

 

[vbaInet]

Have you tried exactly what I wrote in my last post?

 

[russiver]

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.

 

[vbaInet]

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.

 

[russiver]

I'm with you now...

Thanks for clearing this up.

 

[vbaInet]

No problemo