MS Access Forum / Queries / November 2006
Dynamically getting IN field list from text box on form
|
|
Thread rating:  |
JB - 28 Jul 2006 22:54 GMT I have a complex query with multiple rows and columns of criteria that I am modifying for a new issue for my client. Originally I had a Network field in the query that set the criteria string as:
In("choice1","choice2","choice3","choice4","choice5", "choice6")
where the choices were hard coded. This has always worked fine.
Now the choices need to be dynamic based on the selection in the form. I do not want to dynamically write the whole SQL string if I can help it, since it would be a long complex mess. I just need to fix this one field's criteria setting. Based on a check box on the form, the Network
critieria now needs to be:
In("choice1","choice2","choice3","choice4","choice5", "choice6")
or it may need to be:
In("choice1","choice2","choice3","choice4","choice7")
I am trying to build the string in a text box on the form and then have
the query refer to the form field. No matter what combinations I try with double or single quotes, the query returns no records. If I manually test the query, it works fine and there are many records.
My query criteria for the field looks like: "IN (" & [Forms]![frmWeeklyBroadcastSchedule]![txtNetworks] & " )"
My code on the form looks like: If Me.chkOldNetwork Then Me.txtNetworks = """""abc"""",""""cbs"""",""""nbc"""",""""fox"""",""""wb"""",""""upn"""""
Else Me.txtNetworks = """""abc"""",""""cbs"""",""""nbc"""",""""fox"""",""""cw""""" End If
If I use Debug.Print to test the result, I get: IN (""abc"",""cbs"",""nbc"",""fox"",""cw"" )
I just want a version with a single quote for the IN statement but the compiler chokes on other combinations of quotes in the IF statement. I have tried using OR instead of IN where appropriate but I get the same problem.
Does anyone have an idea?
Ken Sheridan - 29 Jul 2006 00:07 GMT > I have a complex query with multiple rows and columns of criteria that > I am modifying for a new issue for my client. Originally I had a [quoted text clipped - 45 lines] > > Does anyone have an idea? Ken Sheridan - 29 Jul 2006 00:12 GMT Sorry about the blank post.
The IN operator cannot be used with parameters. Take a look at the following link for how it can be simulated, however:
http://support.microsoft.com/kb/100131/en-us
Ken Sheridan Stafford, England
> I have a complex query with multiple rows and columns of criteria that > I am modifying for a new issue for my client. Originally I had a [quoted text clipped - 45 lines] > > Does anyone have an idea? JB - 29 Jul 2006 18:33 GMT I decided to test out using the ID in the query instead of the text string.
Here is what the form code looks like: If Me.chkOldNetwork Then Me.txtNetworks = "20,21,22,23,24,25" Else Me.txtNetworks = "20,21,22,23,147" End If
This is what it looks like when using Debug.Print in the immediate window: ?"IN (" & [Forms]![frmWeeklyBroadcastSchedule]![txtNetworks] & ")" IN (20,21,22,23,147)
This seems right to me and like it should work but it does not. I get an error 3464 data type mismatch. If I manually copy what the Immediate
window generates into the query criteria column, it works fine. When I run the form and do it dynamically, it generates the 3464 error as soon
as it tries to build the query.
Any more ideas? Now I am stumped.
Ken Sheridan - 30 Jul 2006 15:35 GMT Concatenating the value list into a string expression will work if you build the SQL statement in code and then set the RecordSource of a form or report to the SQL statement or open a recordset based on it. It won't work in a saved query, however; that requires literal values in the IN operator's value list. You have to use one of the methods described in the MSKB article to which I gave you the link. I usually use the InParam and GetToken functions for this. Its quite simple to implement; you just put the following functions in a standard module in the database (these might differ slightly from those currently in the article as they are ones I've been using for years)
'''''''''Module Begins''''''''''' Option Compare Database Option Explicit ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Function GetToken(stLn, stDelim)
Dim iDelim As Integer, stToken As String iDelim = InStr(1, stLn, stDelim) If (iDelim <> 0) Then stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1))) stLn = Mid$(stLn, iDelim + 1) Else stToken = LTrim$(RTrim$(Mid$(stLn, 1))) stLn = "" End If GetToken = stToken
End Function ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Function InParam(fld, Param) As Boolean
Dim stToken As String If IsNull(fld) Then fld = "" Do While (Len(Param) > 0) stToken = GetToken(Param, ",") If stToken = LTrim$(RTrim$(fld)) Then InParam = TRUE Exit Function Else InParam = FALSE End If Loop End Function '''''''''Module Ends'''''''''''
You then call the InParam function in the query by passing the value of the field and a reference to the parameter into it. In your case the latter is the txtNetworks control on your form, so you'd call the function in the query along these lines:
SELECT * FROM YourTable WHERE InParam(Networks,Forms!frmWeeklyBroadcastSchedule!txtNetworks);
One thing to note here is that the comma delimited value list in the control does not need text values to be delimited with quotes as would be the case if you were using the IN operator itself with a list of text values.
Ken Sheridan Stafford, England
> I decided to test out using the ID in the query instead of the text > string. [quoted text clipped - 20 lines] > > Any more ideas? Now I am stumped. JB - 02 Aug 2006 00:08 GMT Thank you Ken for your detailed help. I appreciate it very much. I was able to create a simple solution to my problem.
I used the value in the check box to create a simple query from a separate table that grabs the network names I need. There are only two variations. That query is linked to my complex query based on a link in the network names.
If Me.chkOldNetwork Then strSQL = "SELECT tblNetworks_HTMLSort.NetworkName " & _ "FROM tblNetworks_HTMLSort " & _ "WHERE (((tblNetworks_HTMLSort.NetworkName) In (""abc"",""cbs"",""nbc"",""fox"",""wb"",""upn"")));" Else strSQL = "SELECT tblNetworks_HTMLSort.NetworkName " & _ "FROM tblNetworks_HTMLSort " & _ "WHERE (((tblNetworks_HTMLSort.NetworkName) In (""abc"",""cbs"",""nbc"",""fox"",""cw"")));" End If
Set db = CurrentDb()
'Delete existing query with prior SQl statement DoCmd.RunSQL "DROP TABLE qryHTMLNetworkList"
'Recreate query with new SQL statement Set qdf = db.CreateQueryDef("qryHTMLNetworkList", strSQL) qdf.Properties.Append qdf.CreateProperty("Description", dbText, _ "Dynamically built network data source for HTML reports. " & _ "Generated from data selected on forms for HTML reports that use Broadcast Network channels .")
DoCmd.OpenQuery ("qryWeeklyBroadcastSchedule") Me.Visible = False
' Reporting Code Call funWeekly_Export
DoCmd.Close acForm, "frmWeeklyBroadcastSchedule" db.Close
This method was simple and now I can use easily for my other query/report combinations that will be affected.
Ken Sheridan - 02 Aug 2006 12:06 GMT You could cut out one stage in the routine by not deleting and recreating the original query, but just changing its SQL property:
qdf.SQL = strSQL
BTW when doing this sort of thing it makes it even more important to regularly compact the database.
Ken Sheridan Stafford, England
> Thank you Ken for your detailed help. I appreciate it very much. I was > able to create a simple solution to my problem. [quoted text clipped - 39 lines] > This method was simple and now I can use easily for my other > query/report combinations that will be affected. bigpalooka - 13 Nov 2006 18:21 GMT Use Single quotes instead of Double quotes in an "IN" list: IN ('a','b','c') It saves a lot of headbanging.
> Thank you Ken for your detailed help. I appreciate it very much. I was > able to create a simple solution to my problem. [quoted text clipped - 39 lines] > This method was simple and now I can use easily for my other > query/report combinations that will be affected.
|
|
|