Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    29

    Unanswered: Deleting question (urgent help needed)

    So im about to send some of my databases out

    then i notice that the state is in the same column as the city, but on the next column its jsut the state seperated (ive just about had it with the property appraisor databases)


    Column1 Column2
    ex: Coral Springs FL | FL
    Jupiter FL | FL


    what i need to do is delete those states that have remained in City column.... it couldve been easier if some states werent 2 word states.... your help would be greatly appreciated

    thanks

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    This should do the trick:

    UPDATE tblAppraisal SET tblAppraisal.Column1 = Left([column1],(Len([column1])-3))
    WHERE (((Right([column1],3))=" " & [Column2]));

    TD

  3. #3
    Join Date
    Oct 2004
    Posts
    29
    I couldnt really get that? My tables name is "Citrus Springs 1550" and the column is "ADDR4" i plugged them in but got nothing?

    I can bear with just deleteng all the 2 letter words like "AR" "IL" in the column?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Just replace Column1 with ADDR4 and replace tblAppraisal with [Citrus Springs 1550] ... You'll also need to change the name of Column2 to whatever you're column name for the state is ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Oct 2004
    Posts
    29
    and where do i plug those into?

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by idioteque
    and where do i plug those into?
    Perhaps in a query that you create ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    You posted that your fields were Column1 with the City and State information and Column2 with just the State information but now you are saying that they are called something different. Tell me the name of your Table and the name of the City with State field and the name of the State only field and I will post code for you to paste into a query in SQL view.

    TD

  8. #8
    Join Date
    Oct 2004
    Posts
    29
    Sorry im fairly new at this

    i just didnt know where to put the codes witihn the query

    but here it is

    name of table: Citrus Springs 1550
    name of field with city and state: ADDR4
    name of field with state only: ST_AD4_AD3

  9. #9
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Start a new query in design view. Do not add any tables. Switch to SQL view. Copy the following and paste into the SQL view window. If Select is already in the SQL view window, get rid of it.

    UPDATE [Citrus Springs 1550] SET [Citrus Springs 1550].ADDR4 = Left([ADDR4],(Len([ADDR4])-3))
    WHERE (((Right([addr4],3))=" " & [ST_AD4_AD3]));


    Switch back to Design View. Click the Run button ! on the toolbar.

    TD

Posting Permissions

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