If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formatting number columns in a report
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. |
#2
|
|||
|
|||
Formatting number columns in a report
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 wrote: 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. |
#3
|
|||
|
|||
Formatting number columns in a report
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])) "John Spencer" wrote: 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 wrote: 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. |
#4
|
|||
|
|||
Formatting number columns in a report
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 "Jim Hess" wrote: 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])) "John Spencer" wrote: 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 wrote: 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. |
#5
|
|||
|
|||
Formatting number columns in a report
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 Duane Hookom wrote: 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])) |
#6
|
|||
|
|||
Formatting number columns in a report
"John Spencer" wrote: 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 Duane Hookom wrote: 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. |
Thread Tools | |
Display Modes | |
|
|