Query criteria pulling from a form

[Brandon]

I have a query that I am needing to pull certain data from a table. I have
been able to get this query to work if I plug in the criteria manually that I
am needing to filter. However, when I setup the criteria to pull it from a
text box on a form then the query returns no results, even though the value
that is in the text box is the same criteria that I typed in manually and it
worked. Can anyone help me figure this one out? The sql statement is below.

*DOES NOT WORK*

SELECT Security.Username, Security.States
FROM Security
GROUP BY Security.Username, Security.States
HAVING (((Security.States) Like "*[forms]![Form1]![Text27]*"));

*WORKS*
SELECT Security.Username, Security.States
FROM Security
GROUP BY Security.Username, Security.States
HAVING (((Security.States) Like "*OK*"));


Thanks,
Brandon

 

[Andy Hull]

Hi Brandon

Change the HAVING clause slightly to...

HAVING (((Security.States) Like "*" & [forms]![Form1]![Text27] & "*"))

Regards

Andy Hull

 

[John Spencer]

You need to break out the reference to the form control and then concatenate
that with the wildcards. See below

SELECT Security.Username, Security.States
FROM Security
GROUP BY Security.Username, Security.States
HAVING (((Security.States) Like "*" & [forms]![Form1]![Text27] & "*"));

Second for efficiency, I would rewrite the query as a Distinct query and
drop the group by

SELECT DISTINCT Security.Username, Security.States
FROM Security
WHERE Security.States Like "*" & [forms]![Form1]![Text27] & "*"

If you really need the Totals line for some added information, you can still
make the query more efficient by changing the Having clause to a WHERE
clause.

SELECT Security.Username, Security.States
FROM Security
WHERE Security.States Like "*" & [forms]![Form1]![Text27] & "*"
GROUP BY Security.Username, Security.States

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michel Walsh]

and you can change it for a WHERE clause:



SELECT Security.Username, Security.States
FROM Security
WHERE States Like "*" & [forms]![Form1]![Text27] & "*"
GROUP BY Username, States



The WHERE clause is applied before the groups are made, while the HAVING
clause is applied after the groups are made. Since you can avoid making
extra groups for nothing (those where states name is NOT LIKE the pattern
you specify) if you applied the criteria in the WHERE clause. Although the
difference in speed is probably not noticeable, given the actual condition
cannot use the index on field States, even if there is one index on that
field.



Vanderghast, Access MVP

Hi Brandon

Change the HAVING clause slightly to...

HAVING (((Security.States) Like "*" & [forms]![Form1]![Text27] & "*"))

Regards

Andy Hull

I have a query that I am needing to pull certain data from a table. I
have
been able to get this query to work if I plug in the criteria manually
that I
am needing to filter. However, when I setup the criteria to pull it from
a
text box on a form then the query returns no results, even though the
value
that is in the text box is the same criteria that I typed in manually and
it
worked. Can anyone help me figure this one out? The sql statement is
below.

*DOES NOT WORK*

SELECT Security.Username, Security.States
FROM Security
GROUP BY Security.Username, Security.States
HAVING (((Security.States) Like "*[forms]![Form1]![Text27]*"));

*WORKS*
SELECT Security.Username, Security.States
FROM Security
GROUP BY Security.Username, Security.States
HAVING (((Security.States) Like "*OK*"));


Thanks,
Brandon