Calculated Field in Crosstab Query Needs Rounding (1 Viewer)

ALewis06

Registered User.
Local time
Yesterday, 20:48
Joined
Jun 21, 2012
Messages
124
I have a very basic query with the following calculated field in it:

ProjRevNRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentNRC]*[Rev Flow Through],0)

I went into the properties if that field inside the query design and changed it to Standard with 0 decimal places and it worked fine.

But when I based a crosstab query off the query that contained the above calculated field, I cannot seem to get the numbers to format correctly. 1231313.424 is shown instead 1,231,313 and I don't have a line in the Properties window to even change the decimal places. It doesn't recognize when I change the format to Standard. I have tried using Round([ProjRevNRC]) which gets rid of the decimal places as desired but does not show commas.

I'm sure it's a simple part of the Round expression that I am missing but nothing has worked.:banghead:please help
 

Ranman256

Well-known member
Local time
Yesterday, 20:48
Joined
Apr 9, 2015
Messages
4,339
Dont do the math here in an if statement.....
Instead Create an extra field with the calc,and rounded in the base query.
THEN in another virtual field /query do:
IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[MyCalc],0)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:48
Joined
Feb 19, 2013
Messages
16,553
the format in the query is relevant only to that query, if you have a cross tab based on it, you need to set the format in the crosstab instead. If the crosstab is to then be used in a form or report, you set the format there instead
 

ALewis06

Registered User.
Local time
Yesterday, 20:48
Joined
Jun 21, 2012
Messages
124
Dont do the math here in an if statement.....
Instead Create an extra field with the calc,and rounded in the base query.
THEN in another virtual field /query do:
IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[MyCalc],0)

Ranman256 I am not sure I follow you. I did the calculated field in the base query, and in the base query it allowed me to right-click and just change the Properties of the result there. But no such luck once that field was summed in a crosstab.
 

Users who are viewing this thread

Top Bottom