Solved

Syntax error missing operator in query expression - Access SQL error

Posted on 2013-07-01
4
1,973 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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 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