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,664
    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 )

  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,664
    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.

  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,664
    Just checking - is "CarValueO1" and "CarValueO2" the letter Oh or a zero?

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Where do the values for [Total] and [CarValue] come from? A form or a table?

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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"));

  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,664
    Wonderful!!

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