translate SQL to Access

  • Thread starter Thread starter Dale Fye
  • Start date Start date
D

Dale Fye

You cannot use a case statement in JET SQL.

What are you doing with this query? Is the the recordsource for a form? If
so, you could leave out the orderby clause and set the forms Order By
property to the field you want to use. In your where clause, you will have
to replace the CASE statements with IIF( ) statements to accomplish that
functionality, although I'm not entirely sure what you are trying to
accomplish with these Case statements.

Dale
 
I'm trying to translate the below query I had in SQL for use in Access. I'm
getting a syntax error (missing operator) in this clause ...LIKE CASE 'C'
..... The cursor has 'C' selected. How do I get this converted. Thanks!
-----------------------------------------------
SELECT GenKStock.OrderNo, GenKStock.Description, GenKStock.SingleArtist,
GenKStock.Type, GenKStock.Packed, GenKStock.Manuf, GenKTitles.Title,
GenKTitles.Artist, GenKStock.Category,
GenKStock.Label, GenKStock.Type
FROM GenKStock INNER JOIN
GenKTitles ON GenKStock.OrderNo = GenKTitles.OrderNo
WHERE (GenKTitles.Title LIKE '%' + 'T' + '%')
AND (GenKTitles.Artist LIKE '%' + 'A' + '%')
AND (GenKStock.Category LIKE CASE 'C' WHEN '%' THEN '%' ELSE 'C' END) <---
Problem row
AND (GenKStock.Type LIKE CASE 'T' WHEN '%' THEN '%' ELSE 'T' END)
AND (GenKStock.Manuf LIKE CASE 'M WHEN '%' THEN '%' ELSE 'M' END)
AND (GenKStock.SingleArtist LIKE CASE 'SA' WHEN '' THEN '%' ELSE 'SA' END)
AND (GenKStock.Description LIKE CASE 'DD' WHEN '%' THEN '%' ELSE '%' + 'DD'
+ '%' END)
AND (GenKStock.SoftHard = 'Soft')
ORDER BY
CASE @Column WHEN 'orderno_asc' THEN GenKStock.OrderNo END ASC,
CASE @Column WHEN 'title_asc' THEN GenKTitles.Title END ASC,
CASE @Column WHEN 'artist_asc' THEN GenKTitles.Artist END ASC,
CASE @Column WHEN 'label_asc' THEN GenKStock.Label END ASC,
CASE @Column WHEN 'type_asc' THEN GenKStock.Type END ASC,
CASE @Column WHEN 'discdescription_asc' THEN GenKStock.Description END ASC,
CASE @Column WHEN 'manuf_asc' THEN GenKStock.Manuf END ASC,
CASE @Column WHEN 'singleartist_asc' THEN GenKStock.SingleArtist END ASC,
--DESCENDING ORDER STARTS HERE
CASE @Column WHEN 'orderno_desc' THEN GenKStock.OrderNo END DESC,
CASE @Column WHEN 'title_desc' THEN GenKTitles.Title END DESC,
CASE @Column WHEN 'artist_desc' THEN GenKTitles.Artist END DESC,
CASE @Column WHEN 'label_desc' THEN GenKStock.Label END DESC,
CASE @Column WHEN 'type_desc' THEN GenKStock.Type END DESC,
CASE @Column WHEN 'discdescription_desc' THEN GenKStock.Description END
DESC,
CASE @Column WHEN 'manuf_desc' THEN GenKStock.Manuf END DESC,
CASE @Column WHEN 'singleartist_desc' THEN GenKStock.SingleArtist END DESC,
GenKTitles.Title ASC
 
Back
Top