SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using CASE in real world examples


Using CASE in real world examples

Author
Message
johndb264
johndb264
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Hello,

I am new to this forum.

I am using Microsoft Access 2010, which includes SQL.

Here's my problem:

What I want to do is to reference a parameter to a combo box control, so that I can
perform multiple CASE statements. For example:

cbox AS TaskSelection.TaskSelect

SELECT cbox
CASE "Names" THEN SELECT * FROM Names
CASE "Locations" THEN SELECT * FROM Locations
END SELECT

I do hope I got this SQL coding correct;otherwise, correct me on this.

I do hope I get some feedback on this issue.


johndb264
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62481 Visits: 17956
What you are describing is VBA code. You want to run different queries based on the selection from a combobox. However you can't just mix sql and VBA code like that. What you would need to do is execute different sql based on the selection. Maybe you can use your case statement to build a sql string and then run your sql string. You need to do some research on how to execute a query in VBA.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
rf44
rf44
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 1093
The VB/VBA language has a strict syntax. You cannot just type whatever you want an expect that the interpreter/compiler will go with it.

1. cbox AS TaskSelection.TaskSelect
To declare and instanciate a variable on a control of type ComboBox, you use:

Dim cbox As ComboBox
Set cbox = <ComboBox Name>


I can't imagine what TaskSelection.TaskSelect is supposed to be in this case.

2. A Select Case block is built as follows:

Select Case <Some variable>
Case <Value 1>
'Code to be executed when <Some variable> = <Value 1>
Case <Value 2>
'Code to be executed when <Some variable> = <Value 2>
Case ...
End Select



3. The VB/VBA interpreter cannot directly perform any SQL expression. You must build (assemble) the SQL expression in a String variable then pass this variable to "something" that can execute the query contained in the string expression in the variable. In MS Access, "something" can be a Form (with it's RecordSource property), a ComboBox or a ListBox control (with it's RowSource property), the CurrentDb object (through it's Execute method - this only works with action queries). You can also declare and instanciate a "data aware" object from a library (DAO, ADO, ...) such as a Database, QueryDef or RecordSet object (DAO), a Command or a RecordSet object (ADO).

You don't explain what you want to do with the query. In MS Access, a query is often used to open a RecordSet or to be used as the RecordSource property of a Form (or as the RowSource property of a ListBox/ComboBox, which is almost the same).

Here is a rewritten version of your code where the following assumptions were made:
- The code is in the Class Module of a Form.
- This Form has a ComboBox named ComboTaskSelect among its controls.
- The Bound Column of the ComboBox contains string data such as "Names", "Locations", etc.
- The SQL string will be used later on for a purpose that remains to be defined (see above).

Private Sub ComboTaskSelect_AfterUpdate()

Dim cbox As ComboBox
Dim strSQL As String

Set cbox = Me.ComboTaskSelect

Select Case cbox.Value
Case "Names"
strSQL = "SELECT * FROM Names"
Case "Locations"
strSQL = "SELECT * FROM Locations"
End Select

End Sub



Have a nice day!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search