Formatting number columns in a report

[Jim Hess]

I have a report that is based on a rather complicated query. Some of the
columns in the query are calculated values with a lot of Case conditions.
When I put these columns on a report I don't have any formatting options.
Some of them display correctly with commas and two decimal places while
others only display the number and rounded decimal amounts. I have tried
typing in my own formatting in the properties window but it doesn't work.
And I haven't been able to find the right code to put into an event
procedure. I would appreciate suggestions. Using Access 2002.

 

[John Spencer]

As a guess your calculated values are getting typed as string values. That
can happen for a variety of reasons, but one of the more frequent reasons is
that you have one or more options that return either a zero-length string or a
specific string (such as "N/A").

One solution might be to force the type of your calculated expressions or use
the format function in the calculation or ...

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jim Hess]

Thank you for your suggestion. Can you please tell me how to wrap this query
column with the format function?

Current Mnth:
Switch(Month(Now())=11,Nz([SalaryDetail]![OCT]),Month(Now())=12,Nz([SalaryDetail]![NOV]),Month(Now())=1,Nz([SalaryDetail]![DEC]))

 

[Duane Hookom]

The Format() function is what John was suggesting you use. He was hinting
that it might be causing your issues. You can try wrap your expression in
Val() or CDbl() or Int()....

Current Mnth:
Val(
Switch(Month(Now())=11,Nz([SalaryDetail]![OCT]),Month(Now())=12,Nz([SalaryDetail]![NOV]),Month(Now())=1,Nz([SalaryDetail]![DEC])))

Have you considered normalizing your data so you don't have to use this type
of expression?
I expect if you have a field for every month, you could use:
Current Mnth: Val(Choose(Month(Now()),[Jan],[Feb],[Mar],[Apr],.etc..,[Dec]))

--
Duane Hookom
Microsoft Access MVP

Thank you for your suggestion. Can you please tell me how to wrap this query
column with the format function?

Current Mnth:
Switch(Month(Now())=11,Nz([SalaryDetail]![OCT]),Month(Now())=12,Nz([SalaryDetail]![NOV]),Month(Now())=1,Nz([SalaryDetail]![DEC]))

As a guess your calculated values are getting typed as string values. That
can happen for a variety of reasons, but one of the more frequent reasons is
that you have one or more options that return either a zero-length string or a
specific string (such as "N/A").

One solution might be to force the type of your calculated expressions or use
the format function in the calculation or ...

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

What do you want if Month(Now) is any other value than 1,11, or 12? Do you
want null, 0, the current month's number - 2 to 10?

Current Mnth:
IIF(Month(Now()) NOT IN (1,11,12), NULL,
CLng(Switch(Month(Now())=11,Nz([SalaryDetail]![OCT],0)
,Month(Now())=12, Nz([SalaryDetail]![NOV],0)
,Month(Now())=1,Nz([SalaryDetail]![DEC]),0))

If you want the current month replace NULL with Month(Now()), if you want, 0
then replace null with zero - no quotes.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jim Hess]

What do you want if Month(Now) is any other value than 1,11, or 12? Do you
want null, 0, the current month's number - 2 to 10?

Current Mnth:
IIF(Month(Now()) NOT IN (1,11,12), NULL,
CLng(Switch(Month(Now())=11,Nz([SalaryDetail]![OCT],0)
,Month(Now())=12, Nz([SalaryDetail]![NOV],0)
,Month(Now())=1,Nz([SalaryDetail]![DEC]),0))

If you want the current month replace NULL with Month(Now()), if you want, 0
then replace null with zero - no quotes.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

The Format() function is what John was suggesting you use. He was hinting
that it might be causing your issues. You can try wrap your expression in
Val() or CDbl() or Int()....

Current Mnth:
Val(
Switch(Month(Now())=11,Nz([SalaryDetail]![OCT]),Month(Now())=12,Nz([SalaryDetail]![NOV]),Month(Now())=1,Nz([SalaryDetail]![DEC])))

Have you considered normalizing your data so you don't have to use this type
of expression?
I expect if you have a field for every month, you could use:
Current Mnth: Val(Choose(Month(Now()),[Jan],[Feb],[Mar],[Apr],.etc..,[Dec]))

Thank you everyone. The Val function did the trick.