Update Query (multiple fields)

[Guest]

I am updating one table (one time only) having 15 of the fields change from
null to 0. Text fields. Yes, I mean to do this. When my query updates one
field at a time, it updates fine.

I cannot get it to work if I ask it to update all 15 in the one query --
field1, field2, field3...etc. Each is set to update to "0" (w/quotes) and
the criteria is "Is Null", all criteria on same line (I also tried 'or').

Am I going to have to enter 15 queries? I have 3 tables needing this
updating with as many fields to update. I use Version 2007.

 

[Allen Browne]

Perhaps you could update the field to itself if it is not null:
UPDATE Table1
SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...

 

[Guest]

If you have all the "Is Null"s on one line, that record will not update if
any of the 15 fields isn't null.

If you put the Is Null on seperate criteria lines, it will up date all all
fields even if they aren't null. Not good.

You probably need to run a query for each field for each table to update the
records. Possible someone can do it easier with code, but not really with a
plain old SQL query.

Also the main problem may, just may, be that your data is not properly
normalized. If you are doing something like fields named Jan, Feb, Mar, etc.,
then properly normalizing your data will go a long way to avoid making
multiple update queries for just one table.

 

[Guest]

Thanks Allen. This is what I tested:

UPDATE [tbl 2 JobBCAdd] SET [AddVisit5] = iif([AddVisit5] Is
Null,"0"),[AddVisit6]=Iif([AddVisit6] Is Null, "0");

This works...almost. Seems to skips some fields. Do you know what might
cause this?
--
Thanks for your help,
Chris

Perhaps you could update the field to itself if it is not null:
UPDATE Table1
SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am updating one table (one time only) having 15 of the fields change from
null to 0. Text fields. Yes, I mean to do this. When my query updates
one
field at a time, it updates fine.

I cannot get it to work if I ask it to update all 15 in the one query --
field1, field2, field3...etc. Each is set to update to "0" (w/quotes) and
the criteria is "Is Null", all criteria on same line (I also tried 'or').

Am I going to have to enter 15 queries? I have 3 tables needing this
updating with as many fields to update. I use Version 2007.

 

[Guest]

Thanks Jerry. I tried Allen's code. See second response to him for issue now.

 

[Guest]

I spoke too soon. Here's my full code:

UPDATE [tbl 2 JobBCAdd] SET AddVisit1 = Iif([AddVisit1] Is Null,"0"),
AddVisit2 = Iif([AddVisit2] Is Null,"0"), AddVisit3 = Iif([AddVisit3] Is
Null,"0"), AddVisit4 = Iif([AddVisit4] Is Null,"0"), AddVisit5 =
Iif([AddVisit5] Is Null,"0"), AddVisit6 = Iif([AddVisit6] Is Null,"0"),
AddVisit7 = Iif([AddVisit7] Is Null,"0"), AddVisit8 = Iif([AddVisit8] Is
Null,"0"), AddVisit9 = Iif([AddVisit9] Is Null,"0"), AddVisit10 =
Iif([AddVisit10] Is Null,"0"), AddVisit11 = Iif([AddVisit11] Is Null,"0"),
AddVisit12 = Iif([AddVisit12] Is Null,"0"), AddVisit13 = Iif([AddVisit13] Is
Null,"0"), AddVisit14 = Iif([AddVisit14] Is Null,"0"), AddVisit15 =
Iif([AddVisit15] Is Null,"0");

What happens: Nulls become zeroes; valid data replace with null. Ideas?
If this is too convoluted, I'll just do a query for each field. Won't take
that long.

Thanks.
--
Thanks for your help,
Chris

Perhaps you could update the field to itself if it is not null:
UPDATE Table1
SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am updating one table (one time only) having 15 of the fields change from
null to 0. Text fields. Yes, I mean to do this. When my query updates
one
field at a time, it updates fine.

I cannot get it to work if I ask it to update all 15 in the one query --
field1, field2, field3...etc. Each is set to update to "0" (w/quotes) and
the criteria is "Is Null", all criteria on same line (I also tried 'or').

Am I going to have to enter 15 queries? I have 3 tables needing this
updating with as many fields to update. I use Version 2007.

 

[Allen Browne]

You skipped the 3rd argument for IIf():

