DateTime Criteria??

[koturtle]

I have an access query linked from a SQL Union Qry. Within this SQL
query there is a datetime field that when linked to access comes
across as a text field.

The format looks like this: (2004-03-01 00:00:00.000)

it sorts correctly in the query but when i try to put a criteria in
like >2007* it doesn't work. I have tried to say greater than
2004-03-01 00:00:00.000 and even tried to format the field by
creating a new field using =left(d[datefield],10) and then putting the
criterial on that field with no luck.

Any ideas?

 

[Wayne-I-M]

This should do it

SELECT CDate(Left([DateField],InStr([DateField]," ")-1)) AS FormatedDate
FROM TableName
WHERE (((CDate(Left([DateField],InStr([DateField]," ")-1)))>#1/1/2007#));

 

[koturtle]

This should do it

SELECT CDate(Left([DateField],InStr([DateField]," ")-1)) AS FormatedDate
FROM TableName
WHERE (((CDate(Left([DateField],InStr([DateField]," ")-1)))>#1/1/2007#));

--
Wayne
Manchester, England.

I have an access query linked from a SQL Union Qry.  Within this SQL
query there is a datetime field that when linked to access comes
across as a text field.

The format looks like this: (2004-03-01 00:00:00.000)

it sorts correctly in the query but when i try to put a criteria in
like >2007* it doesn't work.  I have tried to say greater than
2004-03-01 00:00:00.000  and even tried to format the field by
creating a new field using =left(d[datefield],10) and then putting the
criterial on that field with no luck.

Any ideas?- Hide quoted text -

- Show quoted text -

Worked great!
thanks

 

[Dad]

I have an access query linked from a SQL Union Qry. Within this SQL
query there is a datetime field that when linked to access comes
across as a text field.

The format looks like this: (2004-03-01 00:00:00.000)

it sorts correctly in the query but when i try to put a criteria in
like >2007* it doesn't work. I have tried to say greater than
2004-03-01 00:00:00.000 and even tried to format the field by
creating a new field using =left(d[datefield],10) and then putting the
criterial on that field with no luck.

Any ideas?