• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1152
  • Last Modified:

Formatting Currency & Decimal Places in a Union Query

I have two working queries, which I have joined in a Union Query. The query almost gives me the results I want. The two queries are identical in setup and the fields "[TotalCostPerPound]" and "[NetWorth]" are both currency fields. I need them both to be formatted as currency with 4 decimal places. I've tried it without the formatting in the query, but couldn't get them to appear correctly in the report. So when I added the Currency formatting in the query itself, the report appears to be treating these fields as text fields, because when I try to sum either of them, I get a data type mismatch error.

How can I accomplish having these 4 fields formatted as Currency with 4 decimal places? Here is a complete copy of the Union Query.

Thanks for any assistance,
Carla

SELECT [ParentBldg], [PONbr] as [PO/Batch], [ProductCode], [Form], [Grade], [SumOfNetWeight], Format$([TotalCostPerPound],"Currency") As [TotalCostPerPound$], [CurrBldg], Format$([NetWorth],"Currency") AS [NetWorth$]
FROM
qryProductOnHandScrap
UNION ALL SELECT [ParentBldg], [BatchID] as [PO/Batch], [ProductCode], [Form], [Grade], [SumOfNetWeight], Format$([TotalCostPerPound],"Currency") as [TotalCostPerPound$], [CurrBldg], Format$([NetWorth],"Currency") As [NetWorth$]
FROM
qryProductOnHandProcessed
0
Carla Romere
Asked:
Carla Romere
  • 5
  • 5
  • 4
  • +2
2 Solutions
 
stevbeCommented:
Format and Format$ return strings so your suspicion is correct on the problem with SUM. You really should be doing the formatting in the report. What seems to be the problem with the report?

Steve
0
 
Steve BinkCommented:
Remove the "$" from "Format$".  There are two versions of the same the method - one returns an explicit string data type, the other returns a variant.  By using the "$" version, you are telling Access the data is definitely text.  
0
 
nikkilockeCommented:
Hi Hers2keep,

If you are doiung a Report, then remove all the formatting from the query.

Put the formatting on the field in the report itself (click on the field properties, and you will find the option to set a fixed number of decimal places).

Hope this helps,

Nikki
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
stevbeCommented:
I set the Format property to Currency and Decimal Places to 4 oin a textbox in a report and it looks good to me. Can you give us a couple of specific example of raw data and what you want it to look like?

Steve
0
 
GRayLCommented:
Use the CCur() function in place of the format() function to get four decimal places properly rounded.
0
 
stevbeCommented:
routinet: from the help file:
Format Function
Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.


so while the signature is a variant you are really getting a string ... try this in the immediate window

?Len(Format(Null,"mm/dd/yyyy"))

now try Len(Null)

Steve
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
How do I split the points for this question? Two people's answers were helpful.

Thanks,
Carla
0
 
GRayLCommented:
To split points, Click on "Split Points" link at the bottom. The Split Points page will open, showing "Accept" button and Points for Comment for each comment. Select one Accept button as the main answer. Fill in the points. The total points should equal those alloted to question. Fill in the grade at the bottom. Click "Submit".

As you have already awarded the points, you must ask Community Support to reopen the question.
0
 
Steve BinkCommented:
>>> Returns a Variant (String)

stevbe: Yes, the Format() method returns a string variant.  The Format$() method returns an explicitly delcared string data type.  For Access, there is a difference.  I came across this just recently as a fix for another issue...I'll see if I can locate the page.
0
 
GRayLCommented:
I'm still curious why your trying to create a silk purse out of a sows ear when there is a perfectly good function to do the job - ccur() - which she chose to ignore - I added downtroddenly ;-)
0
 
Steve BinkCommented:
Got me, GRayL.  Using CCur, it wouldn't matter which Format() method was used.  :)
0
 
stevbeCommented:
I always try to keep formatting in my Reports when it has no bearing on the calcutaed results, it would just be adding a layer of processing for the SQL engine.

did you try playing in the immediate window?

?Len(Format(Null,"mm/dd/yyyy"))

this returns 0 and must be returning a String otherwise the Len function would puke as shown in ...

?Len(Null)
0
 
GRayLCommented:
Which is why I leave format() alone when there are other functions precisely suited to the job.
0
 
Steve BinkCommented:
I understand what you're saying stevbe.  I'm still trying to locate the other page I saw recently that explained the difference between the two.  Perhaps I just read it wrong or misunderstood it, but Format() returns a variant data type, sub-typed as a string, while Format$() returns an explicit string.  It showed examples of why it was important and everything, but apparently I forgot to bookmark (like I usually do...<sigh>)
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
GRayL - I wasn't purposefully ignoring you - I'd just gotten the problem solved and hadn't been back in to ee.
I'm always up for learning something new though, how does CCur work? I've never used that function at all....

Carla
0
 
stevbeCommented:
routinet ... the only practical difference I have seen is that the $ functions *require* you to enter a string where their non-$ cousins can handle Null without blowing up.

Perhaps a varinat sub-typed as string allows the return to be implicitly converted to another type, which would make sense in your SUM solution above, but of course a string can only be a string.

Steve
0
 
Steve BinkCommented:
>>> non-$ cousins can handle Null without blowing up

Perhaps that is what I was reading.  That idea sounds familiar.  If I find what I read before, I'll let you know, but I think you just hit the main theme.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now