Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How do I enter "0" in empty fields for an entire table?

Discussion in 'Information Technology' started by eemster, Jul 28, 2006.

  1. eemster

    eemster
    Expand Collapse
    Guest

    Hi,

    I have a MS-Access table with 240K lines and 20 columns. However, some
    fields have "0" while others don't. I know that the blank fields should have
    "0" as well: is there a way that I can automatically add "0" to these blank
    fields without having to manually (!) enter them?

    Thanks!!
     
  2. Loading...


  3. Pat Hartman\(MVP\)

    Pat Hartman\(MVP\)
    Expand Collapse
    Guest

    You may actually want to distinguish null values from zeros depending on
    your application. For example in a table of grades a null value would
    indicate that the test wasn't taken and a 0 would indicate that the test was
    taken but a score of 0 was earned. This null vs. zero impacts averages
    also. For example, the average of 3-3-Null is 3 whereas the average of
    3-3-0 is 2.

    In any event to update all the null values create an update query.
    Open the query builder,
    Add the table you want to update,
    Select the column you want to update,
    Add criteria ---- Is Not Null
    Change the query type to Update
    Place a 0 in the UpdateTo column - NO quotes.

    "eemster" <eemster@discussions.microsoft.com> wrote in message
    news:4F9607CF-FEC8-434D-9E28-700C1AEF5F97@microsoft.com...
    > Hi,
    >
    > I have a MS-Access table with 240K lines and 20 columns. However, some
    > fields have "0" while others don't. I know that the blank fields should
    > have
    > "0" as well: is there a way that I can automatically add "0" to these
    > blank
    > fields without having to manually (!) enter them?
    >
    > Thanks!!
     
  4. Rob Parker

    Rob Parker
    Expand Collapse
    Guest

    The criteria should be:
    Is Null

    Using Is Not Null will update all existing data values to 0, and leave the
    nulls unchanged.

    Rob

    "Pat Hartman(MVP)" <patsky@NoSpam.optonline.net> wrote in message
    news:ONs281unGHA.4868@TK2MSFTNGP02.phx.gbl...
    > You may actually want to distinguish null values from zeros depending on
    > your application. For example in a table of grades a null value would
    > indicate that the test wasn't taken and a 0 would indicate that the test
    > was taken but a score of 0 was earned. This null vs. zero impacts
    > averages also. For example, the average of 3-3-Null is 3 whereas the
    > average of 3-3-0 is 2.
    >
    > In any event to update all the null values create an update query.
    > Open the query builder,
    > Add the table you want to update,
    > Select the column you want to update,
    > Add criteria ---- Is Not Null
    > Change the query type to Update
    > Place a 0 in the UpdateTo column - NO quotes.
    >
    > "eemster" <eemster@discussions.microsoft.com> wrote in message
    > news:4F9607CF-FEC8-434D-9E28-700C1AEF5F97@microsoft.com...
    >> Hi,
    >>
    >> I have a MS-Access table with 240K lines and 20 columns. However, some
    >> fields have "0" while others don't. I know that the blank fields should
    >> have
    >> "0" as well: is there a way that I can automatically add "0" to these
    >> blank
    >> fields without having to manually (!) enter them?
    >>
    >> Thanks!!

    >
    >
     
  5. eemster

    eemster
    Expand Collapse
    Guest

    Hi,

    Thanks for answering. When I tried to do this, I got 0 rows, even though I
    know that there are blank fields in some of the columns. Perhaps part of the
    problem is that only some entries in some of the columns are blank?

    Thanks again.

    "Rob Parker" wrote:

    > The criteria should be:
    > Is Null
    >
    > Using Is Not Null will update all existing data values to 0, and leave the
    > nulls unchanged.
    >
    > Rob
    >
    > "Pat Hartman(MVP)" <patsky@NoSpam.optonline.net> wrote in message
    > news:ONs281unGHA.4868@TK2MSFTNGP02.phx.gbl...
    > > You may actually want to distinguish null values from zeros depending on
    > > your application. For example in a table of grades a null value would
    > > indicate that the test wasn't taken and a 0 would indicate that the test
    > > was taken but a score of 0 was earned. This null vs. zero impacts
    > > averages also. For example, the average of 3-3-Null is 3 whereas the
    > > average of 3-3-0 is 2.
    > >
    > > In any event to update all the null values create an update query.
    > > Open the query builder,
    > > Add the table you want to update,
    > > Select the column you want to update,
    > > Add criteria ---- Is Not Null
    > > Change the query type to Update
    > > Place a 0 in the UpdateTo column - NO quotes.
    > >
    > > "eemster" <eemster@discussions.microsoft.com> wrote in message
    > > news:4F9607CF-FEC8-434D-9E28-700C1AEF5F97@microsoft.com...
    > >> Hi,
    > >>
    > >> I have a MS-Access table with 240K lines and 20 columns. However, some
    > >> fields have "0" while others don't. I know that the blank fields should
    > >> have
    > >> "0" as well: is there a way that I can automatically add "0" to these
    > >> blank
    > >> fields without having to manually (!) enter them?
    > >>
    > >> Thanks!!

    > >
    > >

    >
    >
    >
     
  6. eemster

    eemster
    Expand Collapse
    Guest

    Thanks for replying: I tried to do this and I got a response that I need to
    reset my MacFilesSetOption (or something like that). I'm not sure why I would
    run out of memory: is there some way around this?

    Thanks.

    "Pat Hartman(MVP)" wrote:

    > You may actually want to distinguish null values from zeros depending on
    > your application. For example in a table of grades a null value would
    > indicate that the test wasn't taken and a 0 would indicate that the test was
    > taken but a score of 0 was earned. This null vs. zero impacts averages
    > also. For example, the average of 3-3-Null is 3 whereas the average of
    > 3-3-0 is 2.
    >
    > In any event to update all the null values create an update query.
    > Open the query builder,
    > Add the table you want to update,
    > Select the column you want to update,
    > Add criteria ---- Is Not Null
    > Change the query type to Update
    > Place a 0 in the UpdateTo column - NO quotes.
    >
    > "eemster" <eemster@discussions.microsoft.com> wrote in message
    > news:4F9607CF-FEC8-434D-9E28-700C1AEF5F97@microsoft.com...
    > > Hi,
    > >
    > > I have a MS-Access table with 240K lines and 20 columns. However, some
    > > fields have "0" while others don't. I know that the blank fields should
    > > have
    > > "0" as well: is there a way that I can automatically add "0" to these
    > > blank
    > > fields without having to manually (!) enter them?
    > >
    > > Thanks!!

    >
    >
    >
     
  7. Rob Parker

    Rob Parker
    Expand Collapse
    Guest

    I'm not sure what you mean, when you say "... in some of the columns. "
    The update query you are performing should only have one column, if you are
    following the directions which Pat Hartman posted.

    If you need to update multiple columns, you should do each column in a
    separate query. I suspect that what you may have done is tried to do all
    columns together, and entered the Is Null criteria for each column in a
    single row in the query design grid. This will effectively select - and
    update - the records *for every column* where there is a null in every
    column.

    For a Select query, you can find records with a null in any column by
    entering the Is Null criterion in a different row for each column; this
    gives an overall criterion of:
    ... Where (ColumnA Is Null) OR (ColumnB Is Null) OR ...
    If you enter each criterion in the same row in the design grid, the overall
    criterion is:
    ... Where (ColumnA Is Null) AND (ColumnB Is Null) AND ...

    HTH,

    Rob


    "eemster" <eemster@discussions.microsoft.com> wrote in message
    news:905946F1-7471-4A87-AE9B-769720D027BC@microsoft.com...
    > Hi,
    >
    > Thanks for answering. When I tried to do this, I got 0 rows, even though I
    > know that there are blank fields in some of the columns. Perhaps part of
    > the
    > problem is that only some entries in some of the columns are blank?
    >
    > Thanks again.
    >
    > "Rob Parker" wrote:
    >
    >> The criteria should be:
    >> Is Null
    >>
    >> Using Is Not Null will update all existing data values to 0, and leave
    >> the
    >> nulls unchanged.
    >>
    >> Rob
    >>
    >> "Pat Hartman(MVP)" <patsky@NoSpam.optonline.net> wrote in message
    >> news:ONs281unGHA.4868@TK2MSFTNGP02.phx.gbl...
    >> > You may actually want to distinguish null values from zeros depending
    >> > on
    >> > your application. For example in a table of grades a null value would
    >> > indicate that the test wasn't taken and a 0 would indicate that the
    >> > test
    >> > was taken but a score of 0 was earned. This null vs. zero impacts
    >> > averages also. For example, the average of 3-3-Null is 3 whereas the
    >> > average of 3-3-0 is 2.
    >> >
    >> > In any event to update all the null values create an update query.
    >> > Open the query builder,
    >> > Add the table you want to update,
    >> > Select the column you want to update,
    >> > Add criteria ---- Is Not Null
    >> > Change the query type to Update
    >> > Place a 0 in the UpdateTo column - NO quotes.
    >> >
    >> > "eemster" <eemster@discussions.microsoft.com> wrote in message
    >> > news:4F9607CF-FEC8-434D-9E28-700C1AEF5F97@microsoft.com...
    >> >> Hi,
    >> >>
    >> >> I have a MS-Access table with 240K lines and 20 columns. However, some
    >> >> fields have "0" while others don't. I know that the blank fields
    >> >> should
    >> >> have
    >> >> "0" as well: is there a way that I can automatically add "0" to these
    >> >> blank
    >> >> fields without having to manually (!) enter them?
    >> >>
    >> >> Thanks!!
    >> >
    >> >

    >>
    >>
    >>
     

Share This Page