Formating a Calculated Field

[Clut]

Hi,

I'm developing a database that contains various industrial equipment (Radiators, Silencers, Heat Exchangers etc).

My company buys each of these items in one of three currencies, GBP, USD or EUR.
When we draw up a quote for a customer to resell these items to, the customers quote will be in the customers local currency.

Example:
We buy Radiators in GBP. I have a radiators table that stores the price we pay for a radiator in GBP.

I have an American customer, who wants a radiator. The quotation I give to the American customer must be in USD.
I already have a field that stores the exchange rate between £ and $.

SELECT radiator model, radiator_price, radiator_price * exchangerate as [Customer Price in USD]

The above simplified SQL shows me the radiator the customer wants, the GBP cost to me, and the USD cost I will charge the US customer.

95 degree industrial noise stainless steel Radiator, £23,658.00, 18674.3


the GBP cost is fine, as it is already formatted in the table as currency (GBP), however, how can I make the [Customer Price in USD] field display in currency (USD)?


Apologies if this was a long winded way of asking a simple question

 

[Brianwarnock]

To format the field in a query right click it and then select properties from the menu that opens.

Brian

 

[Clut]

I tried right clicking, but had no joy.

In the query design view, I found a 'properties' item when right-clicking on the calculated field, but the 'Format' drop down box in the properties window was empty.

To add to the question, I am already formatting as currency, (select format(price * exchange,'currency') AS [Sale Price], but this gives me the calculated field in GBP (my local settings).

How can I force this to display as Euros, or Dollars?

Thanks

 

[JANR]

If you just want the Dollarsign in front of your calculation then something like this:

SELECT radiator model, radiator_price, "$"&(radiator_price * exchangerate) as [Customer Price in USD]​

This will make it a text field so no further calculation cannot be made, buy for display only the perhaps...
JR

 

[Clut]

JANR,

thanks, that is half the problem fixed.

If possible, I would like to display as true currency, with thousand seperators ($80,000), but if I can't get that to work, then your solution will be close enough.

 

[JANR]

In a query you can get thousand seperators by setting the property of your calculated field to standard, but not when it's converted to a text field. That said you have better control over the display of things if you use a report based on your query.

In the report you "add" the dollar sign if front of [Customer Price in USD] field

JR

 

[Brianwarnock]

Sorry i did not read your question properly, mindyou my drop down has euros in it , however you should try

Format(price*exchange,'$#,###.00)

Brian

 

[JANR]

Brian is absolutly correct, a little food in the gut and the mind is clear.

Just as a test this SQL does format to $.

SELECT Item, Price, Format((price*exhange),"$ #,###.00") AS USD
FROM tb1;

JR