Using field name for IN text string in query criteria

[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?

 

[John Nurick]

Hi Jennifer,

To get this
"abc", "def", "ghi","jkl"
use
Me.txtNetworks = ""abc", "def", "ghi","jkl""

Or - easier to read - take advantage of the fact that the standard SQL
literal string delimiter is actually the apostrophe:

Me.txtNetworks = "'abc', 'def', 'ghi', 'jkl'"

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?

 

[JB]

Thanks for you rresponse and efforts to help. This seems like such a
simple issue but it is turning out to be tricky. Should I just change
the query to pull the NetworkID's instead? At least I am not messing
with strings that way. I would like to solve this for future reference
but maybe I am spending too much time on it and can just try a
different approach.

To get this
"abc", "def", "ghi","jkl"
use
Me.txtNetworks = ""abc", "def", "ghi","jkl""

I tried this before and the compiler does not accept it. When it gets
to abc, it highlights it and says "expected end of statement."

Or - easier to read - take advantage of the fact that the standard SQL
literal string delimiter is actually the apostrophe:

Me.txtNetworks = "'abc', 'def', 'ghi', 'jkl'"

I have tried this before also. The compiler accepts it but no records
get selected by the query. It returns an empty recordset.

 

[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.

 

[John Nurick]

What is the code you're using to "build the query"?

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.

 

[John Nurick]

I tried this before and the compiler does not accept it. When it gets
to abc, it highlights it and says "expected end of statement."

My brain wasn't in gear. I meant
Me.txtNetworks = """abc"", ""def"", ""ghi"",""jkl"""

I have tried this before also. The compiler accepts it but no records
get selected by the query. It returns an empty recordset.

That suggests there's something wrong with the query.

 

[JB]

I appreciate your persistence on this issue. I have gone back to
testing your suggestions.


My form code:
If Me.chkOldNetwork Then
Me.txtNetworks =
"""abc"",""cbs"",""nbc"",""fox"",""wb"",""upn"""
Else
Me.txtNetworks = """abc"",""cbs"",""nbc"",""fox"",""cw"""
End If


The immediate window evaluates my query criteria as the following:

?"IN (" & [Forms]![frmWeeklyBroadcastSchedule]![txtNetworks] & ")"
IN ("abc","cbs","nbc","fox","cw")

This all looks good but still when the query runs, I get no records
from this condition.

I understand what you are saying about the single quote example and
that it should work. This example should also work. I have tested this
before and I still get no records. If I evaluate the criteria
expressions in the Immediate window, they look fine. I can then copy
those text values into the criteria as another test and that works
fine. Only when I run the form to call the query and build everything
dynamically does it return no records. Uggh.

I have tried adding quotes and # symbols around my criteria date
expressions, although they worked before without this. I have converted
my values in the text boxes to dates using CDate as an additional
check, although that also worked before without this step. Nothing
helps resolve the error "3464 data type mismatch in criteria
expression" I get.

The SQL string is quite long and hard to read but here it is. This
first SQL example is my original query that has always worked. This is
with the network names coded directly into the query and no dynamic
changes being made by the form.

SELECT tblEpisodeAirdates.EpisodeAirdate,
Format([episodeairdate],"dddd") AS DayName,
Format([episodeairdate],"ddd") AS AbbrDayName,
MonthName(DatePart("m",[EpisodeAirdate])) AS [Month],
DatePart("d",[EpisodeAirdate]) AS [Day],
Right(Year([EpisodeAirdate]),4) AS [Year], tblNetworks.NetworkAbbr,
CDate(IIf(IsNull([SpecialAirtimeBegin]),tblStandardAirtimes!StandardAirtimeBegin,tblEpisodeAirdates!SpecialAirtimeBegin))
AS StartTime,
CDate(IIf(IsNull([SpecialAirtimeEnd]),tblStandardAirtimes!StandardAirtimeEnd,tblEpisodeAirdates!SpecialAirtimeend))
AS EndTime, tblPrograms.ProgramName, tblEpisodes.EpisodeName,
tblPrograms.WBShow, tblEpisodeAirdates.Confirmed,
tblEpisodeAirdates.Repeat, tblEpisodeAirdates.Special,
tblEpisodeAirdates.RepeatSpecial, tblEpisodeAirdates.SeasonPremiere,
tblEpisodeAirdates.TPPremiere, tblEpisodeAirdates.Debut,
tblEpisodeAirdates.DebutPreview, tblEpisodeAirdates.SeasonFinale,
tblEpisodeAirdates.SeriesFinale, IIf([repeat]=False,Null,"(R)") AS
strRepeat, IIf([special]=False,Null,"(S)") AS strSpecial,
IIf([repeatspecial]=False,Null,"(RS)") AS strRepeatSpecial,
IIf([SeasonPremiere]=False,Null,"(Season Premiere) ") AS
strSeasonPremiere, IIf([TPPremiere]=False,Null,"(T.P. Premiere) ") AS
strTPPremiere, IIf([Debut]=False,Null,"(Debut) ") AS strDebut,
IIf([DebutPreview]=False,Null,"(Debut/Preview) ") AS strDebutPreview,
IIf([SeasonFinale]=False,Null,"(Season Finale)") AS strSeasonFinale,
IIf([SeriesFinale]=False,Null,"(Series Finale) ") AS strSeriesFinale,
IIf(IsNull(tblEpisodes!GuestStarNames),Null,"Guest:" & [GuestStarNames]
& " ") AS strGuestStars, tblEpisodeAirdates.EpisodeAirdateNotes,
IIf([EpisodeName]="Unknown",Null,[EpisodeName] & " ") AS strEpisode,
UCase([ProgramName]) & " " & [strRepeat] & [strSpecial] &
[strRepeatSpecial] AS ProgramLine, [strEpisode] & [strSeasonPremiere] &
[strTPPremiere] & [strDebut] & [strDebutPreview] & [strSeasonFinale] &
[strSeriesFinale] & [strGuestStars] & [EpisodeAirdateNotes] AS
EpisodeLine, IIf((tlkpGenres!GenreNameID=8) Or
(tlkpGenres!GenreNameID=9),"Movie:" & tblEpisodes!EpisodeName & " (" &
[NetworkAbbr] & ")",[ProgramName] & " " & [strSeasonPremiere] &
[strTPPremiere] & [strDebut] & [strDebutPreview] & [strSeasonFinale] &
[strSeriesFinale] & "(" & [NetworkAbbr] & ")") AS HighlightLine,
tblEpisodeAirdates.CableHighlight, tlkpGenreNames.GenreNameID,
tlkpGenreNames.GenreName, tblEpisodeAirdates.BacktoBack,
tblBacktoBack.BacktoBackEndTime, tblEpisodeAirdates.PrintSpecialAirtime
INTO tblExport
FROM (tlkpGenreNames INNER JOIN tlkpGenres ON
tlkpGenreNames.GenreNameID = tlkpGenres.GenreNameID) INNER JOIN
(tblPrograms INNER JOIN ((tblProgramSeasons INNER JOIN (tblEpisodes
INNER JOIN (tblEpisodeAirdates LEFT JOIN tblBacktoBack ON
tblEpisodeAirdates.EpisodeAirdateID = tblBacktoBack.EpisodeAirdateID)
ON tblEpisodes.EpisodeID = tblEpisodeAirdates.EpisodeID) ON
tblProgramSeasons.ProgramSeasonID = tblEpisodes.ProgramSeasonID) INNER
JOIN (tblNetworks INNER JOIN (tblNetworkPrograms INNER JOIN
tblStandardAirtimes ON tblNetworkPrograms.NetworkProgramID =
tblStandardAirtimes.NetworkProgramID) ON tblNetworks.NetworkID =
tblNetworkPrograms.NetworkID) ON (tblStandardAirtimes.StandardAirtimeID
= tblEpisodeAirdates.StandardAirtimeID) AND
(tblProgramSeasons.ProgramSeasonID =
tblNetworkPrograms.ProgramSeasonID)) ON tblPrograms.ProgramID =
tblProgramSeasons.ProgramID) ON tlkpGenres.GenreID =
tblPrograms.GenreID
WHERE (((tblEpisodeAirdates.EpisodeAirdate) Between
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekBegin] And
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekEnd]) AND
((tblNetworks.NetworkAbbr)="abc" Or (tblNetworks.NetworkAbbr)="cbs" Or
(tblNetworks.NetworkAbbr)="nbc" Or (tblNetworks.NetworkAbbr)="fox" Or
(tblNetworks.NetworkAbbr)="wb" Or (tblNetworks.NetworkAbbr)="upn") AND
((tblNetworks.BroadcastNetwork)=Yes) AND
((tblNetworkPrograms.Cancelled)=No)) OR
(((tblEpisodeAirdates.EpisodeAirdate) Between
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekBegin] And
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekEnd]) AND
((tblEpisodeAirdates.CableHighlight)=Yes) AND
((tblNetworks.BroadcastNetwork)=No) AND
((tblNetworkPrograms.Cancelled)=No)) OR
(((tblEpisodeAirdates.EpisodeAirdate) Between
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekBegin] And
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekEnd] And
(tblEpisodeAirdates.EpisodeAirdate)<=[lastAirdate]) AND
((tblNetworks.NetworkAbbr)="abc" Or (tblNetworks.NetworkAbbr)="cbs" Or
(tblNetworks.NetworkAbbr)="nbc" Or (tblNetworks.NetworkAbbr)="fox" Or
(tblNetworks.NetworkAbbr)="wb" Or (tblNetworks.NetworkAbbr)="upn") AND
((tblNetworks.BroadcastNetwork)=Yes) AND
((tblNetworkPrograms.Cancelled)=Yes)) OR
(((tblEpisodeAirdates.EpisodeAirdate) Between
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekBegin] And
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekEnd] And
(tblEpisodeAirdates.EpisodeAirdate)<=[lastAirdate]) AND
((tblEpisodeAirdates.CableHighlight)=Yes) AND
((tblNetworks.BroadcastNetwork)=No) AND
((tblNetworkPrograms.Cancelled)=Yes))
ORDER BY tblEpisodeAirdates.EpisodeAirdate, tblNetworks.NetworkAbbr,
CDate(IIf(IsNull([SpecialAirtimeBegin]),tblStandardAirtimes!StandardAirtimeBegin,tblEpisodeAirdates!SpecialAirtimeBegin));

