Problem filtering dates 1

[RonMcIntire]

All:

I'm querying another query to filter a list of birthdays (Date/Time type) for a range of months and I'm having trouble.

SETUP
In the query, I use the following calculation:
MO: DatePart("m",[birthdate]) 'this gets me the month

To filter for a range, I use the criteria:
Between [Start: (Example: 2)] And [End: (Example: 5)]

PROBLEM
When I ask for a range like
Start: 1 End: 8, I get months 1 thru 8 AND 10 thru 12.

when I ask for a range like
Start: 8 End: 10, I get months 2 thru 8 AND 10 thru 12.

What am I doing wrong and how do I get what I want?

Thanks

Ron

 

[traingamer]

Access is treating the result of your 'between' as text. Replace what you had with this:

Between CInt([Start: (Example: 2)]) And CInt([End: (Example: 5)])

This converts it to integers which I assume is what you are trying to do.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SkipVought]

Hi,

How about

Month([birthdate]) Between 1 AND 8

Skip,

_[red][/red]

 

[RonMcIntire]

Traingamer:

I just tried the code you suggest and it does exactly what I need.

Thanks for your help.

Ron

 

[RonMcIntire]

Skip:

I presume you suggest that the calculation code is:

MO: Month(birthdate)

and the Criteria is

Between [Start: ] and [End: ] where the start value is 1 and the end value is 8.

I tried that with the same results as using the datepart function. The problem is apparently related to the values calculated being Variant Integers which do not sort the same way integer types do.

Thanks for your help anyway.

Ron