WHERE clause using a date stored as text

[EuanPirie]

I'm trying to write a query in an existing Access 97 database where I want to search on a date. The date has to be in a 1 week window about 6 months ago (it's for a report for printing which tests are required that week). The date is stored in a text field of size 20(I had nothing to do with it!).<br>
<br>
Any ideas?

 

[KurtMiller]

1) look at the datediff function<br>
<br>
2) text dates can be evaluated in sql: <br>
<br>
where some_date_field &gt;= #01/01/99#<br>
<br>
using the # sign as a delimiter<br>
<br>
i don't have any documentation handy or i would be more specific but that's the general idea<br>

 

[Hallsys]

There are so many ways to do this it's hard to pick one. Since you're dealing with character data you can do whatever you'd like depending on how the dates have been entered. <br>
<br>
1. Create a new field in the table and populate it using an update query that employs the CDate() function which takes any valid character Date Expression ("12/25/99"). Then write the WHERE clause how you would normally on the new field.<br>
<br>
2. Use this directly on the data:<br>
WHERE CDate([Your20CharDateField]) between #12/21/99# and #12/28/99#<br>
<br>
3. Use the Left(), Right() and Mid() functions directly on the character data to parse out the Month and Day and make logical comparisons to your desired window to return the proper records.<br>
<br>
4. If theres more to it than it appears due to the way the dates are entered in the 20 char field, write a function in VB in a module within the database and call it from the SQL:<br>
WHERE myDateConv-CompFunction([Your20CharDateField]). In the function perform all of the acrobatics needed to determine if the character data being sent to the function is within your window.<br>
<br>
Hope this helps.<br>