Noob question - Data Type Mismatch

[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

 

[Douglas J. Steele]

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

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

 

[Rick Brandt]

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

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.

 

[Allen Browne]

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

 

[tokersmurf]

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,

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- Hide quoted text -- Show quoted text -

 

[tokersmurf]

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,

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- Hide quoted text -- Show quoted text -