Format Conatenated fields in Select Statement

[KWarzala]

Hello,

I'm trying to format a combo box to show [ProductItemDescription] & " - " & [PriceAmount] so basically it will look like: Large Bowl - $4.75



Right now i have:
"SELECT ProductItemDescription & FormatCurrency([PriceAmount],2) AS Product" &_
"FROM tbl_MerchandiseProduct" &_
"WHERE DepartmentName = 'Example'"

And looks like this: LargeBowl$4.75

I tried this:
"SELECT ProductItemDescription & " - " & FormatCurrency([PriceAmount],2) AS Product
etc...
but that didn't work

Thanks for any help!

[June7]

The second example works for me.

Why doesn't it work, what is the result?

Why do you need this concatenated in combobox? What do you want to do with it?

[KWarzala]

It just says Run-time error '13': Type Mismatch

It's a lookup for items that don't have a barcode to scan, so they first select a dropdown for department, then for Category (which is where this select statemtent is Category(AfterUpdate) and sets me.cboProduct.RowSource) and since ProductItemDescription isn't always very descriptive i want the price to show up with it so they know they are selecting the correct product

[rpeare]

"SELECT ProductItemDescription & FormatCurrency([PriceAmount],2) AS Product" &_
"FROM tbl_MerchandiseProduct" &_
"WHERE DepartmentName = 'Example'"

if this is your code it may be bombing out because you are not putting spaces in between your rows, try:

"SELECT ProductItemDescription & " - " & FormatCurrency([PriceAmount],2) AS Product " &_
"FROM tbl_MerchandiseProduct " &_
"WHERE DepartmentName = 'Example'"

Notice the extra space at the end of each line

Also... rae you sure your priceamount is stored as a number, if it is try format([PriceAmount], "Currency") instead

[June7]

Okay, once they select the Product/Price what happens? How will you use this concatenated value? Is it the parameter for query filter?

[KWarzala]

I actually have it all typed on one row, i just typed the example like that to make it easier to read

I tried using Format([PriceAmount],"Currency") but as soon as i finish typing it says Compile error: Expected: end of statement so i switched to FormatCurrency()

Here's all the code exactly as i have it:

Private Sub cboDepartmentName_AfterUpdate()
Me.cboItemCategory.RowSource = "SELECT DISTINCT ItemCategory FROM tbldbo_MerchandiseProductPrice WHERE ItemCategory <> 'N/A' AND DepartmentName = '" & Me.cboDepartmentName & "'" & "ORDER BY ItemCategory"
End Sub

Private Sub cboItemCategory_AfterUpdate()
Me.cboProduct.RowSource = "SELECT ProductItemDescription & FormatCurrency([PriceAmount],2) AS Product FROM tbldbo_MerchandiseProductPrice WHERE DepartmentName = '" & Me.cboDepartmentName & "' And ItemCategory = '" & Me.cboItemCategory & "'"
End Sub

So it's an unbound form with 3 unbound combo boxes, the first one is cboDepartmentName, after you select from that dropdown cboItemCategory is updated, once you select from cboItemCategory is when i would like to set cboProduct with the fields and format i listed

[June7]

Try just putting the SQL directly in the RowSource property of each combobox. Then the code will just need to requery the combobox. Works for me.

SELECT ProductItemDescription & " - " & FormatCurrency([PriceAmount],2) AS Product FROM tbl_MerchandiseProduct WHERE DepartmentName = [cboDepartmentName];

Still don't know how you intend to use the concatenated value.

[rpeare]

Um, are you changing the table that drives the data in the combo boxes? If you aren't, that is to say if your CBOITEMCATEGORY field is always going to draw it's information from tbldbo_MerchandiseProductPrice, you don't need to populate the rowsource, you can just have a query that drives the combo box

SELECT DISTINCT ItemCategory FROM tbldbo_MerchandiseProductPrice WHERE ItemCategory <> 'N/A' AND DepartmentName = '" & forms!<YOUR FORM NAME HERE>!cboDepartmentName

Then when you change the department name you just issue a requery command (on change, or on exit for example) like:

cboItemCategory.requery

You don't have to build this SQL code every time unless the source table it's retrieving the list from changes.

[KWarzala]

Thank you for all your help! i used the SELECT statement as the row source and added the requery after update and it does what i want it to do!

I appreciate the help!