===============
Here is the current example where I have even added the quotes and #
signs around the Between phrase and I still get the 3464 error. I have
also tried using OR instead of IN and using the NetworkID's instead of
their Abbreviation Names. I have the same problem no matter which
example I try. I am so frustrated with this.

SELECT tblEpisodeAirdates.EpisodeAirdate,
Format([episodeairdate],"dddd") AS DayName,
Format([episodeairdate],"ddd") AS AbbrDayName,
MonthName(DatePart("m",[EpisodeAirdate])) AS [Month],
DatePart("d",[EpisodeAirdate]) AS [Day],
Right(Year([EpisodeAirdate]),4) AS [Year], tblNetworks.NetworkAbbr,
CDate(IIf(IsNull([SpecialAirtimeBegin]),tblStandardAirtimes!StandardAirtimeBegin,tblEpisodeAirdates!SpecialAirtimeBegin))
AS StartTime,
CDate(IIf(IsNull([SpecialAirtimeEnd]),tblStandardAirtimes!StandardAirtimeEnd,tblEpisodeAirdates!SpecialAirtimeend))
AS EndTime, tblPrograms.ProgramName, tblEpisodes.EpisodeName,
tblPrograms.WBShow, tblEpisodeAirdates.Confirmed,
tblEpisodeAirdates.Repeat, tblEpisodeAirdates.Special,
tblEpisodeAirdates.RepeatSpecial, tblEpisodeAirdates.SeasonPremiere,
tblEpisodeAirdates.TPPremiere, tblEpisodeAirdates.Debut,
tblEpisodeAirdates.DebutPreview, tblEpisodeAirdates.SeasonFinale,
tblEpisodeAirdates.SeriesFinale, IIf([repeat]=False,Null,"(R)") AS
strRepeat, IIf([special]=False,Null,"(S)") AS strSpecial,
IIf([repeatspecial]=False,Null,"(RS)") AS strRepeatSpecial,
IIf([SeasonPremiere]=False,Null,"(Season Premiere) ") AS
strSeasonPremiere, IIf([TPPremiere]=False,Null,"(T.P. Premiere) ") AS
strTPPremiere, IIf([Debut]=False,Null,"(Debut) ") AS strDebut,
IIf([DebutPreview]=False,Null,"(Debut/Preview) ") AS strDebutPreview,
IIf([SeasonFinale]=False,Null,"(Season Finale)") AS strSeasonFinale,
IIf([SeriesFinale]=False,Null,"(Series Finale) ") AS strSeriesFinale,
IIf(IsNull(tblEpisodes!GuestStarNames),Null,"Guest:" & [GuestStarNames]
& " ") AS strGuestStars, tblEpisodeAirdates.EpisodeAirdateNotes,
IIf([EpisodeName]="Unknown",Null,[EpisodeName] & " ") AS strEpisode,
UCase([ProgramName]) & " " & [strRepeat] & [strSpecial] &
[strRepeatSpecial] AS ProgramLine, [strEpisode] & [strSeasonPremiere] &
[strTPPremiere] & [strDebut] & [strDebutPreview] & [strSeasonFinale] &
[strSeriesFinale] & [strGuestStars] & [EpisodeAirdateNotes] AS
EpisodeLine, IIf((tlkpGenres!GenreNameID=8) Or
(tlkpGenres!GenreNameID=9),"Movie:" & tblEpisodes!EpisodeName & " (" &
[NetworkAbbr] & ")",[ProgramName] & " " & [strSeasonPremiere] &
[strTPPremiere] & [strDebut] & [strDebutPreview] & [strSeasonFinale] &
[strSeriesFinale] & "(" & [NetworkAbbr] & ")") AS HighlightLine,
tblEpisodeAirdates.CableHighlight, tlkpGenreNames.GenreNameID,
tlkpGenreNames.GenreName, tblEpisodeAirdates.BacktoBack,
tblBacktoBack.BacktoBackEndTime, tblEpisodeAirdates.PrintSpecialAirtime
INTO tblExport
FROM (tlkpGenreNames INNER JOIN tlkpGenres ON
tlkpGenreNames.GenreNameID = tlkpGenres.GenreNameID) INNER JOIN
(tblPrograms INNER JOIN ((tblProgramSeasons INNER JOIN (tblEpisodes
INNER JOIN (tblEpisodeAirdates LEFT JOIN tblBacktoBack ON
tblEpisodeAirdates.EpisodeAirdateID = tblBacktoBack.EpisodeAirdateID)
ON tblEpisodes.EpisodeID = tblEpisodeAirdates.EpisodeID) ON
tblProgramSeasons.ProgramSeasonID = tblEpisodes.ProgramSeasonID) INNER
JOIN (tblNetworks INNER JOIN (tblNetworkPrograms INNER JOIN
tblStandardAirtimes ON tblNetworkPrograms.NetworkProgramID =
tblStandardAirtimes.NetworkProgramID) ON tblNetworks.NetworkID =
tblNetworkPrograms.NetworkID) ON (tblStandardAirtimes.StandardAirtimeID
= tblEpisodeAirdates.StandardAirtimeID) AND
(tblProgramSeasons.ProgramSeasonID =
tblNetworkPrograms.ProgramSeasonID)) ON tblPrograms.ProgramID =
tblProgramSeasons.ProgramID) ON tlkpGenres.GenreID =
tblPrograms.GenreID
WHERE (((tblEpisodeAirdates.EpisodeAirdate)="Between #" &
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekBegin] & "# And #" &
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekEnd] & "#") AND
((tblNetworks.NetworkAbbr)="IN (" &
[Forms]![frmWeeklyBroadcastSchedule]![txtNetworks] & ")") AND
((tblNetworks.BroadcastNetwork)=Yes) AND
((tblNetworkPrograms.Cancelled)=No)) OR
(((tblEpisodeAirdates.EpisodeAirdate)="Between #" &
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekBegin] & "# And #" &
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekEnd] & "#") AND
((tblEpisodeAirdates.CableHighlight)=Yes) AND
((tblNetworks.BroadcastNetwork)=No) AND
((tblNetworkPrograms.Cancelled)=No)) OR
(((tblEpisodeAirdates.EpisodeAirdate)="Between #" &
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekBegin] & "# And #" &
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekEnd] & "# And <=#" &
[lastAirdate] & "#") AND ((tblNetworks.NetworkAbbr)="IN (" &
[Forms]![frmWeeklyBroadcastSchedule]![txtNetworks] & ")") AND
((tblNetworks.BroadcastNetwork)=Yes) AND
((tblNetworkPrograms.Cancelled)=Yes)) OR
(((tblEpisodeAirdates.EpisodeAirdate)="Between #" &
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekBegin] & "# And #" &
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekEnd] & "# And <=#" &
[lastAirdate] & "#") AND ((tblEpisodeAirdates.CableHighlight)=Yes) AND
((tblNetworks.BroadcastNetwork)=No) AND
((tblNetworkPrograms.Cancelled)=Yes))
ORDER BY tblEpisodeAirdates.EpisodeAirdate, tblNetworks.NetworkAbbr,
CDate(IIf(IsNull([SpecialAirtimeBegin]),tblStandardAirtimes!StandardAirtimeBegin,tblEpisodeAirdates!SpecialAirtimeBegin));

 

