Solved

switch statement access sql

Posted on 2010-08-16
11
442 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 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 59

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

713 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