LIKE Operator used with a form

[Guest]

I'm attempting to use the LIKE operator with a combo box entry. I want the
user to be able to choose a value from the domain and return all rows that
have that value as the leading character(s) in the selected column. The query
goes like this: SELECT collist FROM tablist WHERE joinlist GROUP BY collist
HAVING tr.treat_cd LIKE "[forms]![frmSwitchboard].[cboSelTreatment]*".

The query returns no values. If I substitute a value for the form entry, the
query returns the correct set. Am I allowed to use the LIKE operator in this
manner? Thank you in advance.

 

[John Vinson]

I'm attempting to use the LIKE operator with a combo box entry. I want the
user to be able to choose a value from the domain and return all rows that
have that value as the leading character(s) in the selected column. The query
goes like this: SELECT collist FROM tablist WHERE joinlist GROUP BY collist
HAVING tr.treat_cd LIKE "[forms]![frmSwitchboard].[cboSelTreatment]*".

The query returns no values. If I substitute a value for the form entry, the
query returns the correct set. Am I allowed to use the LIKE operator in this
manner? Thank you in advance.

Two suggestions: use the WHERE clause rather than the HAVING clause,
which is applied *after* all the grouping - or, better yet, just use
DISTINCT; and concatenate the asterisk:

SELECT DISTINCT collist FROM tablist WHERE joinlist AND tr.treat_cd
LIKE [forms]![frmSwitchboard].[cboSelTreatment] & "*";

Not sure what joinlist is though, and it would appear to be invalid
SQL.

John W. Vinson[MVP]

 

[Guest]

Thank you, John. I only just had the opportunity to try your suggestion -
which worked well, by the way.

I agree that in this instance filtering rows with the WHERE clause is more
efficient SQL than filtering them with the HAVING clause. It appears to be
the default of ACCESS to put filters that derive from forms into the HAVING
clause. Perhaps that's because there are instances where the filters need to
be applied to the grouped sets, so it just defaults that way. In my case,
however, I may just move the filters (there are quite a few of them) into the
WHERE clause.

The term 'joinlist,' by the way, is just a narrative placeholder for the
list of join conditions, just as 'collist' is a place holder for the list of
columns and 'tablist' for the list of joined tables.
- David

I'm attempting to use the LIKE operator with a combo box entry. I want the
user to be able to choose a value from the domain and return all rows that
have that value as the leading character(s) in the selected column. The query
goes like this: SELECT collist FROM tablist WHERE joinlist GROUP BY collist
HAVING tr.treat_cd LIKE "[forms]![frmSwitchboard].[cboSelTreatment]*".

The query returns no values. If I substitute a value for the form entry, the
query returns the correct set. Am I allowed to use the LIKE operator in this
manner? Thank you in advance.

Two suggestions: use the WHERE clause rather than the HAVING clause,
which is applied *after* all the grouping - or, better yet, just use
DISTINCT; and concatenate the asterisk:

SELECT DISTINCT collist FROM tablist WHERE joinlist AND tr.treat_cd
LIKE [forms]![frmSwitchboard].[cboSelTreatment] & "*";

Not sure what joinlist is though, and it would appear to be invalid
SQL.

John W. Vinson[MVP]

 

[John Vinson]

Thank you, John. I only just had the opportunity to try your suggestion -
which worked well, by the way.

I agree that in this instance filtering rows with the WHERE clause is more
efficient SQL than filtering them with the HAVING clause. It appears to be
the default of ACCESS to put filters that derive from forms into the HAVING
clause. Perhaps that's because there are instances where the filters need to
be applied to the grouped sets, so it just defaults that way. In my case,
however, I may just move the filters (there are quite a few of them) into the
WHERE clause.

Well... it's actually based on the way the query grid sets up a Totals
query. By default any field you add gets a "Group By" as the totals
operator; for a field to be used (only) as a criterion you need to use
the "Where" totals pseudo-operator (or, of course, just build the
query in SQL view in the first place). A criterion on a Group By field
gets put into the HAVING clause; you need to use Where (and turn off
the display of the field) to include it in the WHERE clause.

The term 'joinlist,' by the way, is just a narrative placeholder for the
list of join conditions, just as 'collist' is a place holder for the list of
columns and 'tablist' for the list of joined tables.

Thanks... should have figured that out, but just hadn't seen that
convention.

John W. Vinson[MVP]