Strange Query by Form Problem

[sword856]

Hello all, I am having something weird happen to me when I try to run
a query/report from a form. The form has:

Combo box - cboPickTask
Text Box - txtQCriteria
CmdButton - cbutRunQuery
cmdButton - cbutGetRep

It is an unbound form and is simply used as the selector for the query
criteria. User selects task from dropdown menu, query returns all
personnel who have not completed the task. Personnel are either
supervisory or non-supervisory. In the combo RowSource is a column
beside the task name that is either "All", "Supervisory" or "Non-
Supervisory". I am trying to get the query to show only those who
have to complete the task and haven't, and exclude those who haven't
taken the class but don't have to. Right now, I can get it to work
for supervisory and non-supervisory, but not for all. Here is my code
for the combo after update:

Private Sub cboPickTask_AfterUpdate()

If cboPickTask.Column(1) = "Non-Supervisory" Then
txtQCriteria.Value = "0"
ElseIf cboPickTask.Column(1) = "Supervisory" Then
txtQCriteria.Value = "-1"
Else
txtQCriteria.Value = "0 Or -1"
End If

End Sub

If I select a task that is "All" or try to use "yes" or "no" instead
of "-1" and "0", then I get the error "You Cancelled the previous
operation" when I try to run the query, and the error that the
expression is too complex if I try to run the report. In both cases
the only option is to click "OK" and do nothing.

Note: I went into the query and manually typed in "0 Or -1" for the
SupervisoryStatus criteria and it worked as I wanted it to.

If anyone could explain why this isn't working, I would be very
grateful.

TIA,
George

 

[Allen Browne]

OR is an operator. You can't use an operator as a parameter value like that.

A more efficient solution would be to omit the criteria from the query.
Instead, build the WhereCondition for OpenReport from only the boxes where
the criteria is needed.

Here's a downloadable example of how to do that:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example filters a form, but filtering the report is identical.

 

[sword856]

OR is an operator. You can't use an operator as a parameter value like that.

A more efficient solution would be to omit the criteria from the query.
Instead, build the WhereCondition for OpenReport from only the boxes where
the criteria is needed.

Here's a downloadable example of how to do that:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example filters a form, but filtering the report is identical.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.















- Show quoted text -

Thanks, Allen! I'll try it out and get back to you.
A few questions: So if OR is entered in the query from a form, the
query doesn't read it as an operator? I was trying to get the the
query to search for instances where SupervisoryStatus is either "-1"
or "0". Also, do you know why the query wouldn't run with "Yes" and
"No" in the code?

 

[Allen Browne]

If you switch your query to SQL View, you will see that the WHERE clause
comes out like this:
WHERE SomeField = [Forms].[Form1].[Text0]

Now if the text box contains:
"0 or -1"
the query will be equivalent to:
WHERE SomeField = "0 or -1"

That's not a valid WHERE clause. It would need to be:
WHERE SomeField = "0" OR SomeField = "-1"

In short, an operator such as OR cannot be a parameter value.

 

[sword856]

Thanks, Allen! I'll try it out and get back to you.
A few questions: So if OR is entered in the query from a form, the
query doesn't read it as an operator? I was trying to get the the
query to search for instances where SupervisoryStatus is either "-1"
or "0". Also, do you know why the query wouldn't run with "Yes" and
"No" in the code?- Hide quoted text -

- Show quoted text -

I'm not sure how to implement this for my query. My query is a Not
Exists query, so I don't know how to apply a filter to the table where
I am looking for something that isn't there. This is my SQL as my
query stands right now:

SELECT Personnel.FullName, Personnel.Code, Personnel.SupervisoryStatus
FROM Personnel
WHERE (((Exists (SELECT FullName FROM [Training History]
WHERE [Training History].FullName = Personnel.FullName AND
[Training History].ClassName =[Forms]![NoncomplianceQueryForm]!
[cboPickTask]))=False))
ORDER BY Personnel.FullName;

This is after I took out the criteria for SupervisoryStatus.
I will continue working with this, just thought I would put this out
there because it seems to complicate things for me.
Thanks,
George

 

[Allen Browne]

Anything that goes in the WHERE clause of the query can go in the Filter (or
WhereCondition) string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen! I'll try it out and get back to you.
A few questions: So if OR is entered in the query from a form, the
query doesn't read it as an operator? I was trying to get the the
query to search for instances where SupervisoryStatus is either "-1"
or "0". Also, do you know why the query wouldn't run with "Yes" and
"No" in the code?- Hide quoted text -

- Show quoted text -

I'm not sure how to implement this for my query. My query is a Not
Exists query, so I don't know how to apply a filter to the table where
I am looking for something that isn't there. This is my SQL as my
query stands right now:

SELECT Personnel.FullName, Personnel.Code, Personnel.SupervisoryStatus
FROM Personnel
WHERE (((Exists (SELECT FullName FROM [Training History]
WHERE [Training History].FullName = Personnel.FullName AND
[Training History].ClassName =[Forms]![NoncomplianceQueryForm]!
[cboPickTask]))=False))
ORDER BY Personnel.FullName;

This is after I took out the criteria for SupervisoryStatus.
I will continue working with this, just thought I would put this out
there because it seems to complicate things for me.
Thanks,
George

 

[sword856]

Anything that goes in the WHERE clause of the query can go in the Filter (or
WhereCondition) string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I'm not sure how to implement this for my query. My query is a Not
Exists query, so I don't know how to apply a filter to the table where
I am looking for something that isn't there. This is my SQL as my
query stands right now:

SELECT Personnel.FullName, Personnel.Code, Personnel.SupervisoryStatus
FROM Personnel
WHERE (((Exists (SELECT FullName FROM [Training History]
WHERE [Training History].FullName = Personnel.FullName AND
[Training History].ClassName =[Forms]![NoncomplianceQueryForm]!
[cboPickTask]))=False))
ORDER BY Personnel.FullName;

This is after I took out the criteria for SupervisoryStatus.
I will continue working with this, just thought I would put this out
there because it seems to complicate things for me.
Thanks,
George- Hide quoted text -

- Show quoted text -

Thanks, Allen, for your idea! Since I only had SupervisoryStatus to
worry about, I used filters in AfterUpdate of cboPickTask so the form
showed only the pertinent records. Filters, gotta remember about
those..

Thanks again,
George