By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,466 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Case statements using SQL in VB Editor in Access

P: 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?
Jul 7 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.