Solved

Syntax error missing operator in query expression - Access SQL error

Posted on 2013-07-01
4
2,043 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

624 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