Forum Discussion
Format as Euro in record source
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.
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
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?
- 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.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 ALLSELECT 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]));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.