Display Currency in a Combo Box Dropdown

[Learn2010]

I have a combo box using the PartID as the datasource of the field. I use a query as the Row Source. Based on the PartID selected, the dropdown list will display the Part, the Part Category, and the Selling Price. The Selling Price in the original table is formatted as a Double, Standard, 2. When displaying the numbers, it will not display as that format. For instance, 325.00 will display as 325, 214.50 will display as 214.5, and so on.

In the query field I tried formatting in various ways, Standard, 2 and Currency, 2. They still display the same way.

Can someone lead me in the right direction on this?

Thank you.

 

[MarkK]

You can format or process data in a query, like...

SELECT t.ID, Format(t.SomeField, "#.00") As SomeField
FROM SomeTable As t

...or...

SELECT t.ID, Round(t.SomeField, 2) As SomeField
FROM SomeTable As t

...which, when I test it here, seems to alter how the data is displayed in a combo box.

 

[Ranman256]

prices dont need double. Thats why they have the currency data type.
but did you try:
in the query, right click on the Price column, Format, set to currency, save.
this should carry over to the combo box.

 

[Learn2010]

Ranman,

I did that and it didn't work. Also, to the others, I used an Access query, not SQL.

 

[MarkK]

Access queries are all written in SQL.

 

[Learn2010]

Cute, but not helpful.

 

[Ranman256]

then in the query use FORMAT(myField, "$0,000.00")

 

[MarkK]

I'm giving you accurate information about queries, and about how to control the format of data in your combo. If the information is not helpful, please feel free to describe what part of it you don't understand. I'm not trying to be cute, so you may be misreading my posts.

 

[Gasman]

Format(field,"Currency") in the query designer works for me?

 

[Learn2010]

I will try to do it in SQL and get back with you tomorrow.

Thanks.

 

[Learn2010]

Ranman,

I used SQL and it worked. Here is what I used:

SELECT tblParts.Part, tblParts.Category, Format(tblParts.SellingPrice,"$0,000.00") AS Price, tblParts.PartID
FROM tblParts
ORDER BY tblParts.Part;

This gives me a leading 0 ($0,325.56). I tried to get rid of it but can't. What do I need to do to make it say ($325.56) when it is less than one thousand dollars?

Thanks.

 

[Learn2010]

Ranman,

I changed it to the code below and it worked. Thanks very much.

SELECT tblParts.Part, tblParts.Category, Format([tblParts].[SellingPrice],"Currency") AS Price, tblParts.PartID
FROM tblParts
ORDER BY tblParts.Part;