If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Query not filtering dates correctly - I am stumped

Setup:
I have Table1 with a field called DateReceived. DateReceived is a text field with input mask "99/99/0000;0;_". Changing the data type for the field is not an option.
I also have Table2 which has a field called DateNo of type date/time. I want to append a certain date range of records from Table1 to Table2 including appending DateReceived to DateNo.

Problem:
I am having a problem with the parameters in the append query. First I made a function to convert the text in DateReceived to date/time format (See code for MakeDate below). Then I created a calculated field in my query: "mydate: MakeDate([DateReceived])".

What I Don't Understand:
If my criteria for the mydate field is "Between #3/1/2004# and #3/30/2004#" then it works correctly. If my criteria is "Between [start] and [end]" and I enter "3/1/2004" and "3/30/2004" then the query returns all records where mydate is in March, regardless of the year.

If I do that then mydate becomes a text field. Now "Between [start] and [end]" then entering "3/1/2004" and "3/30/2004" returns no records.

Using "Between #3/1/2004# and #3/30/2004#"* returns the same records as before, including those in March but not in 2004, except mydate reads like "#3/1/2001#" instead of "3/1/2001"

*Access changes this to "Between '#3/1/2004#' and '#3/30/2004#'" because mydate is now a text field.

The way I had it before was filtering correctly if I hard coded the date range into the criteria but not if I used parameters which is really the source of my confusion.

Also, when I used parameters and sort ascending it recognized that 3/26/2001 is before 3/1/2004 so it must be recognizing the year part of the date, so why are March 2001/2002/2003 records not filtered out by "Between [start] and [end]" then entering "3/1/2004" and "3/30/2004"

Originally posted by michael515
If I do that then mydate becomes a text field. Now "Between [start] and [end]" then entering "3/1/2004" and "3/30/2004" returns no records.

Using "Between #3/1/2004# and #3/30/2004#"* returns the same records as before, including those in March but not in 2004, except mydate reads like "#3/1/2001#" instead of "3/1/2001"

*Access changes this to "Between '#3/1/2004#' and '#3/30/2004#'" because mydate is now a text field.

The way I had it before was filtering correctly if I hard coded the date range into the criteria but not if I used parameters which is really the source of my confusion.

Also, when I used parameters and sort ascending it recognized that 3/26/2001 is before 3/1/2004 so it must be recognizing the year part of the date, so why are March 2001/2002/2003 records not filtered out by "Between [start] and [end]" then entering "3/1/2004" and "3/30/2004"