CODEWITHSUNDEEP
c#ms-accessswitch-statementsubquery
Glenn Angel
Glenn Angel

Reputation: 479

Case Statement - using BETWEEN. Ms Access to SQL Server

Hi have made a query in ms access, as my DB is access, however i am creating reports in C# RDLC.

Im just going through making my dataadapters for the reports and i just can't get this CASE statement and BETWEEN to work!!

Now funnily it does actually produce the correct data.. but i get an error:

Error in list of function arguments: 'BETWEEN' not recognized. Unable to parse query text.

SELECT        [CountAge].AgeGroup, COUNT(*) AS CountOfAgeGroup
FROM
(SELECT Switch([Age] BETWEEN 0 AND 17, '0-17',
 [Age] BETWEEN 18 AND 30, '18-30', 
[Age] BETWEEN 30 AND 45, '30-45', 
[Age] BETWEEN 45 AND 60, '45-60', 
[Age] > 60, 'Over 60') AS AgeGroup

FROM
(SELECT tblcustomers.firstname, tblcustomers.lastname, Year(Now()) - 
Year([DOB])  AS Age FROM TblCustomers )) as CountAge


GROUP BY [CountAge].AgeGroup

I've tried a number of things but i cant seem to get it right! Any advice would be great.

It also has an error with Year(date) and changes to year but it works when i change it back.

Update - Clarification (because I'm stupidly vague - sorry)

So just clarifying my problem. My DB is in access - not sql server - that was a bad tag of mine.

I have created the query inside Access and it works, exactly as it is above. HOWEVER I'm trying to create it as a datatable for a report in Visual Studio as an RDLC report.

In Dataset Designer I am adding a datatable usign the above query however it doesnt work due to the aforementioned error (BETWEEN).

I have since tried Gustav's suggestion in replacing BETWEEN with > and >= etc however i now get this error;

Error in list of function arguments: '<' not recognized. Unable to parse query text.

Update 2: THIS QUERY WORKS.. as in it DOES create results that are correct, however because of this Unable to Parse Query Error.. i cant actually SAVE it. Ideas?

Upvotes: 0

Views: 467

Answers (2)

Gopal Sharma
Gopal Sharma

Reputation: 1

    Select case when [Age] between 0 and 17 then '0-17' 
           when [Age] between 18 and 30 then '18-30' 
           when [Age] between 31 and 45 then '31-30' 
           when [Age] between 46 and 60 then '46-60' 
           when [Age] >60 then '>60'
            else 'Invalid Age' end 
    from (SELECT tblcustomers.firstname, tblcustomers.lastname, Year(Now())  
    Year([DOB])  AS Age FROM TblCustomers) as CountAge

Else Case is for when wrong data entered in Database like future date. User defined function is most suitable solution in current scenario as per usability and maintainability.

Upvotes: 0

Gustav
Gustav

Reputation: 55961

The Switch function doesn't take SQL syntax. So:

Switch([Age] <= 17, '0-17',
    [Age] <= 30, '18-30', 
    [Age] <= 45, '30-45', 
    [Age] <= 60, '45-60', 
    [Age] > 60, 'Over 60')

Upvotes: 1

Related Questions