[John Nurick]

Hi Jennifer,

There are various problems with the WHERE clause in the second version.
Here's the first part of it, reformatted for clarity and with the Forms!
referenced abbreviated to F:

WHERE
(
(tblEpisodeAirdates.EpisodeAirdate =
"Between #" & F![txtWeekBegin] & "# And #" & F![txtWeekEnd] & "#")
AND (tblNetworks.NetworkAbbr = "IN (" & F![txtNetworks] & ")")
AND (tblNetworks.BroadcastNetwork = Yes)
AND (tblNetworkPrograms.Cancelled = No)
) OR (
(tblEpisodeAirdates.EpisodeAirdate =
"Between #" & F![txtWeekBegin] & "# And #" & F![txtWeekEnd] & "#")
AND (tblEpisodeAirdates.CableHighlight = Yes)
AND (tblNetworks.BroadcastNetwork = No)
AND (tblNetworkPrograms.Cancelled = No)
) OR (
...
)

1) The equals signs before, and quotes round, the BETWEEN and IN
expressions are wrong: you end up with something like
tblEpisodeAirdates.EpisodeAirdate = "Between ...
i.e. you're comparing EpisodeAirdate with the literal string
Between ...
which of course gives you a type mismatch error.

2) The rules that are applied when interpolating a parameter into an SQL
statement aren't quite the same as those for concatenating strings. (I
don't pretend to understand the details). But if you want to get dates
from a textbox on a form, don't try to concatenate them with #
delimiters. You can either just pass the unadorned date strings
... BETWEEN F![txtWeekBegin] AND F![txtWeekEnd] ...
or convert them explicitly
... BETWEEN CDate(F![txtWeekBegin]) ...
(remember that F! is an abbreviation and must be spelt out in the actual
SQL).

