Trying To Select A User Input Date Range

[TripleJHJO]

I have a query that is using date range criteria entered by the user. However, I am getting dates that do not fall within the user input range. Below are the details:

SELECT invoice_date, Format([invoice_date],"mm/dd/yyyy") AS [Invoice Date]

FROM MyTable

WHERE (Format([invoice_date],"mm/dd/yyyy"))>=Format([Enter The Start Date (ex:mm/dd/yyyy)],"mm/dd/yyyy") And (Format([invoice_date],"mm/dd/yyyy"))<=Format([Enter The Ending Date (ex: mm/dd/yyyy)],"mm/dd/yyyy")

If I enter a start date of 09/01/2005 and an end date of 09/08/2005, I am getting dates, all in the 09/01 - 09/08 range, but the years are all over the map.

Is there a better way to do this?

Thanks,
J.Jensen

 

[TripleJHJO]

Just an additional note: This is in MSAccess 2002

 

[MajP]

My guess is that the format function returns a Variant not a date. You do not need any of the Format functions. Your date is stored as a time serial not in any type of format. When it compares a date it does not care anything about the format.

 

[Golom]

You probably need a parameters statement

PARAMETERS [Enter The Start Date (ex:mm/dd/yyyy)] DateTime, 
           [Enter The Ending Date (ex:mm/dd/yyyy)] DateTime ;

SELECT invoice_date, Format([invoice_date],"mm/dd/yyyy") AS [Invoice Date]

FROM MyTable

WHERE [invoice_date] BETWEEN 
      [Enter The Start Date (ex:mm/dd/yyyy)] AND 
      [Enter The Ending Date (ex: mm/dd/yyyy)]