Type mismatch?

[xool]

Hi i'm getting a type mismatch error, anyone tell me why pls?


conn.Execute "DELETE FROM Bmemo WHERE (tDate <= '" & date() & "')"


tDate is a date field in an Access database....

Many thanks

 

[Randy Rahbar]

Hi i'm getting a type mismatch error, anyone tell me why pls?

You have to surround the date with pound signs when using Access.

Try this instead:
conn.Execute "DELETE FROM Bmemo WHERE (tDate <= #" & date() & "#)"

 

[Aaron Bertrand - MVP]

Why do you need to pass in the date from ASP? Access knows the date.

conn.Execute "DELETE Bmemo WHERE tDate <= Date()"

As Randy pointed out, if you're passing the date from outside the database,
you need to delimit it correctly.

conn.Execute "DELETE Bmemo WHERE tDate <= #" & Date() & "#"

I might even go so far as to suggest putting the date in an ISO format, e.g.
yyyy-mm-dd, to avoid ambiguity.

 

[xool]

Great that's it, many thanks Randy

You have to surround the date with pound signs when using Access.

Try this instead:
conn.Execute "DELETE FROM Bmemo WHERE (tDate <= #" & date() & "#)"

 

[Aaron Bertrand - MVP]

Not to take anything from Randy's answer, but if you use the method I
recommended, you don't have to worry about date formats / regional settings
messing up the interpretation of date() (e.g. if a US-based access database
is placed on a UK server; the internal comparison is much safer than
external).

 

[xool]

Ok will do, many thanks Aaron

Not to take anything from Randy's answer, but if you use the method I
recommended, you don't have to worry about date formats / regional settings
messing up the interpretation of date() (e.g. if a US-based access database
is placed on a UK server; the internal comparison is much safer than
external).