Results 1 to 10 of 10
  1. #1
    Andy camp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    5

    Syntax error missing operator in query expression


    Hello,

    I ran the below query its says: syntax error missing operator in query expression 'IIF( AND ( SummaryTable.NumberDrivers = "NumberDrivers1"; SummaryTable.NumberClaims = "NumberClaims1"); [Total]*[CarValue];

    Please help find the syntax issue?


    UPDATE ProfileData, SummaryTable
    SET ProfileData.CarValueO1 = IIF( AND( SummaryTable.NumberDrivers = "NumberDrivers1" ; SummaryTable.NumberClaims ="NumberClaims1") ; [Total]*[CarValue] ; CarValueO1 )
    , ProfileData.CarValueO2 = IIF( AND( SummaryTable.NumberDrivers = "NumberDrivers2" ; SummaryTable.NumberClaims ="NumberClaims2") ; [Total]*[CarValue] ; CarValueO2 )
    WHERE ((SummaryTable.CarType)="Sedan") ;

    Thanks

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,302
    IIF( AND( SummaryTable.NumberDrivers = "NumberDrivers1" ; SummaryTable.NumberClaims ="NumberClaims1") ; [Total]*[CarValue] ; CarValueO1 )
    This looks like EXCEL syntax.
    In Access, try
    Code:
    IIF(SummaryTable.NumberDrivers = "NumberDrivers1" AND  SummaryTable.NumberClaims ="NumberClaims1" ; [Total]*[CarValue] ;  CarValueO1 )
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    Andy camp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    5
    Thanks for your reply.

    The error now says characters found at end of SQL statement. I've checked for spaces, not sure what this means?


    UPDATE ProfileData, SummaryTable
    SET ProfileData.CarValueO1 = IIF( SummaryTable.NumberDrivers = "NumberDrivers1" AND SummaryTable.NumberClaims ="NumberClaims1") ; [Total]*[CarValue] ; CarValueO1 ),
    ProfileData.CarValueO2 = IIF( SummaryTable.NumberDrivers = "NumberDrivers2" AND SummaryTable.NumberClaims ="NumberClaims2") ; [Total]*[CarValue] ; CarValueO2 )
    WHERE ((SummaryTable.CarType)="Sedan");

    Characters found at end of SQL statement

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,302
    IIF( SummaryTable.NumberDrivers = "NumberDrivers1" AND SummaryTable.NumberClaims ="NumberClaims1") ; [Total]*[CarValue] ; CarValueO1 ),
    My bad. Sorry I missed that. The semi-colon character is the SQL end-of-line terminator.
    Code:
    IIF( SummaryTable.NumberDrivers = "NumberDrivers1" AND  SummaryTable.NumberClaims ="NumberClaims1") ; [Total]*[CarValue] ;  CarValueO1 ),
    The semi-colons should be commas
    Code:
    UPDATE ProfileData, SummaryTable
    SET ProfileData.CarValueO1 = IIF( SummaryTable.NumberDrivers = "NumberDrivers1" AND SummaryTable.NumberClaims ="NumberClaims1") , [Total]*[CarValue] , CarValueO1 ),
    ProfileData.CarValueO2 = IIF( SummaryTable.NumberDrivers = "NumberDrivers2" AND SummaryTable.NumberClaims ="NumberClaims2") , [Total]*[CarValue] , CarValueO2 )
    WHERE ((SummaryTable.CarType)="Sedan");
    You need to change all of the semi-colons to commas. You should keep the very last semi-colon.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    Andy camp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    5
    Thanks for your post.

    Unfortunately there is still a "syntax error in the update statement".

    Can you see the problem?

    I thought this query would be relatively simple its been really difficult to solve.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,302
    Just checking - is "CarValueO1" and "CarValueO2" the letter Oh or a zero?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,302
    Where do the values for [Total] and [CarValue] come from? A form or a table?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,302
    You missed two closing parenthesis (in RED)
    Code:
    UPDATE ProfileData, SummaryTable
    SET ProfileData.CarValueO1 = IIF( SummaryTable.NumberDrivers = "NumberDrivers1" AND SummaryTable.NumberClaims ="NumberClaims1") , [Total]*[CarValue] , CarValueO1 ),
    ProfileData.CarValueO2 = IIF( SummaryTable.NumberDrivers = "NumberDrivers2" AND SummaryTable.NumberClaims ="NumberClaims2") , [Total]*[CarValue] , CarValueO2 )
    WHERE ((SummaryTable.CarType)="Sedan");

    Try this
    Code:
    UPDATE ProfileData, SummaryTable 
    SET ProfileData.CarValueO1 = IIf([SummaryTable].[NumberDrivers]="NumberDrivers1" And [SummaryTable].[NumberClaims]="NumberClaims1",[Total]*[CarValue], CarValueO1), ProfileData.CarValueO2 = IIf([SummaryTable].[NumberDrivers]="NumberDrivers2" And [SummaryTable].[NumberClaims]="NumberClaims2",[Total]*[CarValue], CarValueO2)
    WHERE (((SummaryTable.CarType)="Sedan"));
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    Andy camp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    5
    Thanks, it worked!

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,302
    Wonderful!!
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-08-2013, 12:59 PM
  2. Replies: 4
    Last Post: 07-25-2012, 04:01 AM
  3. Replies: 6
    Last Post: 05-30-2012, 12:32 PM
  4. Replies: 3
    Last Post: 08-19-2011, 09:06 AM
  5. Replies: 1
    Last Post: 10-07-2009, 07:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums