Query criteria from form...

[jpuckett]

Ok guys, give me the answer here. I have a form that IS successfully
passing criteria to a parameter query. However, I have one combo box on the
form that is giving me problems due to the contents. The following are
examples of statements that are entered, in their entirety in the value
fields of the combo box:

Like "*rollover*" and not like "*frontal*"
Like "*rear*" and like "*impact*"
Like "*side*" or like "*lateral*"
Like "*reclined*"

Each of the statements above are literally entered, just as they are above,
in the value field of the combo box.

The reason I've attempted to do it this way is probably obvious. I couldn't
figure out how to do it another way and accomodate for the varied number of
search terms, etc.

Basically the query simply returns no records. Does it have to do with the
punctuation in the field? Is it just impossible to pass this type of
statement in this way?

I know it would work if I was simply passing the search terms themselves and
having the query criteria entry be Like
"*[Forms]![frmCriteriaForm]![cboAccType]*" but what do I have to do to be
able to pass these types of parameters easily on the fly or at least in a
combo box. I know I'm just not doing it correctly but I'm stumped as to the
right method.

TIA

 

[Duane Hookom]

You can only reference single values to compare against. You can't provide
either multiple values or how to compare them. The "Like" would need to be
hard-coded in the query.

Is the query used as the record source for a form or report?

 

[Guest]

Or if the user is in fact typing the full query as 'Like "*this*" and Like
"*that*"' then you would need to parse the user entered data, looking for the
word(s) that you want to allow the user to search on, then if you come across
a word such as Like, take the data that follows it until the next recognized
operator (and) and pass that information to a Like query. And in this case
and the two. This "operation" is more of a VBA code parsing thing coupled
with the desired SQL information.

But then again, maybe Duane and I are barking up the wrong tree.

You can only reference single values to compare against. You can't provide
either multiple values or how to compare them. The "Like" would need to be
hard-coded in the query.

Is the query used as the record source for a form or report?

--
Duane Hookom
MS Access MVP

Ok guys, give me the answer here. I have a form that IS successfully
passing criteria to a parameter query. However, I have one combo box on
the form that is giving me problems due to the contents. The following
are examples of statements that are entered, in their entirety in the
value fields of the combo box:

Like "*rollover*" and not like "*frontal*"
Like "*rear*" and like "*impact*"
Like "*side*" or like "*lateral*"
Like "*reclined*"

Each of the statements above are literally entered, just as they are
above, in the value field of the combo box.

The reason I've attempted to do it this way is probably obvious. I
couldn't figure out how to do it another way and accomodate for the varied
number of search terms, etc.

Basically the query simply returns no records. Does it have to do with
the punctuation in the field? Is it just impossible to pass this type of
statement in this way?

I know it would work if I was simply passing the search terms themselves
and having the query criteria entry be Like
"*[Forms]![frmCriteriaForm]![cboAccType]*" but what do I have to do to be
able to pass these types of parameters easily on the fly or at least in a
combo box. I know I'm just not doing it correctly but I'm stumped as to
the right method.

TIA

 

[jpuckett]

Yes the query is the record source for a report. Is there any way to
accomplish this in some other way?

Thanks

You can only reference single values to compare against. You can't provide
either multiple values or how to compare them. The "Like" would need to be
hard-coded in the query.

Is the query used as the record source for a form or report?

--
Duane Hookom
MS Access MVP

Ok guys, give me the answer here. I have a form that IS successfully
passing criteria to a parameter query. However, I have one combo box on
the form that is giving me problems due to the contents. The following
are examples of statements that are entered, in their entirety in the
value fields of the combo box:

Like "*rollover*" and not like "*frontal*"
Like "*rear*" and like "*impact*"
Like "*side*" or like "*lateral*"
Like "*reclined*"

Each of the statements above are literally entered, just as they are
above, in the value field of the combo box.

The reason I've attempted to do it this way is probably obvious. I
couldn't figure out how to do it another way and accomodate for the
varied number of search terms, etc.

Basically the query simply returns no records. Does it have to do with
the punctuation in the field? Is it just impossible to pass this type of
statement in this way?

I know it would work if I was simply passing the search terms themselves
and having the query criteria entry be Like
"*[Forms]![frmCriteriaForm]![cboAccType]*" but what do I have to do to
be able to pass these types of parameters easily on the fly or at least
in a combo box. I know I'm just not doing it correctly but I'm stumped
as to the right method.

TIA

 

[Duane Hookom]

I would remove the form/control reference from the query and try something
like:
dim strWhere as String
strWhere = Me.cboWhereClause
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

You may need to change the double-quotes to singles like:
Like '*rollover*' and not like '*frontal*'

--
Duane Hookom
MS Access MVP
--

Yes the query is the record source for a report. Is there any way to
accomplish this in some other way?

Thanks

You can only reference single values to compare against. You can't
provide either multiple values or how to compare them. The "Like" would
need to be hard-coded in the query.

Is the query used as the record source for a form or report?

--
Duane Hookom
MS Access MVP

Ok guys, give me the answer here. I have a form that IS successfully
passing criteria to a parameter query. However, I have one combo box on
the form that is giving me problems due to the contents. The following
are examples of statements that are entered, in their entirety in the
value fields of the combo box:

Like "*rollover*" and not like "*frontal*"
Like "*rear*" and like "*impact*"
Like "*side*" or like "*lateral*"
Like "*reclined*"

Each of the statements above are literally entered, just as they are
above, in the value field of the combo box.

The reason I've attempted to do it this way is probably obvious. I
couldn't figure out how to do it another way and accomodate for the
varied number of search terms, etc.

Basically the query simply returns no records. Does it have to do with
the punctuation in the field? Is it just impossible to pass this type
of statement in this way?

I know it would work if I was simply passing the search terms themselves
and having the query criteria entry be Like
"*[Forms]![frmCriteriaForm]![cboAccType]*" but what do I have to do to
be able to pass these types of parameters easily on the fly or at least
in a combo box. I know I'm just not doing it correctly but I'm stumped
as to the right method.

TIA