A Query that allows a user to select sales for 3 months consecutively

[roadrage]

Hi,

I want to display all orders made between specified months that a user chooses, so for example say i want to check for all orders between January and March how would i do this in my query?
My date field is set to shortdate
I have tried the following:

Month Names: Format([Order Date],"mmmm")

Between [Enter month 1] And [Enter Month 2]

This displays orders for both months entered but also includes other months that are not in any order. So if i enter January and March i end up with orders for jan,july,june and march??? I'd appreciate any help

 

[pbaldy]

You see months, Access just sees text. Your criteria is working alphabetically. The simple solution is to switch the field to a numeric month:

MonthNum: Month([Order Date])

and have the user enter numeric months. That said, what happens when the desired period overlaps a year? What happens when you get more than one year's data? The better solution is likely to use a date range.

 

[roadrage]

You see months, Access just sees text. Your criteria is working alphabetically. The simple solution is to switch the field to a numeric month:

MonthNum: Month([Order Date])

and have the user enter numeric months. That said, what happens when the desired period overlaps a year? What happens when you get more than one year's data? The better solution is likely to use a date range.

Thanks for the reply that works just fine!

 

[pbaldy]

Happy to help!

 

[roadrage]

You see months, Access just sees text. Your criteria is working alphabetically. The simple solution is to switch the field to a numeric month:

MonthNum: Month([Order Date])

and have the user enter numeric months. That said, what happens when the desired period overlaps a year? What happens when you get more than one year's data? The better solution is likely to use a date range.

Hi, I noticed that if i enter month 1 and month 3 it shows month 12, 11 and 10.

 

[roadrage]

I also was wondering if it is possible to find out whether i can group my orders depending on the gender of the customer?

 

[DCrake]

Simply add another criteria to your filter string

AND Gender = "Male"

 

[roadrage]

Simply add another criteria to your filter string

AND Gender = "Male"

Thanks that works!

 

[pbaldy]

Hi, I noticed that if i enter month 1 and month 3 it shows month 12, 11 and 10.

Did you get this fixed? If not, your value is being treated as text for some reason. How are you getting the month into the query?

 

[roadrage]

Did you get this fixed? If not, your value is being treated as text for some reason. How are you getting the month into the query?

Hi, no I didn't get this fixed, my month comes from and orders table, the field type is set to short date? Do you have any ideas?

 

[pbaldy]

You're getting just the month in the query; how did you do that? If you used the Format() function, it will return a text value. The Month() function will return a numeric value.

 

[roadrage]

You're getting just the month in the query; how did you do that? If you used the Format() function, it will return a text value. The Month() function will return a numeric value.

I'm using the month function :

MonthNum: Month([Order Date])

And my search criteria is:

Between [Enter month 1] And [Enter month 2]

 

[boblarson]

See if this helps

Between CInt([Enter month 1]) And CInt([Enter month 2])

 

[pbaldy]

I just tested that and it worked as it should, only returning 1-3. Try this to force the issue:

Between CInt([Enter month 1]) And CInt([Enter month 2])

 

[boblarson]

I just tested that and it worked as it should, only returning 1-3. Try this to force the issue:

Between CInt([Enter month 1]) And CInt([Enter month 2])

Sorry, I couldn't help myself

 

[roadrage]

I just tested that and it worked as it should, only returning 1-3. Try this to force the issue:

Between CInt([Enter month 1]) And CInt([Enter month 2])

That has worked! Thanks again to you guys!

 

[Brianwarnock]

Looks like Bob and Paul were posting at the same time. I have a question for them. What circumstances would force the need for CInt , I have never needed this.
I cant see why the system would default the parameter to text, he does not say that he is using a form for the input where he could have the input txtbox formatted as text.

If its a pop up then I'm puzzled, but couldn't he just use the Parameter option to define the parameters/

Brian

 

[boblarson]

Brian -

I can't say why sometimes Access seems to want to use the parameter inputs as text but I've seen it happen. I guess you could explicitly add the parameters to the query parameters and set the data type there and then you shouldn't need the CInt at all.

 

[Brianwarnock]

Thanks for the response Bob.

Brian