Programatic In() statement

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

Is it possible to use the text value in a field on a form to be the in() part
of a query. For example, if the field had C28, C47 could the query say
In(C28,C47)?
 
It is more likely to be the operator LIKE


WHERE fieldName LIKE "*C28*" OR fieldName LIKE "*C47*"


As it is, right now, if the field have the value "C28, C29"

the record will be picked up, but it will also be picked up with "TC28,
C29" though.



Vanderghast, Access MVP
 
So, if field1 has the value "C28, C47", you need to keep a record if

field2 IN("C28", "C47") ?


Can then use

field1 LIKE "*" & field2 & "*"



(note the position of field1 and field2 are not usual, somehow)


or, if you want to be safe, adding delimiters:


(" " & field1 & "," ) LIKE ( "*[, ]" & field2 & "[, ]*" )



Vanderghast, Access MVP



Ryan said:
I need an IN statement, not a Like Or.
Click to expand...
 
I am using checkboxes to build a string value that
([Forms]![C28FILES_DOCTR]![LocationFilter]) will use. Here is what I have
so far.

PARAMETERS [Forms]![C28FILES_DOCTR]![LocationFilter] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Total Of BalDue]
FROM AllCharges
WHERE (((AllCharges.DB) In ([Forms]![C28FILES_DOCTR]![LocationFilter])))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

If I only pass one value, for example C28, in
In([Forms]![C28FILES_DOCTR]![LocationFilter])
then it works fine. However, if I put C28,C47 it returns no results. I
have tried to add the In statement in the
PARAMETERS [Forms]![C28FILES_DOCTR]![LocationFilter] Text ( 255 );
but it makes this
PARAMETERS [In[Forms]![C28FILES_DOCTR]![LocationFilter] Text ( 255 )];
and tells me invalid bracketing. If I manuall type in two values like this
WHERE (((AllCharges.DB) In (C28,C47) it works fine. All I want to do is be
able to pass in the In() from another form. Is this not possible?


Michel Walsh said:
So, if field1 has the value "C28, C47", you need to keep a record if

field2 IN("C28", "C47") ?


Can then use

field1 LIKE "*" & field2 & "*"



(note the position of field1 and field2 are not usual, somehow)


or, if you want to be safe, adding delimiters:


(" " & field1 & "," ) LIKE ( "*[, ]" & field2 & "[, ]*" )



Vanderghast, Access MVP
Click to expand...
 
Ryan said:
Is it possible to use the text value in a field on a form to be the in() part
of a query. For example, if the field had C28, C47 could the query say
In(C28,C47)?
Click to expand...


No, you can not parameterize anything that includes SQL
syntax (e.g. the commas in the list).

OTOH, with a Jet database, you can use the InStr function to
accomplish the same kind of thing.

Use a calulated field similar to:

InStr("," & Forms!theform.thetextbox & ",", "," & thefield
& ",")

with the criteria:
 
InStrTest: InStr("," & [db] & ",","," &
[Forms]![C28FILES_DOCTR]![LocationFilter] & ",")
This still only works to pass one parameter in. I need to pass in one or
more parameters. I cant believe that you can manualy add something like,
In(C28,C47,C52), but you cant pass in the parameters. Is there ANY way to
pass in multiple parameters?
 
Ryan said:
InStrTest: InStr("," & [db] & ",","," &
[Forms]![C28FILES_DOCTR]![LocationFilter] & ",")
This still only works to pass one parameter in. I need to pass in one or
more parameters.
Click to expand...

You reversed the arguments. It should be:
InStr("," & [Forms]![C28FILES_DOCTR]![LocationFilter] & ",",
"," & [db] & ",")

The list items must be separated by only a comma. Using
comma - space will not work.

Michel's Like expression is the equivalent without using a
VBA function.

I cant believe that you can manualy add something like,
In(C28,C47,C52), but you cant pass in the parameters. Is there ANY way to
pass in multiple parameters?
Click to expand...

Review the first sentence in my earlier reply.

Multiple parameters are not a problem, but you want to do it
with one parameter.
 
YEAH, your my hero Marshall!!! After hows of testing your code, I found that
you have to right click in design view of the query, click parameters, and
add a new parameter. Here is my complete code if it will help anyone else.
Thank you SO much Marshall.

PARAMETERS [Forms]!C28FILES_DOCTR.[LocationFilter] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Total Of BalDue]
FROM AllCharges
WHERE (((InStr("," & [Forms]![C28FILES_DOCTR].[LocationFilter] & ",","," &
[db] & ","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

Marshall Barton said:
Ryan said:
InStrTest: InStr("," & [db] & ",","," &
[Forms]![C28FILES_DOCTR]![LocationFilter] & ",")
This still only works to pass one parameter in. I need to pass in one or
more parameters.
Click to expand...

You reversed the arguments. It should be:
InStr("," & [Forms]![C28FILES_DOCTR]![LocationFilter] & ",",
"," & [db] & ",")

The list items must be separated by only a comma. Using
comma - space will not work.

Michel's Like expression is the equivalent without using a
VBA function.

I cant believe that you can manualy add something like,
In(C28,C47,C52), but you cant pass in the parameters. Is there ANY way to
pass in multiple parameters?
Click to expand...

Review the first sentence in my earlier reply.

Multiple parameters are not a problem, but you want to do it
with one parameter.
Click to expand...
 
Change field1 for the name of your parameter.


Vanderghast, Access MVP



Ryan said:
I am using checkboxes to build a string value that
([Forms]![C28FILES_DOCTR]![LocationFilter]) will use. Here is what I have
so far.

PARAMETERS [Forms]![C28FILES_DOCTR]![LocationFilter] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Total Of BalDue]
FROM AllCharges
WHERE (((AllCharges.DB) In ([Forms]![C28FILES_DOCTR]![LocationFilter])))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

If I only pass one value, for example C28, in
In([Forms]![C28FILES_DOCTR]![LocationFilter])
then it works fine. However, if I put C28,C47 it returns no results. I
have tried to add the In statement in the
PARAMETERS [Forms]![C28FILES_DOCTR]![LocationFilter] Text ( 255 );
but it makes this
PARAMETERS [In[Forms]![C28FILES_DOCTR]![LocationFilter] Text ( 255 )];
and tells me invalid bracketing. If I manuall type in two values like
this
WHERE (((AllCharges.DB) In (C28,C47) it works fine. All I want to do is
be
able to pass in the In() from another form. Is this not possible?


Michel Walsh said:
So, if field1 has the value "C28, C47", you need to keep a record if

field2 IN("C28", "C47") ?


Can then use

field1 LIKE "*" & field2 & "*"



(note the position of field1 and field2 are not usual, somehow)


or, if you want to be safe, adding delimiters:


(" " & field1 & "," ) LIKE ( "*[, ]" & field2 & "[, ]*" )



Vanderghast, Access MVP



Ryan said:
I need an IN statement, not a Like Or.
--
Please remember to mark this post as answered if this solves your
problem.


:

It is more likely to be the operator LIKE


WHERE fieldName LIKE "*C28*" OR fieldName LIKE "*C47*"


As it is, right now, if the field have the value "C28, C29"

the record will be picked up, but it will also be picked up with
"TC28,
C29" though.



Vanderghast, Access MVP


Is it possible to use the text value in a field on a form to be the
in()
part
of a query. For example, if the field had C28, C47 could the query
say
In(C28,C47)?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top