3) Similarly, you can't (as far as I know) interpolate a query parameter
into an IN expression.

So I think you'll have to build the SQL in your VBA code. This doesn't
mean starting from scratch. One possibility would be to sort out the
second version you posted as per (1) and (2) above and replace each
Forms! reference with a string parameter, e.g. replace
[Forms]![frmWeeklyBroadcastSchedule]![txtWeekBegin]
with
WeekBegin_

That way you'll be able to test the query, entering the parameter values
manually. The IN expression should work this way provided you only type
one value each time.

Then in your code you can do something like

Set DB = CurrentDB()

'get the sql from the stored query
strSQL = DB.QueryDefs("MyQuery").SQL

'concatenate parameter values
strSQL = Replace(strSQL, "WeekBegin_", _
"#" & Me.txtWeekBegin.Value & "#")
...

'execute the expanded statement
DB.Execute strSQL, dbFailOnError

 

[Michel Walsh]

Hi,


I am not sure if you got it working, but another alternative is to use the
operator LIKE:


WHERE ( "," & param & "," ) LIKE "*[ ,]" & fieldName & "[ ,]*"



As example, with param = "1,4,5", that is logically equivalent to"

fieldName IN(1, 4, 5)


If the fieldName datatype is text, param is still without the standard
delimiter (so, simpler to code and maintain) :

