Querying Dates in Query view

[smada80]

Hi all

I have an access query that looks at a field called Date_Notice_Given and should return all records between a given date range. It picks up the dates from a form.

I have added the Date_notice_Given field to the query and typed the following in the criteria:

Between <<fieldname>> And <<fieldname>>

but it doesn't give the right results, so the I tried:

> Date()

and it still gives a load of records from last year.

Any ideas, any help greatly appreciated!

 

[scriverb]

I am going to guess that the supposed date from and date to controls on your form are UNBOUND as the user is entering dates in to run the query. You see at this point even though they look like dates(i.e. 03/18/2003 ) ACCESS is only seeing them as strings of characters. They have no Date Value as ACCESS uses with its Date type fields. So, just change your criteria statement to the following:

Between CDate(<<fieldname>>) And CDate(<<fieldname>>)

This should do it for you. Now you are comparing your date field(Date_Notice_Given) to two date values both Low and High.
Bob Scriver