Month Text Problem

[CJ]

Hi Groupies:

I have a parameter query that retrieves appointments based on the school
year of September to June.

The problem is that we need to be able to retrieve data for an entire month
at a time, not specific dates. For ease of entry, we would like to enter a
start month of September and an end month of February and retrieve all of
the appointments for September - February.

My query works fine if I set my Between statement based on the date field
and then use 9/1/2006 and 2/28/2007 as the paramenter inputs; however I
would like to make it work with the name of the month so I created a
calculated field of

MonthRequest: MonthName(Month([datAppointment]))

When I enter September and February as my parameter input I do not retrieve
the December information because D does not fall between F and S in the
alpahabet.

How can I make this work?

Thanks for the brain waves!!

 

[KARL DEWEY]

Try this --
MonthRequest: Format([datAppointment],"mmmm")

To use three letter months use this --
MonthRequest: Format([datAppointment],"mmm")

 

[John Spencer]

Entering a month name is complicated. You are really better off entering
the month numbers.

Field: datAppointment
Criteria: Between DateSerial(Year(Date()) + IIF([Enter Start Month]<9,1,0),
[Enter Start Month],1)
AND DateSerial(Year(Date()) + IIF([Enter End
Month]<9,1,0), [Enter End Month]+1,0)


To do that with month names you could use the expressions below in place of
the month numbers in the above expression
Month(CDate("1-" & [Enter Start Month])) and Month(CDate("1-" & [Enter
End Month]))

Criteria: Between DateSerial(Year(Date()) + IIF(Month(CDate("1-" & [Enter
Start Month]))<9,1,0), Month(CDate("1-" & [Enter Start Month])),1) AND
DateSerial(Year(Date()) + IIF(Month(CDate("1-" & [Enter End Month]))<9,1,0),
Month(CDate("1-" & [Enter End Month]))+1,0)

Since this was all entered directly into the posting, there is no guarantee
that there isn't a syntax error (missing or extra parentheses) in those
criteria statements - Especially the last one.

Of course both of them fail when you get to the following year (They work up
until December 31) and then you have to add more complications to the
criteria statement to get them to calculate the correct year or change the
criteria statement to subtract a year from the start date and not add a year
to the end date..

What this all adds up to is you are better off using a form to get the date
range and then calling the query (report) using the paremeters input from
the form.

 

[CJ]

Thanks for the input John and Karl.

I had thought of using a form with a calendar button and perhaps that is the
better, neater, way to go.

Thanks guys.
CJ

Entering a month name is complicated. You are really better off entering
the month numbers.

Field: datAppointment
Criteria: Between DateSerial(Year(Date()) + IIF([Enter Start
Month]<9,1,0), [Enter Start Month],1)
AND DateSerial(Year(Date()) + IIF([Enter End
Month]<9,1,0), [Enter End Month]+1,0)


To do that with month names you could use the expressions below in place
of the month numbers in the above expression
Month(CDate("1-" & [Enter Start Month])) and Month(CDate("1-" & [Enter
End Month]))

Criteria: Between DateSerial(Year(Date()) + IIF(Month(CDate("1-" & [Enter
Start Month]))<9,1,0), Month(CDate("1-" & [Enter Start Month])),1) AND
DateSerial(Year(Date()) + IIF(Month(CDate("1-" & [Enter End
Month]))<9,1,0), Month(CDate("1-" & [Enter End Month]))+1,0)

Since this was all entered directly into the posting, there is no
guarantee that there isn't a syntax error (missing or extra parentheses)
in those criteria statements - Especially the last one.

Of course both of them fail when you get to the following year (They work
up until December 31) and then you have to add more complications to the
criteria statement to get them to calculate the correct year or change the
criteria statement to subtract a year from the start date and not add a
year to the end date..

What this all adds up to is you are better off using a form to get the
date range and then calling the query (report) using the paremeters input
from the form.

Hi Groupies:

I have a parameter query that retrieves appointments based on the school
year of September to June.

The problem is that we need to be able to retrieve data for an entire
month at a time, not specific dates. For ease of entry, we would like to
enter a start month of September and an end month of February and
retrieve all of the appointments for September - February.

My query works fine if I set my Between statement based on the date field
and then use 9/1/2006 and 2/28/2007 as the paramenter inputs; however I
would like to make it work with the name of the month so I created a
calculated field of

MonthRequest: MonthName(Month([datAppointment]))

When I enter September and February as my parameter input I do not
retrieve the December information because D does not fall between F and S
in the alpahabet.

How can I make this work?

Thanks for the brain waves!!