Access Syntax Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to get three buckets of users based on their variation in number
of transactions between two years. I get an error message stating
"Syntax Error (Missing Operator) in query expression"
Below is the query:
SELECT a.BuyerID, a.SiteName, a.BuyerSegment, a.TotalTransactions AS
2005_Transactions, b.TotalTransactions AS 2006_Transactions, a.GMB AS
2005_GMB, b.GMB AS 2006_GMB,
CASE WHEN (((b.TotalTransactions) - (a.TotalTransactions)) /
(a.TotalTransactions)) * 100 > -50.00 THEN "Neither"
WHEN (((b.TotalTransactions) - (a.TotalTransactions))
/ (a.TotalTransactions)) * 100 <= -50.00 AND >=-90.00 THEN "Decliner"
WHEN (((b.TotalTransactions) - (a.TotalTransactions))
/ (a.TotalTransactions)) * 100 < -90.00 THEN "Lapser"
ELSE "Unknown" END as Decliners_Lapsers

FROM Top_Buyers_2005_FR AS a LEFT JOIN Top_Buyers_2005_06_FR AS b ON
a.BuyerID=b.BuyerID
WHERE a.BuyerSegment In ('next19.9');
 
hiteshiyer said:
I'm trying to get three buckets of users based on their variation in number
of transactions between two years. I get an error message stating
"Syntax Error (Missing Operator) in query expression"
Below is the query:
SELECT a.BuyerID, a.SiteName, a.BuyerSegment, a.TotalTransactions AS
2005_Transactions, b.TotalTransactions AS 2006_Transactions, a.GMB AS
2005_GMB, b.GMB AS 2006_GMB,
CASE WHEN (((b.TotalTransactions) - (a.TotalTransactions)) /
(a.TotalTransactions)) * 100 > -50.00 THEN "Neither"
WHEN (((b.TotalTransactions) - (a.TotalTransactions))
/ (a.TotalTransactions)) * 100 <= -50.00 AND >=-90.00 THEN "Decliner"
WHEN (((b.TotalTransactions) - (a.TotalTransactions))
/ (a.TotalTransactions)) * 100 < -90.00 THEN "Lapser"
ELSE "Unknown" END as Decliners_Lapsers

FROM Top_Buyers_2005_FR AS a LEFT JOIN Top_Buyers_2005_06_FR AS b ON
a.BuyerID=b.BuyerID
WHERE a.BuyerSegment In ('next19.9');
Click to expand...

Access, respectively the Jet Engine, has no "CASE WHEN" construct.
Probably it would be easiest to write a VBA function like

Function BuyerType(ATotal As Double, BTotal As Double) As String

Dim dblLimit As Double

dblLimit = ((BTotal - ATotal) / ATotal) * 100
If dblLimit > -50 Then
BuyerType = "Neither"
ElseIf dblLimit <= -50 And dblLimit >= -90 Then
BuyerType = "Decliner"
ElseIf dblLimit < -90 Then
BuyerType = "Lapser"
Else
BuyerType = "Unknown"
End If

End Function

then use it in the SQL in place of the CASE WHEN as follows:

BuyerType(a.TotalTransaction, B.TotalTransactions) As
Decliners_Lapsers

HTH
Matthias Kläy
 
Try this (UNTESTED) ---
SELECT a.BuyerID, a.SiteName, a.BuyerSegment, a.TotalTransactions AS
2005_Transactions, b.TotalTransactions AS 2006_Transactions, a.GMB AS
2005_GMB, b.GMB AS 2006_GMB,
IIF((b.TotalTransactions - a.TotalTransactions) / a.TotalTransactions * 100
-50.00, "Neither", IIF((b.TotalTransactions - a.TotalTransactions)/
Click to expand...
a.TotalTransactions * 100 Between -50.00 AND -90.00, "Decliner",
IIF((b.TotalTransactions - a.TotalTransactions) / a.TotalTransactions * 100 <
-90.00, "Lapser", "Unknown"))) AS Decliners_Lapsers
FROM Top_Buyers_2005_FR AS a LEFT JOIN Top_Buyers_2005_06_FR AS b ON
a.BuyerID=b.BuyerID
WHERE a.BuyerSegment In ('next19.9');

Not sure about your ---- In ('next19.9')
 
I didn't think Access queries supported CASE WHEN syntax. I would model this
with a table of ranges where "Decliner", -50, -90, and "Lapser" were all data
values in a table.
 
With Jet? Within MS ACCESS, so you can use VBA?


