Update Query (multiple fields)

Discussion in 'Microsoft Access Queries' started by Guest, Aug 9, 2007.

  1. Guest

    Guest 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.




    --
    Thanks for your help,
    Chris
     
    Guest, Aug 9, 2007
    #1
    1. Advertisements

  2. Guest

    Allen Browne Guest

    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" <> wrote in message
    news:...
    >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, Aug 9, 2007
    #2
    1. Advertisements

  3. Guest

    Guest 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.
    --
    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
     
    Guest, Aug 9, 2007
    #3
  4. Guest

    Guest 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


    "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" <> wrote in message
    > news:...
    > >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, Aug 9, 2007
    #4
  5. Guest

    Guest Guest

    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
     
    Guest, Aug 9, 2007
    #5
  6. Guest

    Guest 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


    "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" <> wrote in message
    > news:...
    > >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, Aug 9, 2007
    #6
  7. Guest

    Allen Browne Guest

    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" <> wrote in message
    news:...
    >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" <> wrote in message
    >> news:...
    >> >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, Aug 9, 2007
    #7
  8. Guest

    Guest Guest

    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" <> wrote in message
    > news:...
    > >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" <> wrote in message
    > >> news:...
    > >> >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, Aug 9, 2007
    #8
  9. Guest

    Guest 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.


    "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" <> wrote in message
    > news:...
    > >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" <> wrote in message
    > >> news:...
    > >> >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, Aug 9, 2007
    #9
  10. Guest

    Allen Browne Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    >> news:...
    >> >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" <> wrote in message
    >> >> news:...
    >> >> >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, Aug 10, 2007
    #10
  11. Guest

    Dale Fye Guest

    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
    "Allen Browne" <> wrote in message
    news:u$...
    > 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" <> wrote in message
    > news:...
    >> 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" <> wrote in message
    >>> news:...
    >>> >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" <> wrote in message
    >>> >> news:...
    >>> >> >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, Aug 10, 2007
    #11
  12. Guest

    Allen Browne Guest

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

    "Dale Fye" <> wrote in message
    news:...
    > 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
    > "Allen Browne" <> wrote in message
    > news:u$...
    >> 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" <> wrote in message
    >> news:...
    >>> 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" <> wrote in message
    >>>> news:...
    >>>> >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" <> wrote in message
    >>>> >> news:...
    >>>> >> >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, Aug 11, 2007
    #12
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    multiple ifs. multiple fields, multiple criteria

    Guest, Oct 22, 2004, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    333
    Guest
    Oct 22, 2004
  2. Guest
    Replies:
    1
    Views:
    219
    Rick B
    May 17, 2006
  3. Guest
    Replies:
    0
    Views:
    254
    Guest
    Jul 13, 2006
  4. Chrisso
    Replies:
    1
    Views:
    379
    Guest
    Nov 6, 2007
  5. David Mulholland

    Hard to explain - multiple update fields query...

    David Mulholland, Jul 16, 2008, in forum: Microsoft Access Queries
    Replies:
    7
    Views:
    148
Loading...

Share This Page