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  85806  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: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
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,...
|
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
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...
| |
