Date Range

[klp via AccessMonster.com]

I have a query where I want a date range to be entered. I have a field in my
query that is as follows:

Format$([Needs].[Need Date],'mmmm yyyy') - this is as an expression.

For my criteria I have:
Between [Type beginning month and year:] And [Type ending month and year:]

When I put in my date range say May 2005 to June 2005 it brings in the
correct months May and June, but for every year. So I get

May 2005
May 2004
May 1988
June 2006
June 2005
June 2000

It's not recognizing my year. I also have a field in there to format the year:

Year(Needs.[Need Date])*12+DatePart('m',Needs.[Need Date])-1

Where am I going wrong here?

Thanks!
Kim P

 

[Allen Browne]

Format() gives you a string.
Access is therefore performing a string comparsion, not a date comparison.
The string comparsion means it compares character by character.
So, April 2006 (starting with an A) comes before May 2000 (starting with M.)

You need to perform a date comparsion instead. You can still have the user
enter the dates in that format, as long as you declare the parameters.

1. Remove the Format... expresssion from your query.

2. Under the Needs.[Need Date] field, enter this criteria:

= [Type beginning month and year:] And < DateAdd("m", 1, [Type ending

month and year:])

3. Choose Parameters on the Query menu.
Enter 2 rows into the dialog:
[Type beginning month and year:] Date/Time
[Type ending month and year:] Date/Time

The query will now interpret the values as dates.
If you don't supply a day of the month, it will assume the first.
We therefore used the logic "less than the first of next month" to get
records from the final month.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a query where I want a date range to be entered. I have a field in
my
query that is as follows:

Format$([Needs].[Need Date],'mmmm yyyy') - this is as an expression.

For my criteria I have:
Between [Type beginning month and year:] And [Type ending month and year:]

When I put in my date range say May 2005 to June 2005 it brings in the
correct months May and June, but for every year. So I get

May 2005
May 2004
May 1988
June 2006
June 2005
June 2000

It's not recognizing my year. I also have a field in there to format the
year:

Year(Needs.[Need Date])*12+DatePart('m',Needs.[Need Date])-1

Where am I going wrong here?

Thanks!
Kim P

 

[klp via AccessMonster.com]

Thank you so much. That worked beautifully!! Learn something new every day!

Format() gives you a string.
Access is therefore performing a string comparsion, not a date comparison.
The string comparsion means it compares character by character.
So, April 2006 (starting with an A) comes before May 2000 (starting with M.)

You need to perform a date comparsion instead. You can still have the user
enter the dates in that format, as long as you declare the parameters.

1. Remove the Format... expresssion from your query.

2. Under the Needs.[Need Date] field, enter this criteria:

= [Type beginning month and year:] And < DateAdd("m", 1, [Type ending

month and year:])

3. Choose Parameters on the Query menu.
Enter 2 rows into the dialog:
[Type beginning month and year:] Date/Time
[Type ending month and year:] Date/Time

The query will now interpret the values as dates.
If you don't supply a day of the month, it will assume the first.
We therefore used the logic "less than the first of next month" to get
records from the final month.

I have a query where I want a date range to be entered. I have a field in
my

[quoted text clipped - 24 lines]

Thanks!
Kim P