Conversion part of string to date

MRvLuijpen · July 10th, 2003, 07:05 AM

Hello Experts

,

I have a date inside a string. I would like to extract the date from this string. I made a query which extracts part of the string.

Code:

SELECT Mid[IDCode],3,11) AS date, Unit, systemvalue, Type
FROM qryUnit

If I want to apply an filter with

Code:

 WHERE date BETWEEN #1/1/2000# AND #1/1/2003#

the query result is empty. I think that the problem lies in the filter date vs string.

How can I change this??

Your help is appriciated very much

Marc

SerranoG · July 10th, 2003, 07:14 AM

Quote:

quote:Originally posted by MRvLuijpen

Code:

SELECT Mid[IDCode],3,11) AS date, Unit, systemvalue, Type
FROM qryUnit

How about this?

Code:

SELECT CDate(Mid([IDCode],3,11)) AS date, Unit, systemvalue, Type FROM qryUnit

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

MRvLuijpen · July 10th, 2003, 07:34 AM

Greg,

Thx... It works good

Marc

kshantik · May 18th, 2004, 10:59 AM

My date is a string in the format of MMDDYY eg: 012904

I want to write a query to pull all the records with date greater than '01/29/04'

I have oracle background and there were nice functions like TO_DATE(columnname,'MM/DD/YY') to use in my query and I am wondering if there is anything like this in MS Access. If not, how do I solve this?

Thanks
Shanti

SerranoG · May 18th, 2004, 02:30 PM

In your query, simply put this in the criteria row for your specific date field

>=#1/29/04#

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

SerranoG · May 18th, 2004, 02:31 PM

Oh, wait... your date is a string... you have to convert the string into a real date. See the post on that topic in this forum section.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division