Month Query

[accessuser via AccessMonster.com]

Hi,

I read many posts on the accessmonster site regarding month queries, but
still can't find a best solution for my database. Here is my situation, I
have Main table, with different department queries. On the Department field,
Criteria "Department Name." I also have a field called Scheduled Month. It
is in Month name from january - december. i want users to be able to pull up
reports with from Month___ to Month____ . i tried putting criteria as,
Between [Enter Starting Month] and [Ending Month] in the Scheduled Month
field. But it doesn't work. Sometimes it gives me just january, and
sometimes nothing. Anybody has any suggestions for me to make this more user-
friendly and easy to look up? I read many of Month queries, and seems like
the number months work much better than text? If i want to change to number
month, how do i start?


Thanks you!

 

[Rick Brandt]

Hi,

I read many posts on the accessmonster site regarding month queries,
but still can't find a best solution for my database. Here is my
situation, I have Main table, with different department queries. On
the Department field, Criteria "Department Name." I also have a
field called Scheduled Month. It is in Month name from january -
december. i want users to be able to pull up reports with from
Month___ to Month____ . i tried putting criteria as, Between [Enter
Starting Month] and [Ending Month] in the Scheduled Month field. But
it doesn't work. Sometimes it gives me just january, and sometimes
nothing. Anybody has any suggestions for me to make this more user-
friendly and easy to look up? I read many of Month queries, and
seems like the number months work much better than text? If i want
to change to number month, how do i start?

When you display month names any sorting or comparative testing will be
alphbetical, not chronological. You could have a small table that maps the
month names to numbers and include that in your query applying the criteria to
the number, but the user would have to enter the month number when prompted
rather than the name. Otherwise you might be stuck applying criteria to an
expression that builds an actual date from the month name...

WHERE CDate([Scheduled Month] & "1, 2000")
BETWEEN CDate([Enter Starting Month] & "1, 2000")
AND CDate([Enter Ending Month] & "1, 2000")

 

[accessuser via AccessMonster.com]

where do i put these:

WHERE CDate([Scheduled Month] & "1, 2000")
BETWEEN CDate([Enter Starting Month] & "1, 2000")
AND CDate([Enter Ending Month] & "1, 2000")

Do I copy and paste all three lines into the Scheduled Month Criteria line in
the query design view?

I tried using 2nd and 3rd lines, but i got a error message saying the
expression is incorrectly and too complex to be evaluated.


thanks Rick!

Hi,

[quoted text clipped - 11 lines]

seems like the number months work much better than text? If i want
to change to number month, how do i start?

When you display month names any sorting or comparative testing will be
alphbetical, not chronological. You could have a small table that maps the
month names to numbers and include that in your query applying the criteria to
the number, but the user would have to enter the month number when prompted
rather than the name. Otherwise you might be stuck applying criteria to an
expression that builds an actual date from the month name...

WHERE CDate([Scheduled Month] & "1, 2000")
BETWEEN CDate([Enter Starting Month] & "1, 2000")
AND CDate([Enter Ending Month] & "1, 2000")

 

[Rick Brandt]

where do i put these:

WHERE CDate([Scheduled Month] & "1, 2000")
BETWEEN CDate([Enter Starting Month] & "1, 2000")
AND CDate([Enter Ending Month] & "1, 2000")

Do I copy and paste all three lines into the Scheduled Month Criteria
line in the query design view?

I tried using 2nd and 3rd lines, but i got a error message saying the
expression is incorrectly and too complex to be evaluated.

You would have to add a new calculated field using the first expression and then
use the other two under that column as criteria.

 

[John Vinson]

Hi,

I read many posts on the accessmonster site regarding month queries, but
still can't find a best solution for my database. Here is my situation, I
have Main table, with different department queries. On the Department field,
Criteria "Department Name." I also have a field called Scheduled Month. It
is in Month name from january - december.

That's your first problem. Your month name is a text string, which
will sort in the order April, August, December, February...
alphabetically. You've defined it as Text; Access is not going to
treat the text string "September" any differently than the text string
"Septentorialis" or "Serpentine".

