Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Data Change in Field

    I had a new server system take over an older server and some fields have changed.

    In this code the word 'detached' has been changed in the field CommonWalls.

    It's now a situation of Like 'NCW' or Not Like 'NCW'

    I've been trying to make it work but can't seen to figure it out.

    Code:
    INSERT INTO year_month_city_counts
    SELECT YEAR(closingdate) AS closing_year
    , MONTH(closingdate) AS closing_month
    , county AS county
    , city AS city
    , 'detached' AS walls
    , closeprice AS closeprice
    , COUNT(*) AS occurs
    , AVG(buildingsize) AS avg_size
    FROM tblCARETSData
    WHERE closingdate>=DATESERIAL([Enter Year],[Enter Month],1) And closingdate<DATEADD("m",1,DATESERIAL([Enter Year],[Enter Month],1)) And closeprice>0 And buildingsize>0 And CommonWalls Like '*detached*'
    GROUP BY YEAR(closingdate), MONTH(closingdate), county, city, closeprice;
    Thanks . . . Rick

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Why the quotes around 'detached' ?
    What are you thinking that expressoon should be doing
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    current error

    OK . . . I have the queries saved.

    Here is my current error

    Syntaz error (missing operator) in query expression 'Not Like "NCW" AS walls,

    the other query will require "NCW"

    I think I'm close . . .

    rick

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and precisely where does
    Not Like "NCW" AS walls,
    appear in the query you posted?

    its kind of hard to help if you don't provide the actual query you are hitting problems with. Sdly my crystal ball is in for a 10,000 hour clean and service
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    I'm going at this the hard way.

    In lieu of changing all of the programming, how about a simple query on the field CommomWalls?

    I understand that this isn't correct but I don't know how to program the if then else situation.

    Code:
    UPDATE TblCARETSData SET TblCARETSData.CommonWalls = [tblCARETSData].[commonwalls]="detached"
    else  SET TblCARETSData.CommonWalls = [tblCARETSData].[commonwalls]="attached"
    WHERE (((TblCARETSData.CommonWalls)="ncw"));
    Rick

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so whats the proboem with this query?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    frankly this is crap
    Code:
    UPDATE TblCARETSData SET TblCARETSData.CommonWalls = [tblCARETSData].[commonwalls]="detached"
    else  SET TblCARETSData.CommonWalls = [tblCARETSData].[commonwalls]="attached"
    WHERE (((TblCARETSData.CommonWalls)="ncw"));
    its gibberish, not SQL
    you coudl do this in two queries
    Code:
    UPDATE TblCARETSData SET TblCARETSData.CommonWalls = "detached"
    WHERE (((TblCARETSData.CommonWalls)="ncw"));
    Code:
    UPDATE TblCARETSData SET TblCARETSData.CommonWalls = [tblCARETSData].[commonwalls]="attached"
    WHERE (((TblCARETSData.CommonWalls)<>"ncw"));
    but I don't know as the requirtement is even hinted at

    you might be able to do this using an IIF statement
    Code:
    UPDATE TblCARETSData SET TblCARETSData.CommonWalls = iif(TblCARETSData.CommonWalls)= "ncw",'detached','attached)
    failign that you could probably do this through a sub query
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    What I did was hear your suggestion about not reprogramming everything but to make the new data and field names to fit the old data system.

    Once that sunk in the job became far easier.

    A few criticle items with queries but I'm close to being finished.

    Can't thank you enough , and others also.

Posting Permissions

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