Below is a query I am trying to run that is not working properly. Even when the Gross Sales are less than the TIER 1 MINIMUM_Standard value, the query still returns "More" as the Normal Rebate Amount. The TIER 1 MINIMUM_Standard field is a double in access so I don't think it should have single quotes around it but if I don't use the quotes I get #Error for my results. The Gross Sales field is generated by another query that rounds the sum of a double field so I don't know why they are not comparing correctly. Below is the query I am using the switch statement in.
[Gross Sales] < [TIER 1 MINIMUM_Standard],
[Gross Sales] > '[TIER 1 MINIMUM_Standard]',
AS [Normal Rebate Amount]
ON NA_Groups.[GROUP CODE] = qryTempSalesForCalculation.[Group Code];