Building custom SQL

[rlarkin]

Hello,

I'm using a variation of support.microsoft.com/kb/135546, to build a query with custom columns. I've found a way to do it - using If statements to check the Variant's number in the list and, if it is selected, to add it on the end of a SQL string.

Here's pretty much what I'm doing at the moment:

Sub MakeQuery()
Dim ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strSelect As String
Set ctl = Me!lstFields
For Each varItm In ctl.ItemsSelected
If varItm = 0 Then
      If Len(strSelect) = 0 Then
         strSelect = Chr(34) & "[Framework].[Reference]" & Chr(34)
      Else
         strSelect = strSelect & "," & Chr(34) & _
         "[Framework].[Reference]" _
         & Chr(34)
End If
If varItm = 1 Then
      If Len(strSelect) = 0 Then
         strSelect = Chr(34) & "[Framework].[Forename]" & Chr(34)
      Else
         strSelect = strSelect & "," & Chr(34) & _
         "[Framework].[Forename]" _
         & Chr(34)
End If
Next varItm

What I'm hoping I can do, and what I need help with, if possible, is find a way to replace the separate If statements, for each varItm, with a loop that will use the current variant to determine the string to add to strSelect. Something like: intItm = intItm + 1 at the end of the loop to increment the number, and: If varItm = intItm Then to match the Variant.

What I can't get my head around, is how to use the result to then select an appropriate string. I'm assuming I'd need something like strSelect1 = "[Framework].[Reference]", strSelect2 = [Framework].[Forename], etc., and then have a piece of code after Then, to add the value of varItem to the end of strSelect to get one of the variables. Am I anywhere close?

Thanks,

Russ

 

[mdlueck]

Are you basically coding up a multiple field form to dynamically create the WHERE clause for the query to be run?

If so, then I would define in memory a Collection class, have attributes of the Collection be the DB column name, the name of the field control, etc... so you may simply iterate through the collection and build the WHERE clause.

Last week I found a handy article talking about how to do this sort of thing with the collection class - at least the memory storage bit. Just leverage that IP and make it work for WHERE clause purposes.

"Using Custom Collections in Microsoft Access"
http://www.databaseadvisors.com/new...ng custom collections in microsoft access.asp

Note: You will have to add a couple of classes to your project. The one with the collection class I had end with an "s" and the other one that contains the record memory storage I had NOT end in an "s". So in my case they were "clsObjMultipleItemsForms" and "clsObjMultipleItemsForm".

 

[Rx_]

Looked at the link. This might be a variation you could consider.
Here is an example of a list box with a Select All, or the user can make multiple selections. The first part uses the For Each to build a string for the IN statement of a query. This is passed into a function where the Slecte statement is completed. The choice is either Not In(1000) that would be every thing since the items only go to 200, or a In(2,9,20,122) statement for selected items index.

20       SelectedAreas = ""
30    For Each varItm In Me.lstRegionsReports.ItemsSelected
40        If SelectedAreas = "" Then
50               SelectedAreas = Me.lstRegionsReports.ItemData(varItm)
60        Else
70               SelectedAreas = SelectedAreas & ", " & Me.lstRegionsReports.ItemData(varItm)
80        End If
90    Next
100   If SelectedAreas = "0" Then
110     SQLInFunction = " Not In (1000) " 
120   Else   ' build a list for the 'In'  statement in select statement later
130     SQLInFunction = " In (" & SelectedAreas & ") "
140   End If  
170       Response = ConstructionReclamation_Report(ID_Area:=SQLInFunction)
 
' function code fragement example
Public Function ConstructionReclamation_Report( _
                  Optional ID_Area As String = " not In(1000) ", _
 
' later ...... build Select statement and insert "In" for ID_Area 
strSQLConstructionReclamation = "Select ....."
strSQLConstructionReclamation = strSQLConstructionReclamation & "  WHERE (((Wells.Activity)='A') AND ((Wells_Areas.ID_Area)   " & ID_Area & "))"

 

[rlarkin]

I'm building the SELECT part, to decide what columns appear in the table/report/exported spreadsheet, all the rest will stay the same and is tacked on at the very end. So, at the moment, it's basically:

Loop selected items.
Add string to strSelect if selected.
strSQL = "SELECT" & strSelect & "FROM... WHERE..."
Enter strSQL string into SQL of defined query.
Run query.

Exactly, the same as with the WHERE example in the link, except the names in the list aren't the same thing that is entered into strSelect. Instead I have: If Forename is selected, add [Framework].[Forename] to strSelect.

It's going to take me a while to go through both of your suggestions and figure out what they do, and it's coming to the end of my day. So thank you both very much for your help and I'll get back to you tomorrow.

Russ

 

[Rx_]

This is the same code I use to create custom reports using Excel object code.
If the selected items are used to create a column for eash item selected, consider a crosstab query.