Pass 'Not In' data to query

R

RW

I have a form for running queries that has a field called Exclude. Users need
to be able to type in numbers separated by commas that they do not want to be
included in the query results. In the query, I added the criteria Not In
(Forms!FormName!Exclude). However, the data from the Form appears to be sent
as a literal - not as multiple numbers. In other words, if they enter 250,251
then two hundred fifty thousand and two hundred fifty one is passed instead
of 250 and 251. The field in the table is a number field.

I also tried to pass the entire criteria as Not In (250,251) and it is seen
as text and gives a Data Mismatch since the field is a number.

How can I get this to work?
 
M

Marshall Barton

RW said:
I have a form for running queries that has a field called Exclude. Users need
to be able to type in numbers separated by commas that they do not want to be
included in the query results. In the query, I added the criteria Not In
(Forms!FormName!Exclude). However, the data from the Form appears to be sent
as a literal - not as multiple numbers. In other words, if they enter 250,251
then two hundred fifty thousand and two hundred fifty one is passed instead
of 250 and 251. The field in the table is a number field.

I also tried to pass the entire criteria as Not In (250,251) and it is seen
as text and gives a Data Mismatch since the field is a number.
Click to expand...


Your understanding of what's happening is correct. Query
parameters are a single value, not a syntatic portion of an
SQL statement. I.e. a different approach is needed.

There are several ways to get where you want to go. Unless
you are using code to construct the query's SQL statement, I
think the easiest way is to use a calculated field in the
query (Show unchecked) with the expression:

InStr("," & Forms!FormName.Exclude & ",", "," & numberfield
& ",")

then set that field's criteria to:
=0
 
R

RW

Thanks! That seems to work well. I do have another question though. I looked
at the Help on InStr and it only shows 3 commas.

InStr([Start,]string1,string2[,compare])

Where everthing in [] is optional. I would assume the [Start,] is a typo in
help and should have been [Start],
since it would seem the comma after start would be necessary so InStr would
know that the string after the first comma would be what it considers
string1. The , after string 2 would be optional if there isn't a compare.

However in your code it seems as if it reads:
InStr(,data in form field,,table number field,)

This would seem to correlate to:
data in form field = string1
table number field = compare since it is after the 3rd comma

Thanks for helping me understand this.
 
D

David Cox

No, it is more cunning than that.

Help syntax is right. Instr uses the first argument as a number if it is a
number and as a string if it is a string, so the leading comma to show the
first optional argument is missing is not required.

if we want to exclude 59 we do not want to exclude 2598 just because it has
59 inside it

so RW is adding leading commas so he is looking for ",59 " and he will not
find it in ",2598"

He has done this before.


RW said:
Thanks! That seems to work well. I do have another question though. I
looked
at the Help on InStr and it only shows 3 commas.

InStr([Start,]string1,string2[,compare])

Where everthing in [] is optional. I would assume the [Start,] is a typo
in
help and should have been [Start],
since it would seem the comma after start would be necessary so InStr
would
know that the string after the first comma would be what it considers
string1. The , after string 2 would be optional if there isn't a compare.

However in your code it seems as if it reads:
InStr(,data in form field,,table number field,)

This would seem to correlate to:
data in form field = string1
table number field = compare since it is after the 3rd comma

Thanks for helping me understand this.

Marshall Barton said:
Your understanding of what's happening is correct. Query
parameters are a single value, not a syntatic portion of an
SQL statement. I.e. a different approach is needed.

There are several ways to get where you want to go. Unless
you are using code to construct the query's SQL statement, I
think the easiest way is to use a calculated field in the
query (Show unchecked) with the expression:

InStr("," & Forms!FormName.Exclude & ",", "," & numberfield
& ",")

then set that field's criteria to:
=0
Click to expand...
Click to expand...
 
R

RW

I understand it now. THANKS!

David Cox said:
No, it is more cunning than that.

Help syntax is right. Instr uses the first argument as a number if it is a
number and as a string if it is a string, so the leading comma to show the
first optional argument is missing is not required.

if we want to exclude 59 we do not want to exclude 2598 just because it has
59 inside it

so RW is adding leading commas so he is looking for ",59 " and he will not
find it in ",2598"

He has done this before.


RW said:
Thanks! That seems to work well. I do have another question though. I
looked
at the Help on InStr and it only shows 3 commas.

InStr([Start,]string1,string2[,compare])

Where everthing in [] is optional. I would assume the [Start,] is a typo
in
help and should have been [Start],
since it would seem the comma after start would be necessary so InStr
would
know that the string after the first comma would be what it considers
string1. The , after string 2 would be optional if there isn't a compare.

However in your code it seems as if it reads:
InStr(,data in form field,,table number field,)

This would seem to correlate to:
data in form field = string1
table number field = compare since it is after the 3rd comma

Thanks for helping me understand this.

Marshall Barton said:
RW wrote:

I have a form for running queries that has a field called Exclude. Users
need
to be able to type in numbers separated by commas that they do not want
to be
included in the query results. In the query, I added the criteria Not In
(Forms!FormName!Exclude). However, the data from the Form appears to be
sent
as a literal - not as multiple numbers. In other words, if they enter
250,251
then two hundred fifty thousand and two hundred fifty one is passed
instead
of 250 and 251. The field in the table is a number field.

I also tried to pass the entire criteria as Not In (250,251) and it is
seen
as text and gives a Data Mismatch since the field is a number.


Your understanding of what's happening is correct. Query
parameters are a single value, not a syntatic portion of an
SQL statement. I.e. a different approach is needed.

There are several ways to get where you want to go. Unless
you are using code to construct the query's SQL statement, I
think the easiest way is to use a calculated field in the
query (Show unchecked) with the expression:

InStr("," & Forms!FormName.Exclude & ",", "," & numberfield
& ",")

then set that field's criteria to:
=0
Click to expand...
Click to expand...
Click to expand...
 
Top