Dlookup with Date/Time datatype

[gwunta1907]

hi guys, I'm trying to do a DLookup specifying two criteria. The first criterion is a text field an the second is a Date field. Im sure I have the syntax wrong as the error Im getting is a Type mismatch error. Ive checked both tables where I am comparing data and they are both set to Date/Time datatypes. The current syntax I have is:

Me.exin = DLookup("USD_Equivalent", "tbl_rated", "Currency Like """ & Me.Currency_Name & """" And "Dated Like """ & Me.SailingDate & """")

Do I just replace the amphersands &'s surrounding Me.SailingDate or is there more to is than that?

Many thanks in advance

 

[pbaldy]

This should help:

http://www.mvps.org/access/general/gen0018.htm

 

[grzzlo]

Me.exin = DLookup("USD_Equivalent", "tbl_rated", "Currency Like """ & Me.Currency_Name & """" And "Dated Like """ & Me.SailingDate & """")

should be

Me.exin = DLookup("USD_Equivalent", "tbl_rated", "Currency = """  & Me.Currency_Name & """ And Dated = #" &  Me.SailingDate & "#")

let me know if that works

 

[pbaldy]

let me know if that works

Deleted, due to your edit.

 

[gwunta1907]

Yeah I had a look at that before I posted pbaldy, but still having problems.

I tried this:

Me.exin = DLookup("USD_Equivalent", "tbl_rated", "Currency Like """ & Me.Currency_Name & """" AND "Dated = # Me.SailingDate #")

But still get a type mismatch error

 

[pbaldy]

grzzlo has given you your fish.

 

[grzzlo]

Deleted, due to your edit.

Yeah, I didn't notice all those extra quotes the first time around...

 

[gwunta1907]

Success!! Thankyou grzzlo, I must having been writing my previous post while you were posting. Thankyou again for your help, the learning curve is steep at the moment for me haha

 

[John Big Booty]

Dates need to be surround by the date identifier (#), so your DLookup should look like;

Me.exin = DLookup("USD_Equivalent", "tbl_rated", "Currency Like '" & Me.Currency_Name & "' And Dated Like #" & Me.SailingDate & "#")

 

[John Big Booty]

Too slow