query criteria of month and year, possibly quarter and annually



Greetings, thank you very much for looking at my post. Here is what I am
trying to accomplish...

I am being asked to provide reporting data based upon a a particular time
frame. The time frame is generally monthly, but there is potential for the
need to pull quarterly, semi-annually and annually. It would be easy to use
a standard text field but I am finding that many users do not seem to
understand that a montly time frame is not the first of teh month to the
first of the next month (i.e. 1/1/08 to 2/1/08 will not give the reponse that
you are looking for). It seems about fifty-fifty that the user will enter
that time frame in correctly. So I have elected to take that unknown out of
the equation.

So that is where I am right now...I am trying to think of the best way to do
this. If I have a seperate month field and year field taken from a form...I
am having difficulty joining them together in the query making sure that the
because of the need for a wild card after the month and before the year (also
I am not sure that how to display the month because if I use numerical values
they could be mistaken for the day or year).

The next option I would like to make available is a quarterly, semi-annually
and annual option. Basically it would be very nice to know how to have a
drop down item listed but when that item is choosen...the query uses a preset
value for that item...i.e. if they chose quarter 1 from the drop down...it
would use a time frame of January to March.

So I am hoping someone may have suggestions and or examples that will help
me accomplish my goal.

Thank you again for looking and I appreciate all responses =)





One possibility,

Combo box with list of
Quarter 1
January thru June
July thru Dec
Total Year

Then a second txt field where they have to supply the year to use

Then a button to run the query. It can check that both are present
and create the date ranges that are appropriate for each
And if you want you can say that if the month is greater than current
month then the year has to be less than current year or just let it
pick no records. (assuming dates are always in the past.)

You could alternately create a table instead of the list that included
all of the above and a sequence field to sort on so that it appeard in
the sequence you want in case the alphabetic sort would NOT put it the
way you wanted.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question