Avatar of wlwebb
wlwebb
Flag 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?
Microsoft Access

Avatar of undefined
Last Comment
wlwebb

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Tony Barkdull

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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.
wlwebb

ASKER
Ah ha, So how can I change that stmt to format it as a Currency NUMBER.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
wlwebb

ASKER
Forget that last.  Just found it.....CCur(expr).  Thanks all!
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Have you tried formatting on the query / field property sheet directly?

mx
Capture1.gif
Rey Obrero (Capricorn1)

right click on the column > properties

Format  Currency
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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