Format as Euro in record source

[Tony2021]

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

[#2]

@Tony2021 

Try placing the formatting in the Format property of the appropriate field in the query design editor's Property Sheet instead.

[#3]

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?

[#4]

@Tony2021 

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

[#5]

@George Hepworth 

George, I get a data type mismatch if I use "€#,##0.00" as above. I thought that maybe its because of the format I have on [AmountOrig] at the table level but I dont think so?

[#6]

Oh, you used Double for the field size, not currency. I assumed since this is a Euro amount, you'd have defined it as Currency. I would have to check further to see if that makes any difference, though.

On the other hand, it often helps to include the actual SQL of the query in question to help spot issues.

[#7]

@George Hepworth 

George, please see below the entire SQL of the Union query.  I have bolded the field.  It currently has the "Standard" format applied.

SELECT tblFXParent.IDFXParent, tblFXParent.DateEntered AS [Date Of Tx], tblFXParent.DateSettle, tblFXParent.DateSettle AS ParentSettle, "" AS ChildSettle, tblFXParent.Vendor, IIf([Vendor]=48,"Bladt",IIf([Vendor]=47,"EEW",IIf([Vendor]=64,"Windar","***NotFound"))) AS CoName, format(tblFXParent.AmountOrig, "Standard") AS Amount, tblFXParent.AmountOrig AS ParentAmt, "Parent" AS [Group], Switch([tblFXParent].[DateSettle]<=Date(),"In The Past",[tblFXParent].[DateSettle] Between Date() And DateAdd("m",1.5,Date()),"Coming Due 45 Days",True,"Greater 45 Days") AS GroupDate, Nz([AmountOrig],0)+Nz(DSum("AmountRoll","tblFXRollsChild","[IDParentfk]=" & [IDFXParent]),0) AS [Balance EURO], "Trading" AS PmtOrTrade, tblFXParent.Bank, tblBanks.BankName, tblFXParent.Comments AS CommentsP, "" AS CommentsC, IIf(Nz([AdditionalYN],"")=0,"","Yes") AS AddtlFX, "" AS InvoiceYN, tblFXParent.EnteredSAPYN AS EntSAPP, "" AS EntSAPC, "N/A" AS FromDate, "N/A" AS ToDate, "" AS InvDueDate, "" AS RateChild, tblFXParent.Rate AS RateParent, Format([Amount]*[RateParent],"Currency") AS USD, tblFXParent.Rate AS Rate, 0 AS InvAmt
FROM tblFXParent LEFT JOIN tblBanks ON tblFXParent.Bank = tblBanks.BankID
WHERE (((tblFXParent.DateEntered) Is Null Or (tblFXParent.DateEntered) Between Date()-80 And DateAdd("m",3,Date())) AND ((tblFXParent.DateSettle) Is Null Or (tblFXParent.DateSettle)<DateAdd("m",6,Date()))) OR (((tblFXParent.DateSettle) Is Null Or (tblFXParent.DateSettle) Between Date()-80 And DateAdd("m",6,Date())))

UNION ALL

SELECT tblFXRollsChild.IDParentfk, tblFXRollsChild.DateOfTx, tblFXRollsChild.DateSettle, "" AS ParentSettle, tblFXRollsChild.DateSettle AS ChildSettle, tblFXParent.Vendor, IIf([Vendor]=48,"Bladt",IIf([Vendor]=47,"EEW",IIf([Vendor]=64,"Windar","***NotFound"))) AS CoName, format(tblFXRollsChild.AmountRoll,"Standard") AS Amount, "" AS ParentAmt, "Child" AS [Group], Switch([tblFXRollsChild].[DateSettle]<=Date(),"In The Past",[tblFXRollsChild].[DateSettle] Between Date() And DateAdd("m",1.5,Date()),"Coming Due 45 Days",True,"Greater 45 Days") AS GroupDate, Nz([AmountOrig],0)+Nz(DSum("AmountRoll","tblFXRollsChild","[IDParentfk]=" & [IDFXParent]),0) AS Balance, "Trading" AS PmtOrTrade, tblFXParent.Bank, tblBanks.BankName, "" AS CommentsP, tblFXRollsChild.Comments AS CommentsC, "" AS AddtlFX, tblFXRollsChild.InvoiceYN, "" AS EntSAPP, tblFXRollsChild.EnteredSAPYN AS EntSAPC, tblFXRollsChild.DateFrom, tblFXRollsChild.DateSettle AS DateTo, tblFXRollsChild.InvDueDate, tblFXRollsChild.Rate AS RateChild, "" AS RateParent, Format([Amount]*[RateChild],"Currency") AS USD, tblFXRollsChild.Rate AS Rate, Format(Nz([tblFXRollsChild.InvAmt],0),"Standard") AS InvAmt
FROM (tblFXParent LEFT JOIN tblBanks ON tblFXParent.Bank = tblBanks.BankID) RIGHT JOIN tblFXRollsChild ON tblFXParent.IDFXParent = tblFXRollsChild.IDParentfk
WHERE (((tblFXRollsChild.DateOfTx) Is Null Or (tblFXRollsChild.DateOfTx) Between Date()-80 And DateAdd("m",3,Date())) AND ((tblFXRollsChild.AmountRoll)<>0) AND ((tblFXRollsChild.DateFrom) Is Null) AND ((tblFXRollsChild.DateSettle) Is Null Or (tblFXRollsChild.DateSettle)<DateAdd("m",6,Date()))) OR ((((tblFXRollsChild.DateSettle) Is Null Or (tblFXRollsChild.DateSettle) Between Date()-80 And DateAdd("m",6,Date())) And (tblFXRollsChild.DateSettle)<>[DateFrom]));

[#8 · Accepted solution]

@Tony2021 

You should be able to change the format to

Format(tblFXParent.AmountOrig,"€#,##0.00") AS Amount

I 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.

[#9]

George, I think I am going to pass on the format as €#,##0.00. I notice that the alignment is off when compared to other values that do not have this format but are in the same column in the Union so it makes reading in datasheet difficult. I think this is due to the format applied as string as you mentioned and I did not know it converts it to a string.

I can format as needed in the report without any issues so I will just leave the raw data as is and forget about the €#,##0.00 format.

Indeed I thank you for the assistance once again.