Link to home
Start Free TrialLog in
Avatar of wlwebb
wlwebbFlag for United States of America

asked on

Access query Expression - Convert to Currency Formatted Number

This is a follow on question to one earlier today.  That question discussed summing of Accountant AJE's based upon acct_numb and PdEndDate.

The solution brings in the correct total but I want the query to have the total formatted as a number in currency format.

Currently the code I used in the Builder is as follows:
AJEs: (FormatCurrency(nz([qryAcctntAJETtlbyPdEndAndAcctNbr].[Sum Of AJE],0),2))

When I use the results in subsequent queries it isn't seeing that number as a currency number.  Am I doing something wrong?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wlwebb

ASKER

That works.  I will give Cap1 the majority of points since he was first.  One question though before finalizing.

Why on the qry results is that number left justified?  Any other number that is currency formatted is right justified.  Just a pet peeve.  When I go to the home tab and select that column in the results I can not select the right justified formatting.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wlwebb

ASKER

PS One other issue.  On my qry table I have selected the "TOTALS" of records.  For the other columns that it brings in I can tell it to "Sum" the column.  However for that AJE's column the only options it gives is None or Count.  There is no Sum option.
Avatar of wlwebb

ASKER

Ah ha, So how can I change that stmt to format it as a Currency NUMBER.
Avatar of wlwebb

ASKER

Forget that last.  Just found it.....CCur(expr).  Thanks all!
Have you tried formatting on the query / field property sheet directly?

mx
Capture1.gif
right click on the column > properties

Format  Currency
Avatar of wlwebb

ASKER

Tried that earlier and the "Format" section was blank.  No options.  Probably was trying it when my expression was incorrect.  I see that now.  Thanks