Results 1 to 14 of 14

Thread: Update query

  1. #1
    Join Date
    Dec 2002
    Posts
    10

    Question Unanswered: Update query

    Can I update more than one value in the same field in the same query? You know, in the City field, update 'Jacksnvl' to 'JACKSONVILLE' and 'Den' to 'DENVER' in one query. I'm trying to automate this as much as possible.

    Thanks!
    RKSutter

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Update query

    Originally posted by rksutter
    Can I update more than one value in the same field in the same query? You know, in the City field, update 'Jacksnvl' to 'JACKSONVILLE' and 'Den' to 'DENVER' in one query. I'm trying to automate this as much as possible.

    Thanks!
    RKSutter
    sure use if then logic

    update tablename
    set city = if city = jack then jacksonvill elseif city = den then denver end if

    give something like that a try
    Jim

  3. #3
    Join Date
    Dec 2002
    Posts
    10
    It gives me a syntax error (missing operator) when I try that.

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Update query

    Originally posted by JDionne
    sure use if then logic

    update tablename
    set city = if city = jack then jacksonvill elseif city = den then denver end if

    give something like that a try
    Jim
    well its a bit more complicated but try imbeded iif's
    update table_name
    set city = iif(city = "Jack", Jacksonvile,iif(city = "den", Denver))


    try something like that

  5. #5
    Join Date
    Dec 2002
    Posts
    10
    Thanks for the suggestions but I don't think it's going to be that easy. Access won't recognize more than one IIf statement. I'm wondering if this is something you have to write code for. I'm new to all this and have no idea how to do that. Oh well, it was worth a try.

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by rksutter
    Thanks for the suggestions but I don't think it's going to be that easy. Access won't recognize more than one IIf statement. I'm wondering if this is something you have to write code for. I'm new to all this and have no idea how to do that. Oh well, it was worth a try.
    I have goten access to use imbeded iif statments, I do it all the time. What error did you get?
    Jim

  7. #7
    Join Date
    Dec 2002
    Posts
    10
    This is my query:
    UPDATE tblCity SET City = iif(city = "Jack", "Jacksonvile"), iif(city = "deer", "Deerfield"), iif(city = "grey", "Gray"));


    The error message I get is "Syntax error in UPDATE statement." When I click OK it highlights the '(' right after the second iif.

  8. #8
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by rksutter
    This is my query:
    UPDATE tblCity SET City = iif(city = "Jack", "Jacksonvile"), iif(city = "deer", "Deerfield"), iif(city = "grey", "Gray"));


    The error message I get is "Syntax error in UPDATE statement." When I click OK it highlights the '(' right after the second iif.

    its your ) they are in the wrong place. imbeded iifs are hard to see
    use

    UPDATE tblCity SET City = iif(city = "Jack", "Jacksonvile", iif(city = "deer", "Deerfield", iif(city = "grey", "Gray")));

    see what that does for you
    Jim

  9. #9
    Join Date
    Dec 2002
    Posts
    10
    It worked! This syntax stuff is hard to figure out when you don't know what you're doing. Thanks Jim, you just made my life a little easier.

    Now I have a nother question. Can you use a wild card in the iff statement? I've got a table where the address and city are in the same field and I want to update it to have just the city.

    Rob

  10. #10
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by rksutter
    It worked! This syntax stuff is hard to figure out when you don't know what you're doing. Thanks Jim, you just made my life a little easier.

    Now I have a nother question. Can you use a wild card in the iff statement? I've got a table where the address and city are in the same field and I want to update it to have just the city.

    Rob
    if you use a wild card you will have to say city like "den*" instead of city = "den*"
    wild cards need the syntax of Like
    hope that helps, if you have more trouble just post the code
    Jim

  11. #11
    Join Date
    Dec 2002
    Posts
    10
    Well it works great except Access won't let me use more than 6 embedd iif statements. This is my code:

    UPDATE tblCity2 SET CITY =
    IIf([city] like"*IRVING","IRVING",
    IIf([city] like"*DES MOINES","DES MOINES",
    IIf([city] like "*JACKSONVILLE","JACKSONVILLE",
    IIf([city] like "*SIOUX FALLS","SIOUX FALLS",
    IIf([city] like"*DEERFIELD","DEERFIELD",
    IIf([city] like"*HOUSTON","HOUSTON",
    IIf([city] like"*ENGLEWOOD CLIFFS","ENGLEWOOD CLIFFS",
    IIf([city] like"*FLORENCE","FLORENCE",
    IIf([city] like"*HAGERSTOWN","HAGERSTOWN",
    IIf([city] like"*MELVILLE","MELVILLE",
    IIf([city] like"*NEWARK","NEWARK",
    IIf([city] like"*WILMINGTON","WILMINGTON",
    IIf([city] like"*LAYTON","LAYTON",
    IIf([city] like"*THE LAKES","THE LAKES",
    IIf([city] like"*GRAY","GRAY")))))))))))))));

    If I try running this Access gives me an error message saying it's too complicated. It will run with 7 but only update the first 6. All the other fields are updated to a null.

    All I'm trying to do is get rid of the address portion of all these records so I can sort on the city name. Is there a better way to do this?

  12. #12
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    there is a function called mid
    goes something like this
    mid(column_name,1,3)
    the 1 stands for the number of the character to start with ie 1 = first character in the string
    the 3 stands for how long in the string to go ie 3 = three characters long

    if the first entrie in the column was jacksonville the above code would return jac
    so if you know how long the city names are or if you know on what char the address ends on you can split it up like that and you can put that in the order by column of a select statment
    Jim

  13. #13
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    another solution for you

    UPDATE SET [tablename].[address] = Left([address],InStrRev([abv]![ab])) & [abv]![full]
    WHERE ((([tablename].[address]) Like "*" & [abv]![ab]));


    i haven't tested this but i think it should work just make a table called abv that lists the abreviasions and the full name
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    All I'm trying to do is get rid of the address portion of all these records so I can sort on the city name. Is there a better way to do this?
    sorry missed the last paragraph of the post, if the address is segmented its easy ie
    19 john street, northhamton

    in the above code just update to

    right([address],(len([address])-instrrev([address],", "))+2)

    everything after the comma space
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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