Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2002
    Posts
    20

    Unanswered: Multiple Update Queries in a single field

    Right i am cleaning out a database and update any mistakes.

    I have set up a update query shown below and it works


    UPDATE Sheet1 SET Sheet1.Field1 = "Brummie"
    WHERE (((Sheet1.Field1)="Birmingham Univ. of" And (Sheet1.Field1)="BCA - Box Culvert Association"));

    Now the problem i am having is that i wish to run one big sql program to do a numerous amount of changes at once in a single field e.g.

    UPDATE Sheet1 SET Sheet1.Field1 = "Brummie"
    WHERE (((Sheet1.Field1)="Birmingham Univ. of" And (Sheet1.Field1)="BCA - Box Culvert Association"));

    UPDATE Sheet1 SET Sheet1.Field1 = "Tester"
    WHERE (((Sheet1.Field1)="BCA - Box Culvert Association"));.

    Unfortunately i keep on recieving an error, what am i doing wrong in my sql procedure, please i need help as i am only a newbie to this and it is driving me crazy???

  2. #2
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147

    Re: Multiple Update Queries in a single field

    How can you have a WHERE statement like this:

    Originally posted by Admaski123
    WHERE (((Sheet1.Field1)="Birmingham Univ. of" And (Sheet1.Field1)="BCA - Box Culvert Association"));
    That will never return true as you're saying "[Field1] = A AND [Field1] = B" It can't equal both..

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  3. #3
    Join Date
    Jul 2002
    Posts
    20

    Re: Re: Multiple Update Queries in a single field

    I have now tried:

    UPDATE Sheet1 SET Sheet1.Field1 = "Brummie"
    WHERE (((Sheet1.Field1)="Birmingham Univ. of"));
    UPDATE Sheet1 SET Sheet1.Field1 = "Test"
    WHERE (((Sheet1.Field1)="BSI - British Standards Institute"));

    That brings up characters found after sql statement

    Then I have tried to take the brackets out of the first statement but it says there is a missing operator

    Someone say what i am doing wrong or write how the program should go

  4. #4
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    I don't think Access can handle multiple Update Statements in an update query.

    You'll need to do it in code.

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  5. #5
    Join Date
    Jul 2002
    Posts
    20
    Originally posted by Bunce
    I don't think Access can handle multiple Update Statements in an update query.

    You'll need to do it in code.

    Cheers,
    Andrew


    What type of code you got to help me here i haven't got the foggiest

  6. #6
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    OK, how about creating a temp table, which holds all the combinations of the strings you want to find and replace with. Eg. "Birmingham Uni.." & "Brummie".

    tblTemp
    ---------
    ID (PK)
    SearchString
    ReplaceString

    So create that table and fill it with all the possible combinations.

    Then in code we'll iterate through that table, creating our SQL string and executing it for each record.

    Code:
    Sub MultipleUpdate()
    
    Dim db As DAO.database
    Dim rs As DAO.Recordset
    Dim strSQL, strSearch, strReplace As String
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblTemp", dbOpenTable)
    
    rs.MoveFirst
    
    Do Until rs.EOF
        strSearch = rs("SearchString")
        strReplace = rs("ReplaceString")
        strSQL = "UPDATE sheet1 SET [field1] = '" & strReplace _
            & "' WHERE [field1] = '" & strSearch & "'"
        db.Execute (strSQL)
        rs.MoveNext
    Loop
    
    End Sub
    Note this is untested, and has no error handling. You may need to alter the field names as required.

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  7. #7
    Join Date
    Jul 2002
    Posts
    20
    Hi thanks for getting back to me

    Right i have set up a temp table and put the fields in and entries

    Now where do i do the code is it inthe query option through sql?????? or is there somewhere else it can be done on Access

    Sorry to bother u again

  8. #8
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    You need to create a new module, then simply paste in the code and run it.

    Just a quick note, make sure you have a reference to "Microsoft DAO 3.6 Object Library" (Tools -> References).

    Also, if the line:
    Code:
    db.execute strSQL
    doesn't work, replace it with this
    Code:
    docmd.runsql strSQL
    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  9. #9
    Join Date
    Jul 2002
    Posts
    20

    It works

    Nice one kidder it works had a little tweaking like u said it would with the right fields in the database, but hats off spot on. Thanks very much for your time, is their any types of sites and books you could recommend on sql for access as i woud like to look into this more for future reference and practice

  10. #10
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Your Welcome - glad to be of assistance (Bit surprised that something actually worked for a change).

    Now - resources.... VBA and SQL are two quite different topics.

    VBA (Visual Basic for Applications) is MS's 'cross-application' scripting language used to interface with most applications, such as Access, Excel, Outlook etc. It's not overly difficult to learn - just takes a bit of practice.

    Access Help has quite a bit, as does MS but here's a few links that may help:
    http://www.mvps.org/access/
    http://vb.oreilly.com/

    Also, two newsgroups that are quite busy and knowledgeable are at:
    http://www.p2p.wrox.com/list.asp?list=access
    http://groups.yahoo.com/group/ms_access/

    They're always happy to help you out with any questions you may have.

    Now learning SQL (Structured Query Language) is a bit different. To be honest, I've only recently delved a little deeper into the syntx.. Never really needed it since you can pretty much do everything you need in queries (Access) or Views (SQL Server) etc. But here's a link that I've found useful:
    http://www.w3schools.com/sql/default.asp

    As with anything, the best way to learn is to practice. You can read all the books / articles you like but without practice it tends to be useless.

    Hope this helps,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  11. #11
    Join Date
    Jul 2002
    Posts
    20

    Re: Multiple Update Queries in a single field

    Right the program works great but it has a little snag which i hope someone can help me with. When it is cleaning up the data it comes across an error take for example

    Search String = AJ - Architect's Journal
    Replace String = AJ - Architects Journal

    Now i have found out that it is the" ' " in the search string that the program doesn't like, so does anyone know what i can place in the program to overcome this problem please or is there anyway round it

    Thanks

  12. #12
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Jees that was a while ago! Try changing the strSQL line to:
    Code:
    strSQL = "UPDATE sheet1 SET [field1] = '""" & strReplace _
            & """' WHERE [field1] = '""" & strSearch & """'"
    I'm in a bit of a rush so haven't tested it, but let me know if it doesn't work.

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  13. #13
    Join Date
    Jul 2002
    Posts
    20

    Longtime no see Bunce

    Yes Yes i'm back tried that code doesn't work

  14. #14
    Join Date
    Jul 2002
    Posts
    20

    Argggggggggggghhhhhhhhh

    Bunce any more ideas
    Last edited by Admaski123; 09-20-02 at 03:17.

  15. #15
    Join Date
    Jan 2003
    Posts
    6
    this worked for me, superb

Posting Permissions

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