UPDATE [tbl 2 JobBCAdd]
SET AddVisit1 = Iif([AddVisit1] Is Null, "0", [AddVisit1]),
AddVisit2 = Iif([AddVisit2] Is Null,"0", [AddVisit2),
AddVisit3 = Iif([AddVisit3] Is Null,"0", [AddVisit3),

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I spoke too soon. Here's my full code:

UPDATE [tbl 2 JobBCAdd] SET AddVisit1 = Iif([AddVisit1] Is Null,"0"),
AddVisit2 = Iif([AddVisit2] Is Null,"0"), AddVisit3 = Iif([AddVisit3] Is
Null,"0"), AddVisit4 = Iif([AddVisit4] Is Null,"0"), AddVisit5 =
Iif([AddVisit5] Is Null,"0"), AddVisit6 = Iif([AddVisit6] Is Null,"0"),
AddVisit7 = Iif([AddVisit7] Is Null,"0"), AddVisit8 = Iif([AddVisit8] Is
Null,"0"), AddVisit9 = Iif([AddVisit9] Is Null,"0"), AddVisit10 =
Iif([AddVisit10] Is Null,"0"), AddVisit11 = Iif([AddVisit11] Is Null,"0"),
AddVisit12 = Iif([AddVisit12] Is Null,"0"), AddVisit13 = Iif([AddVisit13]
Is
Null,"0"), AddVisit14 = Iif([AddVisit14] Is Null,"0"), AddVisit15 =
Iif([AddVisit15] Is Null,"0");

What happens: Nulls become zeroes; valid data replace with null. Ideas?
If this is too convoluted, I'll just do a query for each field. Won't
take
that long.

Thanks.
--
Thanks for your help,
Chris

Perhaps you could update the field to itself if it is not null:
UPDATE Table1
SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...

I am updating one table (one time only) having 15 of the fields change
from
null to 0. Text fields. Yes, I mean to do this. When my query updates
one
field at a time, it updates fine.

I cannot get it to work if I ask it to update all 15 in the one
query --
field1, field2, field3...etc. Each is set to update to "0" (w/quotes)
and
the criteria is "Is Null", all criteria on same line (I also tried
'or').

Am I going to have to enter 15 queries? I have 3 tables needing this
updating with as many fields to update. I use Version 2007.

 

[Guest]

I just had one of those Duh!?! moments.

Thanks so much, Allen.

--
Thanks for your help,
Chris

You skipped the 3rd argument for IIf():

UPDATE [tbl 2 JobBCAdd]
SET AddVisit1 = Iif([AddVisit1] Is Null, "0", [AddVisit1]),
AddVisit2 = Iif([AddVisit2] Is Null,"0", [AddVisit2),
AddVisit3 = Iif([AddVisit3] Is Null,"0", [AddVisit3),

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I spoke too soon. Here's my full code:

UPDATE [tbl 2 JobBCAdd] SET AddVisit1 = Iif([AddVisit1] Is Null,"0"),
AddVisit2 = Iif([AddVisit2] Is Null,"0"), AddVisit3 = Iif([AddVisit3] Is
Null,"0"), AddVisit4 = Iif([AddVisit4] Is Null,"0"), AddVisit5 =
Iif([AddVisit5] Is Null,"0"), AddVisit6 = Iif([AddVisit6] Is Null,"0"),
AddVisit7 = Iif([AddVisit7] Is Null,"0"), AddVisit8 = Iif([AddVisit8] Is
Null,"0"), AddVisit9 = Iif([AddVisit9] Is Null,"0"), AddVisit10 =
Iif([AddVisit10] Is Null,"0"), AddVisit11 = Iif([AddVisit11] Is Null,"0"),
AddVisit12 = Iif([AddVisit12] Is Null,"0"), AddVisit13 = Iif([AddVisit13]
Is
Null,"0"), AddVisit14 = Iif([AddVisit14] Is Null,"0"), AddVisit15 =
Iif([AddVisit15] Is Null,"0");

What happens: Nulls become zeroes; valid data replace with null. Ideas?
If this is too convoluted, I'll just do a query for each field. Won't
take
that long.

Thanks.
--
Thanks for your help,
Chris

Perhaps you could update the field to itself if it is not null:
UPDATE Table1
SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...

I am updating one table (one time only) having 15 of the fields change
from
null to 0. Text fields. Yes, I mean to do this. When my query updates
one
field at a time, it updates fine.

I cannot get it to work if I ask it to update all 15 in the one
query --
field1, field2, field3...etc. Each is set to update to "0" (w/quotes)
and
the criteria is "Is Null", all criteria on same line (I also tried
'or').

Am I going to have to enter 15 queries? I have 3 tables needing this
updating with as many fields to update. I use Version 2007.

 

[Guest]

Wouldn't this be cleaner using NZ?

UPDATE [tbl 2 JobBCAdd]
SET AddVisit1 = NZ([AddVisit1], "0"),
AddVisit2 = NZ([AddVisit2],"0"),
AddVisit3 = NZ([AddVisit3],"0"),

Dale
--
Email address is not valid.
Please reply to newsgroup only.

You skipped the 3rd argument for IIf():

UPDATE [tbl 2 JobBCAdd]
SET AddVisit1 = Iif([AddVisit1] Is Null, "0", [AddVisit1]),
AddVisit2 = Iif([AddVisit2] Is Null,"0", [AddVisit2),
AddVisit3 = Iif([AddVisit3] Is Null,"0", [AddVisit3),

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I spoke too soon. Here's my full code:

UPDATE [tbl 2 JobBCAdd] SET AddVisit1 = Iif([AddVisit1] Is Null,"0"),
AddVisit2 = Iif([AddVisit2] Is Null,"0"), AddVisit3 = Iif([AddVisit3] Is
Null,"0"), AddVisit4 = Iif([AddVisit4] Is Null,"0"), AddVisit5 =
Iif([AddVisit5] Is Null,"0"), AddVisit6 = Iif([AddVisit6] Is Null,"0"),
AddVisit7 = Iif([AddVisit7] Is Null,"0"), AddVisit8 = Iif([AddVisit8] Is
Null,"0"), AddVisit9 = Iif([AddVisit9] Is Null,"0"), AddVisit10 =
Iif([AddVisit10] Is Null,"0"), AddVisit11 = Iif([AddVisit11] Is Null,"0"),
AddVisit12 = Iif([AddVisit12] Is Null,"0"), AddVisit13 = Iif([AddVisit13]
Is
Null,"0"), AddVisit14 = Iif([AddVisit14] Is Null,"0"), AddVisit15 =
Iif([AddVisit15] Is Null,"0");

What happens: Nulls become zeroes; valid data replace with null. Ideas?
If this is too convoluted, I'll just do a query for each field. Won't
take
that long.

Thanks.
--
Thanks for your help,
Chris

Perhaps you could update the field to itself if it is not null:
UPDATE Table1
SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...

I am updating one table (one time only) having 15 of the fields change
from
null to 0. Text fields. Yes, I mean to do this. When my query updates
one
field at a time, it updates fine.

I cannot get it to work if I ask it to update all 15 in the one
query --
field1, field2, field3...etc. Each is set to update to "0" (w/quotes)
and
the criteria is "Is Null", all criteria on same line (I also tried
'or').

Am I going to have to enter 15 queries? I have 3 tables needing this
updating with as many fields to update. I use Version 2007.

 

[Allen Browne]

That would work fine, Dale.

One day I'll publish an article on why I prefer to avoid Nz(). Essentially,
JET has a native IIf() so it avoids a call to VBA. Also the native JET IIf()
gets the data types right, whereas JET does not interpret the data type
returned by Nz() - and any other VBA functions that returns a Variant.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Wouldn't this be cleaner using NZ?

UPDATE [tbl 2 JobBCAdd]
SET AddVisit1 = NZ([AddVisit1], "0"),
AddVisit2 = NZ([AddVisit2],"0"),
AddVisit3 = NZ([AddVisit3],"0"),

Dale
--
Email address is not valid.
Please reply to newsgroup only.

You skipped the 3rd argument for IIf():

UPDATE [tbl 2 JobBCAdd]
SET AddVisit1 = Iif([AddVisit1] Is Null, "0", [AddVisit1]),
AddVisit2 = Iif([AddVisit2] Is Null,"0", [AddVisit2),
AddVisit3 = Iif([AddVisit3] Is Null,"0", [AddVisit3),

I spoke too soon. Here's my full code:

UPDATE [tbl 2 JobBCAdd] SET AddVisit1 = Iif([AddVisit1] Is Null,"0"),
AddVisit2 = Iif([AddVisit2] Is Null,"0"), AddVisit3 = Iif([AddVisit3]
Is
Null,"0"), AddVisit4 = Iif([AddVisit4] Is Null,"0"), AddVisit5 =
Iif([AddVisit5] Is Null,"0"), AddVisit6 = Iif([AddVisit6] Is Null,"0"),
AddVisit7 = Iif([AddVisit7] Is Null,"0"), AddVisit8 = Iif([AddVisit8]
Is
Null,"0"), AddVisit9 = Iif([AddVisit9] Is Null,"0"), AddVisit10 =
Iif([AddVisit10] Is Null,"0"), AddVisit11 = Iif([AddVisit11] Is
Null,"0"),
AddVisit12 = Iif([AddVisit12] Is Null,"0"), AddVisit13 =
Iif([AddVisit13]
Is
Null,"0"), AddVisit14 = Iif([AddVisit14] Is Null,"0"), AddVisit15 =
Iif([AddVisit15] Is Null,"0");

What happens: Nulls become zeroes; valid data replace with null.
Ideas?
If this is too convoluted, I'll just do a query for each field. Won't
take
that long.

Thanks.
--
Thanks for your help,
Chris


:

Perhaps you could update the field to itself if it is not null:
UPDATE Table1
SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...

I am updating one table (one time only) having 15 of the fields
change
from
null to 0. Text fields. Yes, I mean to do this. When my query
updates
one
field at a time, it updates fine.

I cannot get it to work if I ask it to update all 15 in the one
query --
field1, field2, field3...etc. Each is set to update to "0"
(w/quotes)
and
the criteria is "Is Null", all criteria on same line (I also tried
'or').

Am I going to have to enter 15 queries? I have 3 tables needing
this
updating with as many fields to update. I use Version 2007.

 

[Dale Fye]

Allen,

Thanks for the detail. IIF( ) statements can be soooooo... long, and I find
them hard to read. So, if I understand your logic, I should use a type
conversion function if I want to make sure that JET will properly interpret
the datatype.

Dale

That would work fine, Dale.

One day I'll publish an article on why I prefer to avoid Nz().
Essentially, JET has a native IIf() so it avoids a call to VBA. Also the
native JET IIf() gets the data types right, whereas JET does not interpret
the data type returned by Nz() - and any other VBA functions that returns
a Variant.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Wouldn't this be cleaner using NZ?

UPDATE [tbl 2 JobBCAdd]
SET AddVisit1 = NZ([AddVisit1], "0"),
AddVisit2 = NZ([AddVisit2],"0"),
AddVisit3 = NZ([AddVisit3],"0"),

Dale
--
Email address is not valid.
Please reply to newsgroup only.

You skipped the 3rd argument for IIf():

UPDATE [tbl 2 JobBCAdd]
SET AddVisit1 = Iif([AddVisit1] Is Null, "0", [AddVisit1]),
AddVisit2 = Iif([AddVisit2] Is Null,"0", [AddVisit2),
AddVisit3 = Iif([AddVisit3] Is Null,"0", [AddVisit3),

I spoke too soon. Here's my full code:

UPDATE [tbl 2 JobBCAdd] SET AddVisit1 = Iif([AddVisit1] Is Null,"0"),
AddVisit2 = Iif([AddVisit2] Is Null,"0"), AddVisit3 = Iif([AddVisit3]
Is
Null,"0"), AddVisit4 = Iif([AddVisit4] Is Null,"0"), AddVisit5 =
Iif([AddVisit5] Is Null,"0"), AddVisit6 = Iif([AddVisit6] Is
Null,"0"),
AddVisit7 = Iif([AddVisit7] Is Null,"0"), AddVisit8 = Iif([AddVisit8]
Is
Null,"0"), AddVisit9 = Iif([AddVisit9] Is Null,"0"), AddVisit10 =
Iif([AddVisit10] Is Null,"0"), AddVisit11 = Iif([AddVisit11] Is
Null,"0"),
AddVisit12 = Iif([AddVisit12] Is Null,"0"), AddVisit13 =
Iif([AddVisit13]
Is
Null,"0"), AddVisit14 = Iif([AddVisit14] Is Null,"0"), AddVisit15 =
Iif([AddVisit15] Is Null,"0");

What happens: Nulls become zeroes; valid data replace with null.
Ideas?
If this is too convoluted, I'll just do a query for each field. Won't
take
that long.

Thanks.
--
Thanks for your help,
Chris


:

Perhaps you could update the field to itself if it is not null:
UPDATE Table1
SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...

I am updating one table (one time only) having 15 of the fields
change
from
null to 0. Text fields. Yes, I mean to do this. When my query
updates
one
field at a time, it updates fine.

I cannot get it to work if I ask it to update all 15 in the one
query --
field1, field2, field3...etc. Each is set to update to "0"
(w/quotes)
and
the criteria is "Is Null", all criteria on same line (I also tried
'or').

Am I going to have to enter 15 queries? I have 3 tables needing
this
updating with as many fields to update. I use Version 2007.

 

[Allen Browne]

Yes, IIf() is certainly more circumlocutory.

With Nz(), typecasting works.

Depends whether you want something that's quicker to develop (and easier
maintain), or something that executes more efficiently and works across a
broader range.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the detail. IIF( ) statements can be soooooo... long, and I
find them hard to read. So, if I understand your logic, I should use a
type conversion function if I want to make sure that JET will properly
interpret the datatype.

Dale

That would work fine, Dale.

One day I'll publish an article on why I prefer to avoid Nz().
Essentially, JET has a native IIf() so it avoids a call to VBA. Also the
native JET IIf() gets the data types right, whereas JET does not
interpret the data type returned by Nz() - and any other VBA functions
that returns a Variant.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Wouldn't this be cleaner using NZ?

UPDATE [tbl 2 JobBCAdd]
SET AddVisit1 = NZ([AddVisit1], "0"),
AddVisit2 = NZ([AddVisit2],"0"),
AddVisit3 = NZ([AddVisit3],"0"),

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

You skipped the 3rd argument for IIf():

UPDATE [tbl 2 JobBCAdd]
SET AddVisit1 = Iif([AddVisit1] Is Null, "0", [AddVisit1]),
AddVisit2 = Iif([AddVisit2] Is Null,"0", [AddVisit2),
AddVisit3 = Iif([AddVisit3] Is Null,"0", [AddVisit3),

I spoke too soon. Here's my full code:

UPDATE [tbl 2 JobBCAdd] SET AddVisit1 = Iif([AddVisit1] Is Null,"0"),
AddVisit2 = Iif([AddVisit2] Is Null,"0"), AddVisit3 = Iif([AddVisit3]
Is
Null,"0"), AddVisit4 = Iif([AddVisit4] Is Null,"0"), AddVisit5 =
Iif([AddVisit5] Is Null,"0"), AddVisit6 = Iif([AddVisit6] Is
Null,"0"),
AddVisit7 = Iif([AddVisit7] Is Null,"0"), AddVisit8 = Iif([AddVisit8]
Is
Null,"0"), AddVisit9 = Iif([AddVisit9] Is Null,"0"), AddVisit10 =
Iif([AddVisit10] Is Null,"0"), AddVisit11 = Iif([AddVisit11] Is
Null,"0"),
AddVisit12 = Iif([AddVisit12] Is Null,"0"), AddVisit13 =
Iif([AddVisit13]
Is
Null,"0"), AddVisit14 = Iif([AddVisit14] Is Null,"0"), AddVisit15 =
Iif([AddVisit15] Is Null,"0");

What happens: Nulls become zeroes; valid data replace with null.
Ideas?
If this is too convoluted, I'll just do a query for each field.
Won't
take
that long.

Thanks.
--
Thanks for your help,
Chris


:

Perhaps you could update the field to itself if it is not null:
UPDATE Table1
SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...

I am updating one table (one time only) having 15 of the fields
change
from
null to 0. Text fields. Yes, I mean to do this. When my query
updates
one
field at a time, it updates fine.

I cannot get it to work if I ask it to update all 15 in the one
query --
field1, field2, field3...etc. Each is set to update to "0"
(w/quotes)
and
the criteria is "Is Null", all criteria on same line (I also tried
'or').

Am I going to have to enter 15 queries? I have 3 tables needing
this
updating with as many fields to update. I use Version 2007.