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.
2  85834  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!
 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.
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
functions. This typically increased the count of the functions.
|
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
|
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 .,...
|
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 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
| | |
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
|
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
Case Is = "dim The light" as well
|
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%'
|
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,...
|
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...
| | |
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...
|
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,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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();...
|
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...
| | |
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
| |
