Setting Criteria Within a Select Query

[paulS30berks]

I have set up a query to pull data from within a date range.

I have written an SQL Statement to amend the format of the date field:

Effective Date: IIf([Date_Effective_From]="00000000",Null,DateValue(Mid([Date_Effective_From],7,2) & "/" & Mid([Date_Effective_From],5,2) & "/" & Mid([Date_Effective_From],1,4)))

Within my criteria I have:

Between [Forms]![Benefits]![date1] And [Forms]![Benefits]![date2]

(Benefits being the form). If I enter date range 01/01/2005 to 01/05/2005 I receive all data from 01 January to 01 May - but including different years i.e 2002, 2003, 2004 etc.

Can anyone suggest anything I may have missed?

Thanks

 

[neileg]

I think that using string functions on date values is a problem. Why don't you use the built in formatting for dates?