You might want to consider storing the schedule date in a Date/Time
field, storing #9/1/2006# for something that's scheduled in September.
You can use a Format property of "mmmm" - or better "mmmm yy" - to
display this field as "September" or "January 07" (as it is, does
January mean LAST January or NEXT January, and how can you tell!?);
you'll need to enter a real date, though.

If a) you're stuck with the text date field and b) can confidantly
assume that each month is during the current year, you can use
something close to Rick's suggestion - I think he left out an
obligatory blank. Try putting a calculated field into a Query by
typing

DateScheduled: CDate("1-" & [Scheduled Month] & "-" & Year(Date()))

into a vacant Field cell in a query. This will calculate a Date/Time
field which will sort chronologically; you can use a criterion of

BETWEEN [Enter start date:] AND [Enter end date:]

If the user types a recognizable date value such as 9/1 or
10-Sept-2006 into the criteria, they'll get all records where the
first of the month is between their entered dates.

John W. Vinson[MVP]

 

[accessuser via AccessMonster.com]

Thanks Rick!

But I am stil getting the error message. I tried to add another field, and
changing the existing field, but both didn't work. used the expression
builder too with your first expression, but no result.

where do i put these:

[quoted text clipped - 7 lines]

I tried using 2nd and 3rd lines, but i got a error message saying the
expression is incorrectly and too complex to be evaluated.

You would have to add a new calculated field using the first expression and then
use the other two under that column as criteria.

 

[Rick Brandt]

Thanks Rick!

But I am stil getting the error message. I tried to add another
field, and changing the existing field, but both didn't work. used
the expression builder too with your first expression, but no result.

Post your SQL.

 

[accessuser via AccessMonster.com]

Ok,,this is not working out. I can't get this Month query to work. I dont
know where to find the SQL, isn't that what you gave me? Anyway, I'm giving
this up for now. Unfortunately it just doesn't want to work.

Rick and John, thank you very much for your time writing those tips.

 

[accessuser via AccessMonster.com]

I tried with the codes that you provided and just couldn't figure out. But I
tried using my Scheduled Month format as "number" in the table design. And
put criteria under the "Schedule Month" field as "Between [Enter Starting
Month] AND [Enter Ending Month]. It works the way I wanted by doing it
"number" format. But is there anyway to print reports with MONTH NAMES by
using the "number" format. Is there any way at all? I just can't get them
to work by using the tips you two provided.

I am also thinking of doing it by formating the Scheduled Month field as
number format, and then use combo boxes in the forms with 2 columns, one
number, and another month name, and on the report side by using a formula of
=scheduled month? WIll this work? Will it mess up some calculations in the
future?

Any easy way for me to do this month query, i am not an expert in access and
is getting very frustrated with this month query. If possible, is there any
possible access samples with just Month Query. I checked out the Allen
Browne, but couldn't figure out at all with that code.

Thanks again!

Hi,

[quoted text clipped - 3 lines]

Criteria "Department Name." I also have a field called Scheduled Month. It
is in Month name from january - december.

That's your first problem. Your month name is a text string, which
will sort in the order April, August, December, February...
alphabetically. You've defined it as Text; Access is not going to
treat the text string "September" any differently than the text string
"Septentorialis" or "Serpentine".

You might want to consider storing the schedule date in a Date/Time
field, storing #9/1/2006# for something that's scheduled in September.
You can use a Format property of "mmmm" - or better "mmmm yy" - to
display this field as "September" or "January 07" (as it is, does
January mean LAST January or NEXT January, and how can you tell!?);
you'll need to enter a real date, though.

If a) you're stuck with the text date field and b) can confidantly
assume that each month is during the current year, you can use
something close to Rick's suggestion - I think he left out an
obligatory blank. Try putting a calculated field into a Query by
typing

DateScheduled: CDate("1-" & [Scheduled Month] & "-" & Year(Date()))

into a vacant Field cell in a query. This will calculate a Date/Time
field which will sort chronologically; you can use a criterion of

BETWEEN [Enter start date:] AND [Enter end date:]

If the user types a recognizable date value such as 9/1 or
10-Sept-2006 into the criteria, they'll get all records where the
first of the month is between their entered dates.

John W. Vinson[MVP]

 

[John Vinson]

