Update where query, why does it not work?

[joris]

This should be simple but I can't figure it out....

I have a large table that contains NULL values and values like €0,00 in a field with the name price. I need to replace these values with €0,01

So I thought my rusty sql knowledge and google could help me solve this, but I am stuck...

To remove the null values I used the query:

UPDATE tblItem
 SET tblItem.prijs = "€0,01"
WHERE (((tblItem.prijs) Is Null));

and that worked well to remove the NULL values and changed them to €0,01

good

So I thought this would work to change €0,00 to €0,01

UPDATE tblItem 
SET tblItem.prijs = "€0,01"
WHERE (((tblItem.prijs) Is "€0,00"));

But it does not and I am puzzled why... It must be something very obvious that I am overlooking, but what?

[SteveF]

This should be simple but I can't figure it out....

I have a large table that contains NULL values and values like €0,00 in a field with the name price. I need to replace these values with €0,01

So I thought my rusty sql knowledge and google could help me solve this, but I am stuck...

To remove the null values I used the query:

UPDATE tblItem
 SET tblItem.prijs = "€0,01"
WHERE (((tblItem.prijs) Is Null));

and that worked well to remove the NULL values and changed them to €0,01

good

So I thought this would work to change €0,00 to €0,01

UPDATE tblItem 
SET tblItem.prijs = "€0,01"
WHERE (((tblItem.prijs) Is "€0,00"));

But it does not and I am puzzled why... It must be something very obvious that I am overlooking, but what?

If the data type for tblItem.prijs is Text, then

UPDATE tblItem 
SET tblItem.prijs = "€0,01"
WHERE (((tblItem.prijs) = "€0,00"));

should work.

However, I'm curious why you have the price of an item stored as a Text data type. Wouldn't Currency be a better choice? Text data cannot be used in calculations; numeric types (like Currency) can.

Steve

[June7]

Field prijs is a text field, not number?

Try = sign:

WHERE (((tblItem.prijs) = "€0,00"));

[joris]

Thanks for your fast reply June7!

Field prijs is a text field, not number?

Try = sign:

WHERE (((tblItem.prijs) = "€0,00"));

Tried the = sign, but that didn't work...
Data type is Currency, could that be the problem?

I actually solved it by using the find and replace function in Access, but I would still like to understand why it isn't working as I expected and how I would do it with an sql statement

[SteveF]

OK, for a Currency or any numeric data type, you need to remove the quotes inside the SQL. You may also need to experiment with the formatting of your numbers, as I'm not sure if the "," character is an accepted decimal separator in SQL. This should work; note the "." used for decimals:

UPDATE tblItem SET tblItem.prijs = 0.01 WHERE (((tblItem.prijs) = 0));

Also note that the € symbol is not stored as actual data in the table; it's either supplied by default from the formatting you set up in the table's design, or explicitly in the design of forms and queries.

Steve

[joris]

Thanks SteveF that query works! Good to learn some more about sql in access.

This forum is really an amazing place to learn about access. I really appreciate it.

[rohini]

hey guys i have a similar doubt for find and replace...the problem i have here is that the thing i want to find a part of the whole cell

example:
the cell is 'i love tennis'
and i want to change it to
'i love cricket'

can you please tell me how to do this...thanks

[rohini]

hey...i havent used access much so i have no clue where to put this 'update' statement...sorry and thanks

also i dont want to use find and replace because i want to automize everything..a lot of things to happen at a single click of the button as they say...

[rohini]

i used your code:

UPDATE tblItem  SET tblItem.prijs = "€0,01" WHERE (((tblItem.prijs) Is Null));

and it works fine for a single field

but what if i want to implement it on two fields simultaneously depending on each individually
as in
if tblItem.prijis = Null then set tblItem.prijis = "0"
and if tblItem.hello = Null then set tblItem.hello = "0"

[June7]

UPDATE tblItem SET prijs = Nz(prijs), hello = Nz(hello);

Are prijs and hello text or number fields?

[rohini]

UPDATE tblItem SET prijs = Nz(prijis), hello = Nz(hello);

thanks a lot!