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.




--
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chris" <(E-Mail Removed)> wrote in message
news:EC352A9D-03CD-4A4E-BAB9-(E-Mail Removed)...
>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.

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Chris" wrote:

> 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.
>
>
>
>
> --
> Thanks for your help,
> Chris

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


"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Chris" <(E-Mail Removed)> wrote in message
> news:EC352A9D-03CD-4A4E-BAB9-(E-Mail Removed)...
> >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.
>
>

Thanks Jerry. I tried Allen's code. See second response to him for issue now.
--
Thanks for your help,
Chris


"Jerry Whittle" wrote:

> 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.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Chris" wrote:
>
> > 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.
> >
> >
> >
> >
> > --
> > Thanks for your help,
> > Chris

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


"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Chris" <(E-Mail Removed)> wrote in message
> news:EC352A9D-03CD-4A4E-BAB9-(E-Mail Removed)...
> >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.
>
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chris" <(E-Mail Removed)> wrote in message
news:5F6918F0-3E7D-4708-8772-(E-Mail Removed)...
>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
>
>
> "Allen Browne" wrote:
>
>> Perhaps you could update the field to itself if it is not null:
>> UPDATE Table1
>> SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...
>>
>> "Chris" <(E-Mail Removed)> wrote in message
>> news:EC352A9D-03CD-4A4E-BAB9-(E-Mail Removed)...
>> >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.

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

Thanks so much, Allen.

--
Thanks for your help,
Chris


"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Chris" <(E-Mail Removed)> wrote in message
> news:5F6918F0-3E7D-4708-8772-(E-Mail Removed)...
> >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
> >
> >
> > "Allen Browne" wrote:
> >
> >> Perhaps you could update the field to itself if it is not null:
> >> UPDATE Table1
> >> SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...
> >>
> >> "Chris" <(E-Mail Removed)> wrote in message
> >> news:EC352A9D-03CD-4A4E-BAB9-(E-Mail Removed)...
> >> >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.
>
>

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.


"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Chris" <(E-Mail Removed)> wrote in message
> news:5F6918F0-3E7D-4708-8772-(E-Mail Removed)...
> >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
> >
> >
> > "Allen Browne" wrote:
> >
> >> Perhaps you could update the field to itself if it is not null:
> >> UPDATE Table1
> >> SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...
> >>
> >> "Chris" <(E-Mail Removed)> wrote in message
> >> news:EC352A9D-03CD-4A4E-BAB9-(E-Mail Removed)...
> >> >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.
>
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dale Fye" <(E-Mail Removed)> wrote in message
news:8D2E3FCF-9ABE-4C16-94AB-(E-Mail Removed)...
> 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.
>
>
> "Allen Browne" wrote:
>
>> 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),
>>
>> "Chris" <(E-Mail Removed)> wrote in message
>> news:5F6918F0-3E7D-4708-8772-(E-Mail Removed)...
>> >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
>> >
>> >
>> > "Allen Browne" wrote:
>> >
>> >> Perhaps you could update the field to itself if it is not null:
>> >> UPDATE Table1
>> >> SET Field1 = IIf([Field1] Is Null, "0", [Field1]), ...
>> >>
>> >> "Chris" <(E-Mail Removed)> wrote in message
>> >> news:EC352A9D-03CD-4A4E-BAB9-(E-Mail Removed)...
>> >> >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.