I needed to convert a string date format (ie 20080429) to an access date format by use of a query.
After changing the txt to a real DateValue I was able to perform a query for selecting a range of dates.

In my access Database I had a field of the type TEXT with in there a date in the format yyyyMMdd
To be able to make a query for selecting all dates between two dates, Access needs the date in a real date format.

After spending a lot of time on google and different sites I came up with following solution without having to change the type of the Date field from text to date.

First of all I used the Format() function to change the format to “yyyy/MM/dd” ie. 2008/04/29
This step is necessary because the following step expects your date separated by slashes or some other date seperators

After doing that I could use the DateValue() function to get the date of the text
Pay attention the DateValue function will convert the date to the format specified in your Windows Short Date format in the Regional and Language setting.

Check your short Date format in the Windows Control Panel -> Regional and Language options -> Formats -> Customize this format -> Date




Now that I have converted the TEXT to DATE, I could use the access specific query syntax to get all dates between date 1 and date 2.
My Access criteria looks like this. “>#29/04/2008# And <#1/05/2008#”

Attention your criteria should reflect the short date format in your windows regional settings.




Some more background info about the DateValue function syntax:
http://office.microsoft.com/en-gb/access/HA012288141033.aspx?pid=CH100728911033
http://www.techonthenet.com/access/functions/date/datevalue.php

Query date syntax
http://office.microsoft.com/en-gb/access/HA100666111033.aspx?pid=CH100645771033