473,326 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

CASE Statements using SQL in MS Access

2
Can someone help me please. I have data from another system passed into an Access db that we use to run queries. One of the fields gets passed codes: G, S, Q. I wanted to know how to have them displayed as GGG, SSS, QQQ for userfriendliness. There is other complex stuff going on where i need to do this through the visual basic editor in access. It does not appear to be working. This is what i have so far:

strSQL1 = "SELECT DISTINCTROW [dbo_ContactAccount_vw].[contactid], [Account].[Location], market = CASE [Account].[market] WHEN 'G' THEN 'GGG' WHEN 'Q' THEN 'QQQ' WHEN 'S' THEN 'SSS' ELSE [Account].[market] END,"

strSQL2 = "FROM ([Account] INNER JOIN [ContactAccount] ON [Account].[AccountId] = [ContactAccount].[accountid]) INNER JOIN [Contact] ON [ContactAccount].[contactid] = [Contact].[ContactId] WHERE ("

strSQL3 = "(([Account].[Owner]) " & strOwner & ") AND (([Account].[Market]) " & strMarket & ") AND (([Contact].[Never_Mail]) " & strNeverMail & "))"

strSQL5 = " OR "

strSQL6 = "((([Account].[Owner]) " & strOwner & ") AND (([Account].[Region]) " & strRegion & ") AND AND (([Account].[Market]) " & strMarket & ") AND (([Contact].[NeverEmail]) " & strNeverEmail & ") AND (([Contact].[Never_Mail]) " & strNeverMail & ")));"

If (Me!BODList.ItemsSelected.Count = 0) And (Me!RoleList.ItemsSelected.Count > 0) Then
qdf.SQL = strSQL1 & strSQL2 & strSQL3 & strSQL4 & ");"
End If

What's giving me the problem case statement portion in strSQL1. When i take that out, the query runs properly but gives me the Q, G, S values which means nothing to outsiders. Does anyone know what i am doing wrong? I am in access 2003.
Jul 10 '06 #1
2 85793
PEB
1,418 Expert 1GB
Hi,
For the CASE statement, in fact MS Access SQL doesn't have CASE statement. You have to replace it with the statement Switch(A=B,B*58,B=C,B*56) and so on!
For the rest I can't help you!
Aug 29 '06 #2
MMcCarthy
14,534 Expert Mod 8TB
As the previous expert stated Access SQL doesn't allow CASE statements. Can you set up a Lookup table in the SQL backend to the effect the "G" = "GGG", etc. Then use an INNER JOIN statement on Account.Market = Lookup.Market and SELECT the second field in the lookup table.

Can someone help me please. I have data from another system passed into an Access db that we use to run queries. One of the fields gets passed codes: G, S, Q. I wanted to know how to have them displayed as GGG, SSS, QQQ for userfriendliness. There is other complex stuff going on where i need to do this through the visual basic editor in access. It does not appear to be working. This is what i have so far:

strSQL1 = "SELECT DISTINCTROW [dbo_ContactAccount_vw].[contactid], [Account].[Location], market = CASE [Account].[market] WHEN 'G' THEN 'GGG' WHEN 'Q' THEN 'QQQ' WHEN 'S' THEN 'SSS' ELSE [Account].[market] END,"

strSQL2 = "FROM ([Account] INNER JOIN [ContactAccount] ON [Account].[AccountId] = [ContactAccount].[accountid]) INNER JOIN [Contact] ON [ContactAccount].[contactid] = [Contact].[ContactId] WHERE ("

strSQL3 = "(([Account].[Owner]) " & strOwner & ") AND (([Account].[Market]) " & strMarket & ") AND (([Contact].[Never_Mail]) " & strNeverMail & "))"

strSQL5 = " OR "

strSQL6 = "((([Account].[Owner]) " & strOwner & ") AND (([Account].[Region]) " & strRegion & ") AND AND (([Account].[Market]) " & strMarket & ") AND (([Contact].[NeverEmail]) " & strNeverEmail & ") AND (([Contact].[Never_Mail]) " & strNeverMail & ")));"

If (Me!BODList.ItemsSelected.Count = 0) And (Me!RoleList.ItemsSelected.Count > 0) Then
qdf.SQL = strSQL1 & strSQL2 & strSQL3 & strSQL4 & ");"
End If

What's giving me the problem case statement portion in strSQL1. When i take that out, the query runs properly but gives me the Q, G, S values which means nothing to outsiders. Does anyone know what i am doing wrong? I am in access 2003.
Aug 29 '06 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: db2sysc | last post by:
All. We have LOT of variables declared in the Oracle package as ORACLE CONSTANTS like, v_test CONSTANT INTEGER=1; When converting to DB2, MTK changes each of these CONSTANTs into...
3
by: Fanofmsu | last post by:
Hi - from my limited expereince in DB2, it appears that you can't include a case statement in a group by clause - is this correct? For example, my SQL looked like this: SELECT CASE WHEN...
0
by: shah | last post by:
Can someone help me please. I have data from another system passed into an Access db that we use to run queries. One of the fields gets passed codes: G, S, Q. I wanted to know how to have them...
5
by: alacrite | last post by:
If I have code like this: int main() { a=1; b=2; x=0; switch(a) {
0
by: java7man | last post by:
All - below is part of a construct I am using. I have some very complex data and processing to perfrom and I need a way to get beyond case limitations. Anyone know of other more robust constructs...
6
by: Fir5tSight | last post by:
Hi All, I have a "SELECT" statement in a stored procedure that looks like the follows: ...
1
by: poops2468 | last post by:
I have about 100 functions that each have a Select Case statement as their main driver. I need to retrieve the number of actual Case statements within each file. How can I do this? Thanks.
6
by: kronecker | last post by:
Is there any way to make case statements case independent? ie Case Is = "dim the light" or Case Is = "Dim the light" making them the same outcome without putting every possibility. eg ...
1
by: weefrenchie | last post by:
Hi, I have a SQL query that looks like this: , CASE WHEN object 1 LIKE '%first%' OR object 1 LIKE '%second%' OR object 2 LIKE '%first%' OR object 2 LIKE '%second%' THEN object 3...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.