Update query with comma as a separator

[webisti]

Hi
I have a update query where I want to update a table.Field it based on a criteria
The price field in the table orders is called Price_USD and in this format : "#,###.00" (number ->double->standard)
I want in the update query to insert a comment like "Check IT" on the field Comment when the Price_USD is over 9,999
using the comma in the criteria is not possible because it gives an error Data type mismatch.



Any ideas on how to do that?

Thanks a lot
Webisti

[ItsMe]

I would use a label on a form and dynamically adjust the label's properties. The other option, if you truly desire to have it at the table level, would be to add a Boolean field or text field to your table and update that.

EDIT:
A third option would be to create an alias in your query and concatenate the text to the number field. You could use an Iif function in the alias too.

[webisti]

or would you use vba?

[webisti]

He is the code in sql

UPDATE Orders SET Orders.Comment = "Check- Value over 10K USD"
WHERE (((Order.PRICE_USD)>"9,999.00"));

How to make it work to update the comment..

[ItsMe]

I think I see the problem.

Instead of
WHERE (((Order.PRICE_USD)>"9,999.00"));

try
WHERE (((Order.PRICE_USD)> 9999));

[webisti]

I had it like that before but was not working....cannot figure out why.

[ItsMe]

Specificaly to the problem you are adressing regarding the UPDATE query. It is not making sense why, as you stated already. Have you tried using the query builder and adding

 >9999

to the criteria field? What does the SQL look like using the query builder?





The other issue is that you are updating a field in a table based on a calculation. So I will comment on that point. It is not associated with the problem you are posting about but, it is generaly not a good idea to store calculated data. So something like the third option I offered earlier should be considered.

You could create a select query based on the table Orders. Create an alias that has an IIf. So you would create a new column by entering the following in a blank field.

MyNewAlias: IIf([Order].[PRICE_USD] > 9999.99, "Check- Value over 10K USD", "Acceptable")

You need to verify the data type of Price_USD

[webisti]

Thanks ITsMe

I did used your third option by creating another query where it gives the result I want and in the original query updating the comment with "MyNewAlias".
It worked smoothly, thanks again.

Webisti