473,750 Members | 2,209 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

CASE Statements using SQL in MS Access

2 New Member
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 userfriendlines s. 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_ContactAcco unt_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.Ite msSelected.Coun t = 0) And (Me!RoleList.It emsSelected.Cou nt > 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 85837
PEB
1,418 Recognized Expert Top Contributor
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 Recognized Expert Moderator MVP
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 userfriendlines s. 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_ContactAcco unt_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.Ite msSelected.Coun t = 0) And (Me!RoleList.It emsSelected.Cou nt > 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
1897
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 functions. This typically increased the count of the functions.
3
1915
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 GROUP_CD<'00001' THEN 'ITS' ELSE 'NON' END AS TYPE, COUNT(*) AS CNT FROM tblCLAIMS GROUP BY CASE WHEN GROUP_CD<'00001' THEN 'ITS' ELSE 'NON' END My solution was to not initially group the data, and then summarize
0
1480
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 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 .,...
5
2193
by: alacrite | last post by:
If I have code like this: int main() { a=1; b=2; x=0; switch(a) {
0
3215
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 available in SQL - DB2. The book says only 3 levels of nested case statements are allowed - that is true. Any assistance would be appreciated. select field1 , field2 ,case
6
1805
by: Fir5tSight | last post by:
Hi All, I have a "SELECT" statement in a stored procedure that looks like the follows: -------------------------------------------------------------------------------------------------------------- SELECT CASE WHEN ( ri.Status NOT LIKE '%COMPLETE%' ) -- report not completed
1
1244
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
1367
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 Case Is = "dim The light" as well
1
2648
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 END AS , CASE WHEN object 1 LIKE '%first%'
0
8836
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9575
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9394
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9338
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
6080
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4712
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4885
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3322
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2798
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.