Noob question - Data Type Mismatch

  • Thread starter Thread starter tokersmurf
  • Start date Start date
T

tokersmurf

Hi All,

I am fairly new to SQL and am running into a problem I can't figure
out. Hopefully someone here can help. To be honest im not even sure
if
this is the right place for the query so if not then let me know
where
I should put this question:


I have an Access database which I am trying to run the following
query
against:


DELETE From Basket WHERE CreatedAt < '26/01/2007 14:20:49'


The DB has a table called BASKET which in turn has a column called
"CreatedAt". The column is set to be a date and contains a row with a
CreatedAt date of "25/01/2007 14:11:45".


As far as I can tell this should work but when I run the above
statement, I get a "Data Type Mismatch in criteria expression" error.


Any help or pointer would be appreciated


Cheers in advance


TS
 
In Access, dates are delimited with #, not '.

DELETE From Basket WHERE CreatedAt < #26/01/2007 14:20:49#
 
Hi All,

I am fairly new to SQL and am running into a problem I can't figure
out. Hopefully someone here can help. To be honest im not even sure
if
this is the right place for the query so if not then let me know
where
I should put this question:


I have an Access database which I am trying to run the following
query
against:


DELETE From Basket WHERE CreatedAt < '26/01/2007 14:20:49'


The DB has a table called BASKET which in turn has a column called
"CreatedAt". The column is set to be a date and contains a row with a
CreatedAt date of "25/01/2007 14:11:45".


As far as I can tell this should work but when I run the above
statement, I get a "Data Type Mismatch in criteria expression" error.


Any help or pointer would be appreciated
Click to expand...

In Access queries dates are delimited with # rather than '. You also have to
use either US format or a non-ambiguous format...

DELETE From Basket WHERE CreatedAt < #01/26/2007 14:20:49#

Your non-US format would work with the specific date value you specified because
the day value was greater than 12, but whenever that is not the case Access
would have assumed that the first part was the month.
 
Use # instead of ' to delimit the literal date/time value.

You also need to use the American format. Try:
DELETE From Basket WHERE [CreatedAt] < #1/26/2007 14:20:49#;

More info about formats here:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
 
Cheers people,

I shall try this and see how I get on.

all your help is much appreciated.

TS
================================================

Use # instead of ' to delimit the literal date/time value.

You also need to use the American format. Try:
DELETE From Basket WHERE [CreatedAt] < #1/26/2007 14:20:49#;

More info about formats here:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.



Hi All,
Click to expand...
I am fairly new to SQL and am running into a problem I can't figure
out. Hopefully someone here can help. To be honest im not even sure
if
this is the right place for the query so if not then let me know
where
I should put this question:
Click to expand...
I have an Access database which I am trying to run the following
query
against:
Click to expand...
DELETE From Basket WHERE CreatedAt < '26/01/2007 14:20:49'
Click to expand...
The DB has a table called BASKET which in turn has a column called
"CreatedAt". The column is set to be a date and contains a row with a
CreatedAt date of "25/01/2007 14:11:45".
Click to expand...
As far as I can tell this should work but when I run the above
statement, I get a "Data Type Mismatch in criteria expression" error.
Click to expand...
Any help or pointer would be appreciated
Click to expand...
Cheers in advance
Click to expand...
TS- Hide quoted text -- Show quoted text -
Click to expand...
Click to expand...
 
Thanks to all who gave input - worked a treat.

I also changed the format so that it will work for other dates too.

Great stuff, cheers to all who replied,

TS
============================================

Use # instead of ' to delimit the literal date/time value.

You also need to use the American format. Try:
DELETE From Basket WHERE [CreatedAt] < #1/26/2007 14:20:49#;

More info about formats here:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.



Hi All,
Click to expand...
I am fairly new to SQL and am running into a problem I can't figure
out. Hopefully someone here can help. To be honest im not even sure
if
this is the right place for the query so if not then let me know
where
I should put this question:
Click to expand...
I have an Access database which I am trying to run the following
query
against:
Click to expand...
DELETE From Basket WHERE CreatedAt < '26/01/2007 14:20:49'
Click to expand...
The DB has a table called BASKET which in turn has a column called
"CreatedAt". The column is set to be a date and contains a row with a
CreatedAt date of "25/01/2007 14:11:45".
Click to expand...
As far as I can tell this should work but when I run the above
statement, I get a "Data Type Mismatch in criteria expression" error.
Click to expand...
Any help or pointer would be appreciated
Click to expand...
Cheers in advance
Click to expand...
TS- Hide quoted text -- Show quoted text -
Click to expand...
Click to expand...
 
Back
Top