I tried with the codes that you provided and just couldn't figure out. But I
tried using my Scheduled Month format as "number" in the table design. And
put criteria under the "Schedule Month" field as "Between [Enter Starting
Month] AND [Enter Ending Month]. It works the way I wanted by doing it
"number" format. But is there anyway to print reports with MONTH NAMES by
using the "number" format. Is there any way at all? I just can't get them
to work by using the tips you two provided.

Sure. You can use an integer 1-12 field [Scheduled Month] and use a
function:

Choose([Scheduled Month], "January", "February", "March", <etc through
December)

You DIDN'T use the tips I provided. I suggested changing the datatype
of the field to Date/Time. Did you try that?

I am also thinking of doing it by formating

Don't confuse a field's DATA TYPE with its FORMAT. They are two quite
separate (somewhat interdependent) properties. A Format merely
controls how a value is displayed; a datatype controls what can be
actually stored in the field.

the Scheduled Month field as
number format, and then use combo boxes in the forms with 2 columns, one
number, and another month name, and on the report side by using a formula of
=scheduled month? WIll this work? Will it mess up some calculations in the
future?

Yes. What year is 4 in?

Date values ARE DATES. January follows December (if it's January 2007
and December 2006); 12 follows 11 and precedes 1. This is true of
dates; it is NOT true of numbers. If you're working with date data -
why on Earth don't you want to use a date datatype!?

Any easy way for me to do this month query, i am not an expert in access and
is getting very frustrated with this month query. If possible, is there any
possible access samples with just Month Query. I checked out the Allen
Browne, but couldn't figure out at all with that code.

It's not working because you are insisting on using THE WRONG
DATATYPE.

John W. Vinson[MVP]

 

[accessuser via AccessMonster.com]

I dont know if this is me that can't get this to work or what. I TRIED what
you told me, over and over. I dont need YEAR, all i need is MONTH. I have
another field for YEAR. I tried to change the following so many times:

1) Table Design, Scheduled Month field to "Date/Time", under it, there is a
"format" i put "mmmm" or "mmm" neither worked. There is option that I can
select, but it is always with month and year, i dont need that, I only need
MONTH, that's all.

2) On the query designed, I changed the field "format" to "mmmm" or "mmm",
neither worked. And I also copy and paste the expressions that you two
provided. Still no luck.

3) I changed my "scheduled month" column on my form to "Date/Time", format
"mmmm" or "mmm", it doesnt work.

And it keeps giving me the error message of it is invalid, and too complex.
I really want this to work as Date, but I can't make it work.


Choose([Scheduled Month], "January", "February", "March", <etc through

December) <<<<<<<<Where do I put this???

I tried with the codes that you provided and just couldn't figure out. But I
tried using my Scheduled Month format as "number" in the table design. And

[quoted text clipped - 3 lines]

using the "number" format. Is there any way at all? I just can't get them
to work by using the tips you two provided.

Sure. You can use an integer 1-12 field [Scheduled Month] and use a
function:

Choose([Scheduled Month], "January", "February", "March", <etc through
December)

You DIDN'T use the tips I provided. I suggested changing the datatype
of the field to Date/Time. Did you try that?

I am also thinking of doing it by formating

Don't confuse a field's DATA TYPE with its FORMAT. They are two quite
separate (somewhat interdependent) properties. A Format merely
controls how a value is displayed; a datatype controls what can be
actually stored in the field.

the Scheduled Month field as
number format, and then use combo boxes in the forms with 2 columns, one
number, and another month name, and on the report side by using a formula of
=scheduled month? WIll this work? Will it mess up some calculations in the
future?

Yes. What year is 4 in?

Date values ARE DATES. January follows December (if it's January 2007
and December 2006); 12 follows 11 and precedes 1. This is true of
dates; it is NOT true of numbers. If you're working with date data -
why on Earth don't you want to use a date datatype!?

Any easy way for me to do this month query, i am not an expert in access and
is getting very frustrated with this month query. If possible, is there any
possible access samples with just Month Query. I checked out the Allen
Browne, but couldn't figure out at all with that code.

It's not working because you are insisting on using THE WRONG
DATATYPE.

John W. Vinson[MVP]