DateTime Criteria??

  • Thread starter Thread starter koturtle
  • Start date Start date
K

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?
 
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#));
 
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.



koturtle said:
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.
Click to expand...
The format looks like this: (2004-03-01 00:00:00.000)
Click to expand...
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.
Click to expand...
Any ideas?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


Worked great!
thanks
 
koturtle said:
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?
Click to expand...
 
Back
Top