Using IIf with Dates

[Guest]

My date data is formatted as short date (9/1/04). I have a grouped query by
month (9,10,11,12) using the Month format MonthName:Month([CreatedDate]). I
am trying to create an Iif statement to change the number to the name of the
month.

=IIf([MonthName]=9,"September",IIf([MonthName]=10,"October",IIf([MonthName]=11,"November",IIf([MonthName]=12,"December","Invalid month"))))

I keep receiving Error# when I run my report. I have even enclosed the month
number in "", i.e. "9".

If there is any easier way to format my month to show the month name in the
query, that would be helpful.
Thanks!

 

[Rick B]

Why not use the built in format function to pull the month name?

Format([CreatedDate],"mmmm")



Rick B

My date data is formatted as short date (9/1/04). I have a grouped query by
month (9,10,11,12) using the Month format MonthName:Month([CreatedDate]). I
am trying to create an Iif statement to change the number to the name of the
month.

=IIf([MonthName]=9,"September",IIf([MonthName]=10,"October",IIf([MonthName]=
11,"November",IIf([MonthName]=12,"December","Invalid month"))))

 

[Guest]

That works great. Only problem is now, it is sorting alpha. I want it to show
September, October, November, December....ideas?

Why not use the built in format function to pull the month name?

Format([CreatedDate],"mmmm")



Rick B

My date data is formatted as short date (9/1/04). I have a grouped query by
month (9,10,11,12) using the Month format MonthName:Month([CreatedDate]). I
am trying to create an Iif statement to change the number to the name of the
month.

=IIf([MonthName]=9,"September",IIf([MonthName]=10,"October",IIf([MonthName]=
11,"November",IIf([MonthName]=12,"December","Invalid month"))))

I keep receiving Error# when I run my report. I have even enclosed the month
number in "", i.e. "9".

If there is any easier way to format my month to show the month name in the
query, that would be helpful.
Thanks!

 

[Rick B]

You may need to have both in your query (but uncheck the one that pulls the
numeric month) and make that your sort.

That works great. Only problem is now, it is sorting alpha. I want it to show
September, October, November, December....ideas?

Why not use the built in format function to pull the month name?

Format([CreatedDate],"mmmm")



Rick B

My date data is formatted as short date (9/1/04). I have a grouped

query
by

month (9,10,11,12) using the Month format

MonthName:Month([CreatedDate]).
I

am trying to create an Iif statement to change the number to the name

of
the

month.

=IIf([MonthName]=9,"September",IIf([MonthName]=10,"October",IIf([MonthName]=
11,"November",IIf([MonthName]=12,"December","Invalid month"))))

I keep receiving Error# when I run my report. I have even enclosed the month
number in "", i.e. "9".

If there is any easier way to format my month to show the month name

in
the

query, that would be helpful.
Thanks!

 

[Guest]

Thanks, that did it!

You may need to have both in your query (but uncheck the one that pulls the
numeric month) and make that your sort.

That works great. Only problem is now, it is sorting alpha. I want it to show
September, October, November, December....ideas?

Why not use the built in format function to pull the month name?

Format([CreatedDate],"mmmm")



Rick B


My date data is formatted as short date (9/1/04). I have a grouped query
by
month (9,10,11,12) using the Month format MonthName:Month([CreatedDate]).
I
am trying to create an Iif statement to change the number to the name of
the
month.


=IIf([MonthName]=9,"September",IIf([MonthName]=10,"October",IIf([MonthName]=
11,"November",IIf([MonthName]=12,"December","Invalid month"))))

I keep receiving Error# when I run my report. I have even enclosed the
month
number in "", i.e. "9".

If there is any easier way to format my month to show the month name in
the
query, that would be helpful.
Thanks!