Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Aug 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update simplified (Access 2000)

    I want to build a function that sets chosen fields to 0.
    I want to simplify my function since i have to repeat and and the same line code.
    I am doing now the following


    Public function ClearFields()
    ' set the field Field2 to 0
    Dim sqlField2 as string
    sqlField2 = " Update products SET Field2 = 0"
    CurrentDb.Execute SqlField2


    ' set the field Field4 to 0
    Dim sqlField5 as string
    sqlField5 = " Update products SET Field5 = 0"
    CurrentDb.Execute SqlField5

    etc etc I have 5 more fields to set to 0"

    Is there any way to simplify my code, put the fields into brackets and just
    contain the function into one or two lines ?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Update simplified (Access 2000)

    You should be able to update multiple fields to 0 in a single SQL statement. Build a simple select query in the query designer, change it to an update query, and set several fields to 0, then switch to the SQL view and you should see the correct syntax for setting several fields to 0.
    Wendell

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Update simplified (Access 2000)

    If you want to set all the fields to zero then Wendell's is the way to go.

    If you only want selected (which I presume is what you want) then why don't you build an SQL on the fly then execute it.

Posting Permissions

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