Query won't filter for dates properly?

[odin1701]

I have a query which is filtering by date as criteria...but it won't filter right.

There is a date field from a table in another query which has date/times in it.

I have used Format([Field], "Short Date") in that query to display just the date.

I am now doing another query which points to that query (It's a SQL Union query if that matters) and it has the following for the Criteria:

Between "6/1/2007" And "6/30/2007"

However, it is showing anything in the month of June for the years 2005, 2006 and 2007, instead of just 2007. Any ideas why it is doing this? It doesn't make sense to me that it will filter for the month/date but not the year.

I also tried changing the format to Format([Field], "mm/dd/yyyy") and criteria of "06/01/2007", etc.

 

[Rabbie]

IF the field is in Date/Time format the criteria should be "Between #1/2/2007# and #1/3/2007#"

HTH

 

[odin1701]

I've not had to use the # character for Date/Time fields before...but they did not have the time stored.

I have formatted the field so that it is just a date, so that shouldn't affect it as I've done it that way before.

Even if I try to put in #6/1/2007#, when I tab out of the criteria Access just puts quotes around it so it ends up being "#6/1/2007#" which of course returns nothing at all.

 

[Rabbie]

What does the SQL view of your query look like?

 

[odin1701]

Simplified, here's what it is:

SELECT [qryAccount Information].[Date Entered], [qryAccount Information].[Date Exited], [qryAccount Information].[Account Type Code] FROM [qryAccount Information]
WHERE ((([qryAccount Information].[Date Entered]) Between "06/01/2007" And "06/30/2007"))
ORDER BY [qryAccount Information].[Date Entered];

 

[Rabbie]

The format of the datea in the between statement look like that [qryAccount Information].[Date Entered] is a text field. Thats why you are not selcting the right records. You need to have [qryAccount Information].[Date Entered] as date/time field not a text field and then put # round the dates in the between clause. - no quotes

This works in my DB.

 

[odin1701]

The Date Entered field is a Date/Time field, not a text field. However I have formatted it to be a short date, rather than include the time which I do not need.

The Date Entered field in the Account Info query is a field from a SQL table.

The way that I have tried has worked in all other DB's that I have here, but not this one. I cannot change field types though, as this DB is not developed or maintained by me, I just do reporting out of it.

 

[Rabbie]

Formatting as a short date has converted it to TEXT. You don't always get what you expect when you try matching on strings

 

[odin1701]

Formatting as a short date has converted it to TEXT. You don't always get what you expect when you try matching on strings

Currently I have it formatted as:

Format([Date Entered], "mm/dd/yyyy")

That should return a date right?

 

[Rabbie]

No it returns a string. Read Access help for Format.

 

[odin1701]

Okay I got it working...I have to have two fields for the date. One to filter by and not show, and the other to format. Kinda ridiculous. I've never had to do that before =/

 

[Rabbie]

Glad to hear you got it working