param="apple, peach, ananas"

against a fieldname with the value "strawberry", will lead to :


WHERE ",apple, peach, ananas," LIKE "*[ ,]straberry[ ,]*"

which is false, but would lead to something true for the value "peach".


Hoping it may help,
Vanderghast, Access MVP

 

[JB]

Thank you so much Jamie. Your solution was the simple one I hoped was
out there. Since I have to use this for three other query/report
combinations, I wanted something that was quick and easy. This idea did
the trick quite nicely.

Thanks also to everyone who stuck with this question to provide a
variety of solutions. I have learned a lot from this problem.

Here is the code on the form now. I have also linked qryNetworkList to
my main query so that the Network names have to match.

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


I have one remaining question. The line of code above
(qdf.Properties.Append ) works for me in other forms so that I can
dynamically create a query description. On this form, I get a run time
error '3367' object name already exists. I am not sure what part of the
line I need to adjust. It is not critical as I can comment it out if
necessary.

 

[JB]

Ok. This time I found a quick solution to the dynamic description
property.

db.QueryDefs("qryHTMLNetworkList").Properties("Description") =
"Message"

should be used in Access XP instead of :

qdf.Properties.Append qdf.CreateProperty("Description", dbText,
"Message")

This issue is now completely resolved. Thanks again everyone.