Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Syntax error missing operator in query expression - Access SQL error

Posted on 2013-07-01
4
Medium Priority
?
2,107 Views
Last Modified: 2013-07-01
Still working in access.

Here is the SQL from one of the queries in the database:

INSERT INTO t_bookorders
(ISBN, BOOK_ID, BookSequenceNum, Publisher ORDer_DATE, ORDered_QTY, QTYS, Book_PRICE, List_Value_1, List_Value_2,Book_DESC )
SELECT master_Book_List.PROD_ID,
master_Book_List.Book_ID,
master_Book_List.BookSequenceNum, ,
 master_Book_List.Pub,
 master_Book_List.Ordered_DATE,
  master_Book_List.RET_QTY, "Units" AS Expr1,
  master_Book_List.Book_ACT_SELL_PRC,
  master_Book_List.List_value_1,
  CASE WHEN titles.backorder= 'N' THEN 'Verify with teacher"',
       WHEN titles.backorder= = 'Y'  THEN 'add surcharge',
       ELSE
 master_Book_List.value_from_booklist,
  master_Book_List.PROD_DESC
FROM (master_Book_List LEFT JOIN MASTER_PublisherList ON master_Book_List.PROD_ID = master_Book_List.PROD_ID)
LEFT JOIN dupe_Books ON (master_Book_List.BookSequenceNum = q_dupes_1.[SEQ_NBR Field]))
WHERE (((master_Book_List.Mark)=True) AND ((q_dupes_1.[ORD_ID Field]) Is Null));

I get this error around my CASE statements:  "Syntax error missing operator in query expression"

I then substituted the CASE statements with Ifs:

  If titles.backorder= 'N' THEN 'Verify with teacher"',
      ELSE IF  titles.backorder= = 'Y'  THEN 'add surcharge',
       ELSE  master_Book_List.value_from_booklist,
I got the same error.

What am I doing wrong?
0
Comment
Question by:programmher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 300 total points
ID: 39291640
Try this:

 IIf (titles.backorder= 'N' , 'Verify with teacher',
      IIF( titles.backorder = 'Y' , 'add surcharge',
       master_Book_List.value_from_booklist) )

Open in new window

0
 
LVL 77

Accepted Solution

by:
peter57r earned 300 total points
ID: 39291650
If titles.backorder= 'N' THEN 'Verify with teacher"',
      ELSE IF  titles.backorder= = 'Y'  THEN 'add surcharge',
       ELSE  master_Book_List.value_from_booklist,

should be..

Iif (titles.backorder= 'N' , 'Verify with teacher',IIF( titles.backorder= = 'Y', 'add surcharge',
       master_Book_List.value_from_booklist))
0
 
LVL 9

Expert Comment

by:COANetwork
ID: 39291654
CASE is an atomic structure, the entire CASE statement is equivalent to a single column.  So stop putting commas inside the CASE statement - you are breaking it up.
...
...
master_Book_List.List_value_1,
  CASE WHEN titles.backorder= 'N' THEN 'Verify with teacher'
       WHEN titles.backorder= 'Y'  THEN 'add surcharge'
       ELSE master_Book_List.value_from_booklist,
  master_Book_List.PROD_DESC
...
...

Open in new window

0
 

Author Comment

by:programmher
ID: 39291813
Thanks so much - that resolved my error.
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question