Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

switch statement access sql

Posted on 2010-08-16
11
Medium Priority
?
448 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
11 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 60

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 60

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

971 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