Date range problem in query

[fdavidgolf]

The date is stored MMDDYY. I want the query to select between dates and the user enter dates normal like mm/dd/yyyy.
The where statement would be between [Enter From Month - MM/DD/YYYY] and [Enter To Month - MM/DD/YYYY]. Would I have to reformat the date range using Left, Mid and Right methods? I am having no success reformating the MMDDYY field to YYYYMMDD. What is the best way to do this ?
Thanks!

 

[Davepacey]

BETWEEN [Enter Date in mm/dd/yyyy format] AND [Enter Date in mm/dd/yyyy format]

that will work

 

[fdavidgolf]

Hi Dave ! I am having trouble with different years. If I reformat the date from the data as YYYYMMDD, then I can get date over several years, but I don't want the user to enter dates that way. Between 100202 (Oct 2,2002) and 011503 (Jan 15, 2003) would not work. 021002 and 030115 would work. That make sense ?
Thanks!

 

[Davepacey]

Just re read your post, if the field is a date field with properties set mmddyyyy, all you need to do is change it to yyyymmdd that should do it, it certainly does on on the one I have (copy it first)

Dave

 

[fdavidgolf]

Dave, the field properties cannot be changed. The data is coming in thru ODBC to an SQL Tandem database. It is defined numeric there. I always leave out info, sorry. Thanks!

 

[Davepacey]

On 2003-02-17 16:21, fdavidgolf wrote:
Between 100202 (Oct 2,2002) and 011503 (Jan 15, 2003) would not work. 021002 and 030115 would work. That make sense ?
Thanks!

It looks to me as though your field is not formatted as a date/time field, if it was a date/time field you could set its format to yyyy/mm/dd with a input mask set to 0000/00/00 that way, there could be no mix up with dates. Insert another field, set as above, then copy and paste your data down into it. You should get the correct results

Dave

 

[fdavidgolf]

and I forgot to say, this will be run monthly or on demand. I am trying to use one macro to get all the Leave data of all employees. The macro runs all queries that make tables and runs reports. The macro needs to do it all.

More replies coming, thanks!