Syntax Error (missing Operator)

[tlindeman]

Hello,
I have been fighting with this Access query all morning, however, I think I am close. Right now, it is not complete, but you will get the idea. What I am trying to do is if sales (IVRNET3) are between certain numbers than categorize them in the AUV Strat field. I am using switch , however, this query gives me a Syntaxt error (missing operator error). Please help.

SELECT Switch([ 2999.99 <= Factsalesweekly.[ivrnet3] > 0,'000-2999') As 'AUV_STRAT',F.DateOfBusiness,
f.Storenumber ,IVRNet2, IvrNet3
FROM vwSiteType st INNER JOIN vwStoreAdHoc sa
ON St.SiteType = SA.SiteTypeName
INNER JOIN FactSalesWeekly f
ON SA.StoreNumber = F.StoreNumber
WHERE (((F.DateOfBusiness)>='6/24/2012' And
(F.DateOfBusiness)<='07/8/2012') AND
((St.SiteTypeCategory)='Trad (x C&G)') AND
((F.WeeksOpenAsOf)>26) AND
((SA.Country)='USA') AND
((sa.CloseDate) Is Null Or (sa.CloseDate)>=([DateOfBusiness]-6)) AND
((sa.StateOrProvince)<>'PR') AND
((F.QuarantineFlag)=0))

 

[plog]

Re: Syntaxt Error (missing Operator)

First line, you have an unclosed left bracket:

SELECT Switch([ 2999.99 <= Factsalesweekly.[ivrnet3] > 0,'000-2999') As 'AUV_STRAT',F.DateOfBusiness


However, even without that, I don't think its going to past muster. What exactly are you trying to do with that? Explain in english what this

Switch([ 2999.99 <= Factsalesweekly.[ivrnet3] > 0,'000-2999') As 'AUV_STRAT'

is supposed to do.

 

[tlindeman]

Thank You for your help, what I am trying to do is classify sales in different categories (Strats) so we call sales IVRNET3, so when IVRNET3 is greater than 0 but less than or equal 2999.99 then I want it state 0000-2999 in the blank column which I am calling AUV_STRAT, when sales are greater than 2999.99 and less than or equl to 3999.99, I want it to state 3000-3999 in the AUV_STRAT column. I am using Access 2010.

Thank You
Tony

 

[boblarson]

Thank You for your help, what I am trying to do is classify sales in different categories (Strats) so we call sales IVRNET3, so when IVRNET3 is greater than 0 but less than or equal 2999.99 then I want it state 0000-2999 in the blank column which I am calling AUV_STRAT, when sales are greater than 2999.99 and less than or equl to 3999.99, I want it to state 3000-3999 in the AUV_STRAT column. I am using Access 2010.

Thank You
Tony

That makes more sense. So you have your equality stuff off:

SELECT Switch(Factsalesweekly.[ivrnet3] > 0 And Factsalesweekly.[ivrnet3]<= 2999.99,'000-2999', Factsalesweekly.[ivrnet3]>2999.99 AND Factsalesweekly.[ivrnet3]<=3999.99, '3000-3999') As 'AUV_STRAT',F.DateOfBusiness

 

[tlindeman]

Thanks Bob, However, I am still getting a missing operator error, any ideas?

SELECT Switch(Factsalesweekly.[IVRnet3] > 0 And Factsalesweekly.[IVRnet3]<= 2999.99,'0000-2999, Factsalesweekly.[ivrnet3]>2999.99 AND Factsalesweekly.[ivrnet3]<=3999.99, '3000-3999') As 'AUV_STRAT',F.DateOfBusiness,f.Storenumber ,IVRNet2,IVRNet3,
FROM vwSiteType st INNER JOIN vwStoreAdHoc sa
ON St.SiteType = SA.SiteTypeName
INNER JOIN FactSalesWeekly f
ON SA.StoreNumber = F.StoreNumber
WHERE (((F.DateOfBusiness)>='6/24/2012' And
(F.DateOfBusiness)<='07/8/2012') AND
((St.SiteTypeCategory)='Trad (x C&G)') AND
((F.WeeksOpenAsOf)>26) AND
((SA.Country)='USA') AND
((sa.CloseDate) Is Null Or (sa.CloseDate)>=([DateOfBusiness]-6)) AND
((sa.StateOrProvince)<>'PR') AND
((F.QuarantineFlag)=0))

 

[boblarson]

Well, there is this:

FROM vwSiteType st INNER JOIN vwStoreAdHoc sa
and
INNER JOIN FactSalesWeekly f


Are you trying to use

FROM vwSiteType AS st INNER JOIN vwStoreAdHoc AS sa
INNER JOIN FactSalesWeekly AS f


And then for your dates, if they are date fields, it would need octothorpes instead of quotes:

WHERE (((F.DateOfBusiness)>=#6/24/2012# And
(F.DateOfBusiness)<=#07/8/2012#) AND

 

[tlindeman]

Bob,
I cleaned it up( I had some SQL paramters wrong) and the query works great, thank you very much for your help

Tony