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.
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!
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
by: alacrite |
last post by:
If I have code like this:
int main()
{
a=1;
b=2;
x=0;
switch(a)
{
|
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...
|
by: Fir5tSight |
last post by:
Hi All,
I have a "SELECT" statement in a stored procedure that looks like the
follows:
...
|
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.
|
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
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |