Forum Discussion
Format as Euro in record source
Hello Experts,
I need to format [Amount] as Euro (and red if negative) and I am not sure how to do this. In the below copy and paste, I have changed "Standard" to "Euro" but I get a data type mismatch.
I have also tried to replace the below "Standard" with €#,##0.00;[Red](€#,##0.00) but I dont think I can do that in the sql (I did get a syntax but I think the real problem is that I can not do this in the sql level). I can format the field on the report as €#,##0.00;[Red](€#,##0.00 and that works but I need to make that change iat the query level (ie when I press the button to view in datasheet since I dont always view the data in a report).
snipping a part from the Union query:
format(tblFXParent.AmountOrig,"Standard") AS Amount,
thank you
You should be able to change the format to
Format(tblFXParent.AmountOrig,"€#,##0.00") AS AmountI can't see anything else that doesn't look appropriate in that SQL, to be honest.
The Format() expression does convert the numbers to text, though. I wonder if there is something else consuming the query output that doesn't expect that result.
8 Replies
Try placing the formatting in the Format property of the appropriate field in the query design editor's Property Sheet instead.
- Hi George,
Since its a Union query I dont think I can apply a format to the field as such since I dont think a Union query can be viewed in design mode. ? It does apply that format when I break out the separate queries making up the Union since design is visible in the separate queries. I copied and pasted the separate queries with the formatting shown above but there is no formatting since I dont think it carries over to the Union.
Maybe I am doing something wrong?My oversight. I missed that you have a Union Query.
You are right that the formatting isn't going to be available .
That leaves the ability to format the amount with the Euro sign, but not with the red font.
Format([AmountOriginEuros],"$#,##0.00") AS AmountOrigEAsD, Format([AmountOriginDollarsD],"€#,##0.00") AS AmountOrigDAsE
