Date Range Query

[paulS30berks]

I am trying to extract records within a certain date range.

My structure is as follows:

Query 1 = pulls data direct from a table. There is a date field which is in the format YYYYMMDD.

Query 2 = pulls data from Query 1 and amends the date format to:
dd/mm/yyyy

Has anyone any suggestions on how I pull data from query 2 from within a certain date range. i.e 01/01/2005 to 01/05/2005

Thanks

 

[Bat17]

assuming that they really are dates and not text string there is no need to change them as they are only numbers underneath really! anyway to filter a range of dates use
Between #01/01/2005# And #01/05/2005# as the criteria

HTH

Peter

 

[paulS30berks]

Thanks for your reply.

This would be fine for a one of date search, but for end users they may wish to set the criteria for different ranges.

Thanks

 

[paulS30berks]

The purpose of my query is to select employees pension dates by date range.

The structure of my query is split into 2 queries.

The first pulls data direct from a benefits table. The pension date is in the format yyyymmdd. If I add:

Between [Forms]![Benefits]![date1] And [Forms]![Benefits]![date2] to the criteria of the date field and select a date range i.e 20050101 to 20050501, the correct data is output.

My second query pulls data from this query plus another table. The format of this date I have amended using:

Eligible Date: IIf(IsNull([Relevant_Date]),"",DateValue(Mid([Relevant_Date],7,2) & "/" & Mid([Relevant_Date],5,2) & "/" & Mid([Relevant_Date],1,4)))

so now my date format is dd/mm/yyyy.

If I now try to select a date range I receive all dates between 01/01 and 01/05 but for some reason other yyyy are being picked up.

Hope this makes sense.

 

[Bat17]

you could try adding cdate to force the datatype
Eligible Date: IIf(IsNull([Relevant_Date]),"",CDate(DateValue(Mid([Relevant_Date],7,2) & "/" & Mid([Relevant_Date],5,2) & "/" & Mid([Relevant_Date],1,4))))

HTH

Peter