PC Review


Reply
Thread Tools Rate Thread

Update Query (multiple fields)

 
 
=?Utf-8?B?Q2hyaXM=?=
Guest
Posts: n/a
 
      9th Aug 2007
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
 
Reply With Quote
 
 
 
 
Allen Browne
Guest
Posts: n/a
 
      9th Aug 2007
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:(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.


 
Reply With Quote
 
 
 
 
=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=
Guest
Posts: n/a
 
      9th Aug 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

 
Reply With Quote
 
=?Utf-8?B?Q2hyaXM=?=
Guest
Posts: n/a
 
      9th Aug 2007
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:(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.

>
>

 
Reply With Quote
 
=?Utf-8?B?Q2hyaXM=?=
Guest
Posts: n/a
 
      9th Aug 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

 
Reply With Quote
 
=?Utf-8?B?Q2hyaXM=?=
Guest
Posts: n/a
 
      9th Aug 2007
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:(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.

>
>


 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      9th Aug 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:(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:(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.


 
Reply With Quote
 
=?Utf-8?B?Q2hyaXM=?=
Guest
Posts: n/a
 
      9th Aug 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:(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:(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.

>
>

 
Reply With Quote
 
=?Utf-8?B?RGFsZSBGeWU=?=
Guest
Posts: n/a
 
      9th Aug 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:(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:(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.

>
>

 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      10th Aug 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:(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:(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:(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.


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can an update query be used to update/produce a query? Pat Microsoft Access Queries 8 20th Dec 2007 02:46 PM
Cannot use update query to update records in another query =?Utf-8?B?V2FyRWFnbGU5MA==?= Microsoft Access 6 14th Jun 2007 10:19 PM
Can I update a select query using an update query? =?Utf-8?B?a2ZuZw==?= Microsoft Access Queries 1 17th Aug 2006 06:50 PM
Re: Software update incomplete, Windows Update software did not update Hilary Karp Windows XP General 0 22nd Jul 2004 03:30 AM
Re: Software update incomplete, Windows Update software did not update Hilary Karp Windows XP General 0 22nd Jul 2004 02:20 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:50 PM.