Solved

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

Posted on 2012-12-23
12
689 Views
Last Modified: 2012-12-24
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
Comment
Question by:bill201
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 63 total points
ID: 38717525
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38717527
Can you not....
0
 

Author Comment

by:bill201
ID: 38717561
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
 
LVL 5

Assisted Solution

by:vguzman
vguzman earned 62 total points
ID: 38717749
Can you just concatenate the symbol and your price ? like:
"Shekel Symbol" & " " & [total]
0
 

Author Comment

by:bill201
ID: 38718412
but i want that it's will be a number value and not a text string
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 62 total points
ID: 38718534
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:bill201
ID: 38718541
its not possible that the char will just be a format visualy?
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 63 total points
ID: 38718557
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 38718559
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38718561
That was supposed to be a shekel symbol, but it would not post.  The sample file uses the symbol correctly.
0
 
LVL 1

Accepted Solution

by:
jyk_aus earned 250 total points
ID: 38719381
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
 

Author Closing Comment

by:bill201
ID: 38719517
thanks alot  for every one
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now