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

how to format a value in a query in access to a different curreny symbol

i want to get in a access query a currency value with another currency symbol and not the windows default. is that possible?

this is my code: Format(total,"Currency") AS TotalEx

if is not possible is there some way to add a word to a num value (visualy) like 100 with the word "euro"
0
bill201
Asked:
bill201
  • 4
  • 2
  • 2
  • +3
5 Solutions
 
jerryb30Commented:
Cab you not format the field as currency/euro in table design?
Otherwise, look here:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21695968.html
0
 
jerryb30Commented:
Can you not....
0
 
bill201Author Commented:
thanks alot for your answer

i will be more specific i dont need a euro symbol (it's was an example) i need a Shekel symbol
like there http://en.wikipedia.org/wiki/Shekel_sign
where can i put this
format ([price],"shekel Symbol")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
vguzmanIT ManagerCommented:
Can you just concatenate the symbol and your price ? like:
"Shekel Symbol" & " " & [total]
0
 
bill201Author Commented:
but i want that it's will be a number value and not a text string
0
 
Gustav BrockCIOCommented:
If you concatenate a char and a number you will get a string.
If you also need the numerice value, have two columns.

/gustav
0
 
bill201Author Commented:
its not possible that the char will just be a format visualy?
0
 
Patrick MatthewsCommented:
In your table design, select that column, and in Format for the column properties use something like this:

¿#,##0.00;(¿#,##0.00)

It worked for me.

Q-27976657.mdb
0
 
Gustav BrockCIOCommented:
Yes, then you would use the Format property:

\€#,##0.00

Replace € with your Sekel sign (the font here seems not to hold it).

/gustav
0
 
Patrick MatthewsCommented:
That was supposed to be a shekel symbol, but it would not post.  The sample file uses the symbol correctly.
0
 
jyk_ausCommented:
I use multiple currencies, including the shekel  regularly.

The way I approach this particularly problem is by having a 'Currencies' table that holds the currencies signs amongst other thing - the *minimum* you would need is:

[Currency_ID]  = PK
[Currency Name]
[Currency ISO] (eg USD, ILS, EUR etc), Required, zero length not allowed
[Currency Symbol]  - not required as some currencies don't have a specific symbol or I dont have symbols for such currencies.

All these fields are Indexed, duplications not allowed - you may allow duplication in the symbol but I would not recommend it.

If you are using more than one currency that uses the dollar sign and you allow duplications it is still  a good practice to distinguish beteen them by using US$, A$, C$, NZ$ etc  as symbol, the same gors for the franc sign, if you are using it.

I  have a public function, say CurrencySymbol(), that extrct the symbol using the  DLookup() and if it returns Null for the symbol it uses the [Currency ISO] as an alternative, e.g.

++++++++
Public Function CurrencySymbol( _
idCurrency As Long) _
As String

    Dim tmpSymbol As Variant, tmpCode As String
   
    tmpSymbol = DLookup("[Currency Symbol]", "Currencies", "[Currency_ID]=" _
    & idCurrency)
    tmpCode = Nz(DLookup("[ISO Code]", "Currencies", "[Currency_ID]=" _
    & idCurrency), "")
   
    CurrencySymbol = IIf(IsNull(tmpSymbol) Or Len(CStr(tmpSymbol)) = 0, tmpCode, _
    CStr(tmpSymbol))
   
   
End Function
+++++++++

In order to display the currency symbol in a query, I link the query to the currency table on [Currency_ID] and the rest is simple:

YourField=CurrencySymbol([Curreny_ID]) & Format(YourAmount,"#,##0.00")

Note: This assumes that *all* the currencies that you are using are sub-divisible by 100 (e.g 100 cents in a dollar, 100 agorot in a Skekel etc), if this is not the case, such as in Yen  and some other currencies, the query is slightly more compleicated  if you wish to eliminate the decimal for such currencies.

I hope that this is helpful

Best regards
Jacob
0
 
bill201Author Commented:
thanks alot  for every one
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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