Dynamically getting IN field list from text box on form

[JB]

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?

 

[Guest]

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?

 

[Guest]

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

 

[JB]

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.

 

[Guest]

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.

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.

 

[JB]

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.

 

[Guest]

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

 

[Guest]

Use Single quotes instead of Double quotes in an "IN" list:
IN ('a','b','c')
It saves a lot of headbanging.