Parameter In Operator

[Ronald Dodge]

I'm attempting to use a parameter within the In Operator of the WHERE clause
such as the following line:

AND (tblHrsHist.fldstrWrkCode IN([LIST OF WORK CODES])
OR [LIST OF WORK CODES] Is Null)

If I type in a value that is actually in the table, it does not pull in the
data, rather if that value is surrounded with double quotes, single quotes
or no quotes. At this point of time, I just typed in a single value just to
see if it would work, and it seems to be failing. However, if I leave this
parameter as blank (Null), it works fine.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Michel Walsh]

You cannot directly use a parameter for a list, as you shown.

Among other solutions:

- make a temporary table (one field) filled with the values for the
list (one record per value), and use an inner join:

... FROM HrsHist INNER JOIN temp ON HrsHist.WrkCode = temp.fieldName


- slower, but without using a table:

WHERE ("," & parameterValueList & ",") LIKE ("*[, ]" & WrkCode
& "[, ]*")


with parameterValueList a string like "a,b,c"
to return rows with wrkCode IN("a", "b", "c" )



Hoping it may help,
Vanderghast, Access MVP

 

[Ronald Dodge]

Thank you for the update. Guess I will have to go ahead and create my
paramter dialog form, as I had that planned to be done, but didn't get
around to doing that, as I wanted to have the query itself working before
putting in the form.

Now one question. If the user doesn't put in a single value for the work
code, then how would you have this handled in the SQL statement given the
table method is in the "FROM" clause rather than in the "WHERE" clause?

With a dialog form, I certainly can have it programmed to have all of the
values put into the temp table, but without such dialog form, then what?

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

You cannot directly use a parameter for a list, as you shown.

Among other solutions:

- make a temporary table (one field) filled with the values for the
list (one record per value), and use an inner join:

... FROM HrsHist INNER JOIN temp ON HrsHist.WrkCode = temp.fieldName


- slower, but without using a table:

WHERE ("," & parameterValueList & ",") LIKE ("*[, ]" & WrkCode
& "[, ]*")


with parameterValueList a string like "a,b,c"
to return rows with wrkCode IN("a", "b", "c" )



Hoping it may help,
Vanderghast, Access MVP

I'm attempting to use a parameter within the In Operator of the WHERE
clause such as the following line:

AND (tblHrsHist.fldstrWrkCode IN([LIST OF WORK CODES])
OR [LIST OF WORK CODES] Is Null)

If I type in a value that is actually in the table, it does not pull in
the data, rather if that value is surrounded with double quotes, single
quotes or no quotes. At this point of time, I just typed in a single
value just to see if it would work, and it seems to be failing. However,
if I leave this parameter as blank (Null), it works fine.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Michel Walsh]

With the INNER join, you will get nothing (no records returned). But you can
test to see if the temporary table has no record in it, and if so, return
all the rows of the other table (through appropriate VBA code).

Same thing with the proposed WHERE clause, except if there are NULL values
in the field you used. In that case,

",," LIKE "*[, ][, ]*"

evaluates to true, and the rows (with a NULL value) would be returned.



Hoping it may help,
Vanderghast, Access MVP

Thank you for the update. Guess I will have to go ahead and create my
paramter dialog form, as I had that planned to be done, but didn't get
around to doing that, as I wanted to have the query itself working before
putting in the form.

Now one question. If the user doesn't put in a single value for the work
code, then how would you have this handled in the SQL statement given the
table method is in the "FROM" clause rather than in the "WHERE" clause?

With a dialog form, I certainly can have it programmed to have all of the
values put into the temp table, but without such dialog form, then what?

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

You cannot directly use a parameter for a list, as you shown.

Among other solutions:

- make a temporary table (one field) filled with the values for the
list (one record per value), and use an inner join:

... FROM HrsHist INNER JOIN temp ON HrsHist.WrkCode = temp.fieldName


- slower, but without using a table:

WHERE ("," & parameterValueList & ",") LIKE ("*[, ]" &
WrkCode & "[, ]*")


with parameterValueList a string like "a,b,c"
to return rows with wrkCode IN("a", "b", "c" )



Hoping it may help,
Vanderghast, Access MVP

I'm attempting to use a parameter within the In Operator of the WHERE
clause such as the following line:

AND (tblHrsHist.fldstrWrkCode IN([LIST OF WORK CODES])
OR [LIST OF WORK CODES] Is Null)

If I type in a value that is actually in the table, it does not pull in
the data, rather if that value is surrounded with double quotes, single
quotes or no quotes. At this point of time, I just typed in a single
value just to see if it would work, and it seems to be failing.
However, if I leave this parameter as blank (Null), it works fine.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000