SELECT a.BuyerID, a.SiteName, a.BuyerSegment, a.TotalTransactions AS
2005_Transactions, b.TotalTransactions AS 2006_Transactions, a.GMB AS
2005_GMB, b.GMB AS 2006_GMB,
SWITCH ( (((b.TotalTransactions) - (a.TotalTransactions)) /
(a.TotalTransactions)) * 100 > -50.00 ,"Neither"
,(((b.TotalTransactions) - (a.TotalTransactions))
/ (a.TotalTransactions)) * 100 <= -50.00 AND >=-90.00 ,"Decliner"
,(((b.TotalTransactions) - (a.TotalTransactions))
/ (a.TotalTransactions)) * 100 < -90.00 ,"Lapser"
, TRUE, "Unknown" ) as Decliners_Lapsers

FROM Top_Buyers_2005_FR AS a LEFT JOIN Top_Buyers_2005_06_FR AS b ON
a.BuyerID=b.BuyerID
WHERE a.BuyerSegment In ('next19.9');




I just changed CASE WHEN x1 THEN y1 WHEN x2 THEN y2 WHEN xi THEN yi ELSE
yDefault END

to SWITCH( x1, y1, x2, y2, xi, yi, TRUE, yDefault)



Hoping it may help,
Vanderghast, Access MVP
 
'next19.9' is a value within column BuyerSegment and I want to perform this
function only on those rows that have a value of 'next19.9' within Column
BuyerSegment
 
Ok, i'll give that a try, just out of curiosity, would I be able to perform
this task using the IIF function?
 
It gives me the same error message.

Regards,

Michel Walsh said:
With Jet? Within MS ACCESS, so you can use VBA?


SELECT a.BuyerID, a.SiteName, a.BuyerSegment, a.TotalTransactions AS
2005_Transactions, b.TotalTransactions AS 2006_Transactions, a.GMB AS
2005_GMB, b.GMB AS 2006_GMB,
SWITCH ( (((b.TotalTransactions) - (a.TotalTransactions)) /
(a.TotalTransactions)) * 100 > -50.00 ,"Neither"
,(((b.TotalTransactions) - (a.TotalTransactions))
/ (a.TotalTransactions)) * 100 <= -50.00 AND >=-90.00 ,"Decliner"
,(((b.TotalTransactions) - (a.TotalTransactions))
/ (a.TotalTransactions)) * 100 < -90.00 ,"Lapser"
, TRUE, "Unknown" ) as Decliners_Lapsers

FROM Top_Buyers_2005_FR AS a LEFT JOIN Top_Buyers_2005_06_FR AS b ON
a.BuyerID=b.BuyerID
WHERE a.BuyerSegment In ('next19.9');




I just changed CASE WHEN x1 THEN y1 WHEN x2 THEN y2 WHEN xi THEN yi ELSE
yDefault END

to SWITCH( x1, y1, x2, y2, xi, yi, TRUE, yDefault)



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
 
The AND >= is illegal.



,(((b.TotalTransactions) - (a.TotalTransactions))
/ (a.TotalTransactions)) * 100 <= -50.00 AND >=-90.00



use BETWEEN, or repeat the lhs argument:


(((b.TotalTransactions) - (a.TotalTransactions))
/ (a.TotalTransactions)) * 100 BETWEEN -90.00 AND -50.00


Note that Jet does not care if the BETWEEN values are out of order: x
BETWEEN 10 AND 2 will be the same as x BETWEEN 2 AND 10, but not
all SQL dialects perform in the same manner.



Hoping it may help,
Vanderghast, Access MVP
 
YEYYY... the second option worked... repeating the argument instead of using
Between...

Thank you! so much...

Regards,
 
Sure, you could use IIf() or Switch() or a user-defined function. However,
these solutions all are undesireable since the min and max and title values
may change in the future. You should NEVER create an application that
requires changing expressions in order to keep up with changed ranges of
values. This should be maintained in data, not expressions.

My second preference would be the function suggested by Matthias since it
keeps the logic in one, easily maintained place in your application. If you
don't understand much about VBA, this is an excellent place to start ;-).

Create a new module and past his function into the code window. Save the
module with the name "modBusinessCalcs". You can then use the function almost
anywhere in your application. Matthias gave you the syntax for using the
function in your query.

I actually don't care for multiple ElseIfs etc. My stab at a function would
be something like (you may need to modify the logic):
Function BuyerType(ATotal As Double, BTotal As Double) As String
Dim dblLimit As Double
dblLimit = ((BTotal - ATotal) / ATotal) * 100
Select Case dblLimit
Case Is > -50
BuyerType = "Neither"
Case Is >= -90
BuyerType = "Decliner"
Case Is < -90
BuyerType = "Lapser"
Case Else
BuyerType = "Unknown"
End Select
End Function
You can test your functions by opening the immediate window (press ctrl+G)
and enter:
+--------------------
| ? BuyerType(100,60)
|
|
Press the enter key after entering the above and your answer should display.
Humor me and give this a try.
 
Back
Top