Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Update Field to Zero if Null (2003)

    OK. I've got another one: I've got a table Called "Export Data" with 6 number fields Denom1 to Denom6. That need checking if "Null" and then updating to 0. I can create and run 6 Update Queries via a macro before Export, which test if the fields Null, thay do the trick, but what is the elegant and simple solution without creating 6 Queries (I've actually got about 14 fields to check). I can't set a default value to the field when created as this interfears with the visible structure (and the girls don't like it). I've very little experiance with modules, so any instructions will help. Thank-You.

    My gratitude is inexpressable.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Field to Zero if Null (2003)

    Whether you create them manually or write some code for it, you'll have to execute an update for each field. You write that you have 14 fields to check - are they named Denom1 through Denom14, or do not all fields conform to this pattern?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Update Field to Zero if Null (2003)

    Thanks Hans

    I've got to apply a 0 to all Null number Fields in:

    Denom1 to Denom6
    Amount1 to Amount6
    Full Redemption Amount
    Partial Redemption Amount

    The existing display format of Decimals and 000,000 Structure of Not Null Fields needs to be maintained and no data lost

    The File is Complete at this processing stage and should have no more than 100 Records per shot. And then will be exported, formatted, to excel for delivery.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Field to Zero if Null (2003)

    You could create the following code (you can copy and paste it into a module):

    Sub FillMultiple()
    Const TableName = "Export Data"
    Dim i As Integer
    For i = 1 To 6
    FillZeros TableName, "Denom" & i
    FillZeros TableName, "Amount" & i
    Next i
    FillZeros TableName, "Full Redemption Amount"
    FillZeros TableName, "Partial Redemption Amount"
    End Sub

    Sub FillZeros(TableName As String, FieldName As String)
    Dim strSQL As String
    strSQL = "UPDATE <!t>[" & TableName & "]<!/t> SET <!t>[" & FieldName & _
    "]<!/t> = 0 WHERE <!t>[" & FieldName & "]<!/t> Is Null"
    CurrentDb.Execute strSQL, dbFailOnError
    End Sub

    Click inside FillMultiple and click the Run button or press F5 to execute it.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update Field to Zero if Null (2003)

    Hans

    Can I just stress that having learnt from the University of Hard Knocks, that graliv1 ensures thay have backed up the database before doing your code. I am sure your code is correct but there may be issues with the data and I have seen whole datasets been updated instead of the required fields and I have had to run off for the 24 hour back up...my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Field to Zero if Null (2003)

    Excellent point. It is always a good idea to create a backup before running queries or code that modify the database.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Update Field to Zero if Null (2003)

    You Da Man!!!!!

    As a point, it took me a while, but I Changed the "Sub" to a "Public Function" and I can now run in a macro through "RunCode" Just before we export.

    Excellent. Yet another moment of satisfaction through "better living by Computer"

    Thanks HansV

  8. #8
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Update Field to Zero if Null (2003)

    Yep. BackUp always Happens before we do anything. They're accuumulating Nicely on a PC 100 yards away.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •