DLOOKUP casting problems: won't work with a date 2

[VBAguy22]

Hello,
I have the following code:

If Not IsNull(DLookup("DT", "HistBase", "DT='" & myDate & "'")) Then

Using it i try to check if a certain value of field DT in table HistBase exists.
This code works when DT is of type String, but it crashes when DT is of type Date (which is what it is)
I am guessing it's because you CAN'T put a date in single quotes (aka '20/01/2003').

Any ideas of how to go around it?

 

[RoyVidar]

Think you are correct there, different datatypes, different qualifiers, text are single quote, dates are hash (#), numerics, none, try:

[tt]If Not IsNull(DLookup("DT", "HistBase", "DT=#" & myDate & "#")) Then[/tt]

Roy-Vidar

 

[VBAguy22]

It still gave me Null as a return value even with # around date

Any other options?

 

[PHV]

Have you tried this ?
If Not IsNull(DLookup("DT", "HistBase", "DT=#" & Format(myDate, "mm/dd/yyyy") & "#")) Then


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RoyVidar]

Hi!

This might be a challenge relating to having regional settings different from US date format.

It might need formatting, for instance something like this:

[tt]...,"[DT]=#" & format(myDate, "yyyy-mm-dd") & "#"[/tt]

Here using ansi format. US format (mm/dd/yyyy) should also work.

Roy-Vidar

 

[VBAguy22]

Ok it worked thanks guys

would you know why I am getting a "type missmatch" on the following line

strSQL = "delete * from HistBase where Prov ='" & myProv & "' AND Dt = #" & Format(myDT, "mm/dd/yyyy") & "#"

I guess I cant put a date in SQL, can I?

 

[PHV]

What are the type and the value of myDT ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RoyVidar]

Nice that the DLookup worked
(PHV, you're fast, man;-))

I've experienced no problems when using dates in SQL, only the format thing. Jet needs dates in a US recognisable way, but:

What more does the type mismatch say? Does it say which field?

Do you have a value in both your variables. Is the Prov field a text (if numeric, remove the text qualifiers (')). Try the debug.print strSQL tip from the previous thread, study it in the immidiate pane, try to run it in the QBE...

Roy-Vidar

 

[VBAguy22]

Both myDT and the field DT are of Date format
I am in Toronto so we have a Canadian date format?
Should be the same as US, except maybe for an occasional maple leaf here and there. just kidding.
It actually breaks on the next line which executes the above statement
DoCmd.RunSQL strSQL

and the full error msg isatatype missmatch in criteria expression. Nothing more
The string is assigned correctly....with # # around the date, and '' around strings....

 

[VBAguy22]

Oh I tried commenting out the last part of the expression containing the date...and it still gives the same error
must be another problem
this is soooooooo frustrating

 

[RoyVidar]

We are trying to help, but it is difficult without information.

If the string is assigned correctly, as you say, there shouldn't be any problem, but as long as we can't see the string, don't know the datetypes... how could we know/assist?

I use similar syntax all the time, and it works. Is it the same as yesterday, doing a restart again? (Use Debug | Compile and save frequently, perhaps also have a look at decompiling the database, marketed as a way of increasing the performance of the database, but also works for resolving code corruption)

Regarding the settings, in the first post, you used a non use date format (20/01/2003), US format is mm/dd/yyyy.

Roy-Vidar