Solved

Syntax error missing operator in query expression - Access SQL error

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now