Update Query for Multiple Formats

[jdd3110]

I am trying to update an Access field for pricing for a quote log. Some of the records have the price quote and some have NQ for "No Quote" I would like to make the records that are a numeric value appear as currency and leave the "NQ"s as text. Is this possible?????

 

[Golom]

If some of your records contain the string "NQ" then I infer that the field is a text field even though other records contain a text string that looks like a number. You can, in a query, produce a result that does what you want with the construct

Select ..., IIF(QuoteField = "NQ', QuoteField,
                Format(Val(QuoteField),"$#,###.00")) As [Quote], ...

That should produce records that appear like

 Quote

 $1,234.56
 NQ
 $345.67

Note that you will not be able to actually use those values as numbers because they are really character strings.