Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

switch statement access sql

Posted on 2010-08-16
11
Medium Priority
?
451 Views
Last Modified: 2012-05-10
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.
SELECT 
    qryTempSalesForCalculation.[Location Code],
    qryTempSalesForCalculation.[Group Code],
    qryTempSalesForCalculation.[Gross Sales],
	SWITCH(
		[Gross Sales] < [TIER 1 MINIMUM_Standard],
		    "Less",
		[Gross Sales] > '[TIER 1 MINIMUM_Standard]',
		    "More")
	AS [Normal Rebate Amount]
FROM 
    NA_Groups 
INNER JOIN 
    qryTempSalesForCalculation 
ON NA_Groups.[GROUP CODE] = qryTempSalesForCalculation.[Group Code];

Open in new window

0
Comment
Question by:wipnav
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 13

Expert Comment

by:dwkor
ID: 33448162
Something like that
SELECT 
    qryTempSalesForCalculation.[Location Code],
    qryTempSalesForCalculation.[Group Code],
    qryTempSalesForCalculation.[Gross Sales],
	(CASE 
		WHEN 
			[Gross Sales] < [TIER 1 MINIMUM_Standard]
		THEN "Less"
		ELSE 
			CASE 
				WHEN[Gross Sales] > [TIER 1 MINIMUM_Standard]
				THEN "More"
				ELSE "Equal"
			END
	END)
	AS [Normal Rebate Amount]
FROM 
    NA_Groups 
INNER JOIN 
    qryTempSalesForCalculation 
ON NA_Groups.[GROUP CODE] = qryTempSalesForCalculation.[Group Code];

Open in new window

0
 
LVL 1

Author Comment

by:wipnav
ID: 33448206
This is an access database, I don't believe case statements are allowed.
0
 
LVL 75
ID: 33448213
Why do you have single quotes around this:

[Gross Sales] > '[TIER 1 MINIMUM_Standard]',


?

mx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 33448225
The having to use quotes around a double value doesn't seem right at all, so I would ensure by being explicit that the value from other query is a double. Additionally, what happens if they are equal ?
SELECT 
    qryTempSalesForCalculation.[Location Code],
    qryTempSalesForCalculation.[Group Code],
    qryTempSalesForCalculation.[Gross Sales],
	SWITCH(
		CDbl([Gross Sales]) < [TIER 1 MINIMUM_Standard],
		    "Less",
		CDbl([Gross Sales]) > [TIER 1 MINIMUM_Standard],
		    "More", True, "Same")
	AS [Normal Rebate Amount]
FROM 
    NA_Groups 
INNER JOIN 
    qryTempSalesForCalculation 
ON NA_Groups.[GROUP CODE] = qryTempSalesForCalculation.[Group Code];

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33448230

SELECT
    qryTempSalesForCalculation.[Location Code],
    qryTempSalesForCalculation.[Group Code],
    qryTempSalesForCalculation.[Gross Sales],
      SWITCH(
            [Gross Sales] < [TIER 1 MINIMUM_Standard],
                "Less",
            [Gross Sales] > [TIER 1 MINIMUM_Standard],
                "More")
      AS [Normal Rebate Amount]
FROM
    NA_Groups
INNER JOIN
    qryTempSalesForCalculation
ON NA_Groups.[GROUP CODE] = qryTempSalesForCalculation.[Group Code];
0
 
LVL 1

Author Comment

by:wipnav
ID: 33448239
Sorry, I missed removing those in the example. If I have single quotes around [TIER 1 MINIMUM_Standard] for both the < and > of the switch statement, the query returns "More" for all values, but if I leave out the single quotes, every record has #Error.
0
 
LVL 1

Author Closing Comment

by:wipnav
ID: 33448268
Thank you, that was exactly the problem. The [Gross Sales] field was from a seperate query that pulled from a double field BUT I had rounded it to two decimal places because I am dealing with currency which must have changed the format.

Does the value become a string after a round() function?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33448278
where do you get the values for [TIER 1 MINIMUM_Standard]

do you have the field [TIER 1 MINIMUM_Standard] in your query qryTempSalesForCalculation  or in table NA_Groups?
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 33448328
Not as far as I know. Round() should equate to a number. Maybe there was something else in the query like format or something, but glad the explicit conversion to double helped.
0
 
LVL 1

Author Comment

by:wipnav
ID: 33448364
[TIER 1 MINIMUM_Standard0 is in the table NA_Groups
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 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