Solved

Syntax error missing operator in query expression - Access SQL error

Posted on 2013-07-01
4
1,950 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
4 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 75 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 75 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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

839 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