Formatting Currency (1 Viewer)

[Larnu]

Hi All,

I was wondering if there's a way to designate which currency is shown using the formatcurrency statement. i have a table with values in, but they are representative of the currency of the account they are linked to. As a result I have £, € and $ values in there.

I'll be using a query to export each account separately, which means that if i can set formatcurrency to always give back a certain type for the entire statement that's fine. At the moment, I have a statement picking up the currency of the account and then concatenating that to the value. This works, but it gives the value in text format, so sums won't work on the excel sheet I export to.

The current SQL statement I have is below. Can anyone give some insight? Thanks.

SELECT tblSuspenseTransaction.[Week No], tblSuspenseTransaction.Date, tblSuspenseTransaction.CRIS, tblSuspenseTransaction.Advisor, tblSuspenseTransaction.[Card Number], IIf([Value]>0,tblSuspenseAcc.[Account Currency] & Round([Value],2),tblSuspenseAcc.[Account Currency] & "0.00") AS [Credit Transfer], IIf([Value]<0,tblSuspenseAcc.[Account Currency] & Round([Value]*-1,2),tblSuspenseAcc.[Account Currency] & "0.00") AS [Debit Transfer], tblSuspenseTransaction.[Authorised Signature], (tblSuspenseAcc.[Account Currency] & Round(tblSuspenseTransaction.[Opening Balance],2)) AS [Acc Opening Balance], (tblSuspenseAcc.[Account Currency] & Round(tblSuspenseTransaction.[Closing Balance],2)) AS [Acc Closing Balance], tblSuspenseTransaction.Initials, tblSuspenseTransaction.Reason
FROM tblSuspenseTransaction, tblSuspenseAcc
WHERE (((tblSuspenseTransaction.ID)<>0) AND ((tblSuspenseAcc.[Account ID])=[tblSuspenseTransaction].[Account]))
ORDER BY tblSuspenseTransaction.ID DESC;

 

[John Big Booty]

Why not simply export the currency indicator and currency value as separate fields

 

[Larnu]

Hi John,

Not sure what you're implying in the sense of what I'm already doing, or having it as two fields. Unfortunately neither would work as the report goes elsewhere in the business. As the reports they want to receive are very cut and paste, I need to make this reflect the previous ones as much as possible. Adding an extra field therefore wouldn't work, as new/old data would become misaligned. The format above would mean that a simple =sum() won't work on the values as they're being treated as text, rather than a currency format decimal "(£/€/$)0.00". Hence my question.

Thanks.

 

[JHB]

Do you have some sample data to show, show also how you want the result?

 

[Larnu]

Hi JHB,

Hope this is useful. The actual export has 8 tabs, each sheet is generated using a new where clause in the query, so there would only be one currency on each tab. If Access therefore has a format([Value],"£" & 0.00) type function, which is treated as a value, this would work, as all currencies would follow that format in a single query.

Thanks.

 

[JHB]

Hi JHB,
If Access therefore has a format([Value],"£" & 0.00) type function, which is treated as a value, this would work, as all currencies would follow that format in a single query.

MS-Access has it more or less as I see it, then you can use "ORDER BY" or "GROUP BY" on the [Account Currency] field.

Hope this is useful. ...

Sorry, not exactly.

From the first thread I could read that you have problem in Excel by summing:

This works, but it gives the value in text format, so sums won't work on the excel sheet I export to.

I don't know how you export it, (you're not telling it ), but you can set the currency format for each column, manually or by code if you know the column(s) number.
I've attached a small example database for you, how you do it by code (the code is very simple, remember to change the path for "Destination2"):

  Dim dbs As Database, rst As Recordset, Destination2 As String
  Dim xlapp As Object 'Excel.Application
  Dim xlbook As Object 'Excel.Workbook
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("SELECT [Week No], Sum(Value) AS [Credit Transfer], [Account Currency] " _
  & "FROM tblSuspenseTransaction " _
  & "GROUP BY [Week No], [Account Currency]")
  
  Set xlapp = CreateObject("Excel.Application")
  ' Make it visible...
  xlapp.Visible = True
  Destination2 = "C:\Access programmer\DifferentCurrency.xls"
  Set xlbook = xlapp.Workbooks.Open(Destination2)
  If Not rst.EOF Then
    Do
      If rst![Account Currency] = "$" Then
        xlbook.Sheets("Dollar").Columns(3).NumberFormat = "[$$-409]#,##0.00"
      ElseIf rst![Account Currency] = "£" Then
        xlbook.Sheets("Sterling").Columns(3).NumberFormat = "[$£-809]#,##0.00"
      End If
      rst.MoveNext
    Loop Until rst.EOF
  End If

The picture shows the Excel sheet before (to the left side) and after the code has run, (to the right side).

Here is the link to the other formatting code. http://office.microsoft.com/en-us/excel-help/creating-international-number-formats-HA001034635.aspx

 

[Larnu]

Thanks again JHHB,

I was trying to avoid is doing the formatting in the export code itself, as it's a module I'm using the export different queries so therefore the same across the board.

From your message, it appears that this can't be done, so I'll manually format in excel after in an additional module.