docmd.OpenReport - problem with Where Clause

[russellhq]

Hi, i am trying to open a report based on values picked in a form but
get stuck with one of the values. I have a feeling it has to do with
the yes/no datatype but am not sure since I am quite new to access
VBA. I'm using Access2003

Below is my code extract. The tblWelderQualification.Stamped field is
a yes/no field, I want to show records that have a "TRUE" value when
the checkbox on the form is ticked. When I run the code, I get the
parameter value window popup asking for
"tblWelderQualification.Stamped". If i type "True" then the report
works, if left blank it fails. I dont want to have to type anything
and would like it to work as it does for the other fields.

Any ideas??

stWhereClause = "(((tblWelderQualification.[Test Number]) Like
IIf(Forms!frmTestSubform!chkTestNo=True,Forms!frmT estSubform!
TestNo,'*')) And " & _
"((tblWelders.[Welder ID]) Like IIf(Forms!frmTestSubform!
chkWID=True,Forms!frmTestSubform!wID,'*')) And " & _
"((tblWelderQualification.[WPS No]) Like IIf(Forms!frmTestSubform!
chkWPS=True,Forms!frmTestSubform!subWPS,'*')) And " & _
"((tblWelderQualification.Stamped) Like IIf(Forms!frmTestSubform!
chkCertified='True','True','*')) And " & _
"((tblWelderQualification.Project) Like IIf(Forms!frmTestSubform!
chkProject=True,Forms!frmTestSubform!txtProject,'* ')))"
stDocName = "rptTest"
DoCmd.OpenReport stDocName, acPreview, , stWhereClause

[Rick Brandt]

ru*******@gmail.com wrote:

Hi, i am trying to open a report based on values picked in a form but
get stuck with one of the values. I have a feeling it has to do with
the yes/no datatype but am not sure since I am quite new to access
VBA. I'm using Access2003

Below is my code extract. The tblWelderQualification.Stamped field is
a yes/no field, I want to show records that have a "TRUE" value when
the checkbox on the form is ticked. When I run the code, I get the
parameter value window popup asking for
"tblWelderQualification.Stamped". If i type "True" then the report
works, if left blank it fails. I dont want to have to type anything
and would like it to work as it does for the other fields.

Any ideas??

stWhereClause = "(((tblWelderQualification.[Test Number]) Like
IIf(Forms!frmTestSubform!chkTestNo=True,Forms!frmT estSubform!
TestNo,'*')) And " & _
"((tblWelders.[Welder ID]) Like IIf(Forms!frmTestSubform!
chkWID=True,Forms!frmTestSubform!wID,'*')) And " & _
"((tblWelderQualification.[WPS No]) Like IIf(Forms!frmTestSubform!
chkWPS=True,Forms!frmTestSubform!subWPS,'*')) And " & _
"((tblWelderQualification.Stamped) Like IIf(Forms!frmTestSubform!
chkCertified='True','True','*')) And " & _
"((tblWelderQualification.Project) Like IIf(Forms!frmTestSubform!
chkProject=True,Forms!frmTestSubform!txtProject,'* ')))"
stDocName = "rptTest"
DoCmd.OpenReport stDocName, acPreview, , stWhereClause

A Yes/No actually stores -1 and 0 for True and False, but you can apply
criteria using True and False, but not 'True' and 'False' as you are.

Also, Like is only appropriate for text fields. For Yes/No, Numeric, and
Dates you should stick with "=".

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[russellhq]

Tried changing things to the below, but still get the same problem.

stWhereClause = "(((tblWelderQualification.[Test Number]) Like
IIf(Forms!frmTestSubform!chkTestNo=True,Forms!frmT estSubform!
TestNo,'*')) And " & _
"((tblWelders.[Welder ID]) Like IIf(Forms!frmTestSubform!
chkWID=True,Forms!frmTestSubform!wID,'*')) And " & _
"((tblWelderQualification.[WPS No]) Like IIf(Forms!frmTestSubform!
chkWPS=True,Forms!frmTestSubform!subWPS,'*')) And " & _
"((tblWelderQualification.Stamped)=IIf(Forms!frmTe stSubform!
chkCertified=True,True,'*')) And " & _
"((tblWelderQualification.Project) Like IIf(Forms!frmTestSubform!
chkProject=True,Forms!frmTestSubform!txtProject,'* ')))"

Also, in the query where I copied this from. If I change "Like" to
"=" on the "stamped" field condition then I get the following error
when I try to view in datasheet view.

"The expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables."

If I change it back to "Like" then it works fine.

I also tried removing the where clause in the OpenReport and adding a
filter, this being the query from which I got the where statement.
This produced the same problem.

Could it be the case that because the "stamped" field is not in the
report I am trying to open is the reason i am getting this error?

[Rick Brandt]

ru*******@gmail.com wrote:

Tried changing things to the below, but still get the same problem.

stWhereClause = "(((tblWelderQualification.[Test Number]) Like
IIf(Forms!frmTestSubform!chkTestNo=True,Forms!frmT estSubform!
TestNo,'*')) And " & _
"((tblWelders.[Welder ID]) Like IIf(Forms!frmTestSubform!
chkWID=True,Forms!frmTestSubform!wID,'*')) And " & _
"((tblWelderQualification.[WPS No]) Like IIf(Forms!frmTestSubform!
chkWPS=True,Forms!frmTestSubform!subWPS,'*')) And " & _
"((tblWelderQualification.Stamped)=IIf(Forms!frmTe stSubform!
chkCertified=True,True,'*')) And " & _
"((tblWelderQualification.Project) Like IIf(Forms!frmTestSubform!
chkProject=True,Forms!frmTestSubform!txtProject,'* ')))"

Get rid of all the check boxes and use criteria like this...

WHERE (TestNumber = Forms!frmTestSubform!TestNo
OR Forms!frmTestSubform!TestNo Is Null)

That returns all rows if the control on the form is empty and only matching rows
when the control on the form has a value in it. (Much simpler)

repeat for other criteria
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com