pass a list of parameters to a sql string variable

[Knife1]

Hi.

This question has 2 parts really. I have a form with a command button which runs a sql query in a string variable. The VBA code is below. The sql string basically counts the number of branches with less than 2000 customers from a table with Branch Id and customerTotal columns. However i want to run this query maybe 10 times with different fixed parameters. i.e. where customers is :
<2000
<3000
<4000
<5000 and so on....

So what is the best way of passing these parameters to the query in a loop without having to run the procedure 10 times.
Also I would like to be able to output the 10 count results in a single list, in the query output window, immediate window, table, whatever a long as all the results are in a list. Currently it just runs the one query and open the the query window.

Any pointers... ?





Private Sub cmdCountBranchTotals_Click()


Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim TableName As string


Set db = CurrentDb
Set qrydef = db.QueryDefs("qryCountTotals")
TableName = msgboxTableVersion


strsql = "SELECT Count([Branch ID]) AS CountOfBranch " & _
"FROM [" & TableName & "] " & _
"WHERE CustTotal < 2000; "

qrydef.SQL = strsql

DoCmd.OpenQuery "qryCountTotals", acViewNormal



End Sub



Thanks

[hansup]

I don't see why you need to save a query then run it for each separate case. Seems to me the DCount() function could provide what you want.

I don't know where msgboxTableVersion comes from, but apparently it's working for you now, so I'll hope it continues to work in this version.

Private Sub cmdCountBranchTotals_Click()
Const clngCases As Long = 4
Dim TableName As String
Dim i As Long
Dim strList As String
Dim strLimit As String

TableName = msgboxTableVersion

For i = 1 To clngCases
Select Case i
Case 1
strLimit "< 2000"
Case 2
strLimit "< 3000"
Case 3
strLimit "< 4000"
Case 4
strLimit "< 5000"
End Select

strList = strList & ", " & _
DCount("[Branch ID]", TableName, "CustTotal " & strLimit)
Next i

MsgBox Mid(strList, 3)
End Sub

That should get you a comma-separated list of the values which were returned by your query approach. If you also want to include which condition each value refers to, you can build that information into the list:

strList = strList & ", " & strLimit & ": " & _
DCount("[Branch ID]", TableName, "CustTotal " & strLimit)

[Knife1]

Thanks
hansup

Yes, Dcount is a better idea. Thanks the select case statement works great. you just left the = out of the statements.

The msgboxTableVersion was an input box rather than a message box for a variable table name users can enter on the form/.

[hansup]

you just left the = out of the statements.Yes, I see that now. That emphasizes the value of Debug->Compile. When I tried, it complained about msgboxTableVersion, so I just gave up before it had an opportunity to complain about the missing = signs.

Ahem. Fortunately, it wasn't code for me! :whistle:

Which reminds me ... you'll likely want to include error handling at a minimum for the case where the user types in a table name which doesn't exist.

Regards,
Hans