docmd.OpenRepor t - 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 tblWelderQualif ication.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
"tblWelderQuali fication.Stampe d". 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 = "(((tblWelderQu alification.[Test Number]) Like
IIf(Forms!frmTe stSubform!chkTe stNo=True,Forms !frmTestSubform !
TestNo,'*')) And " & _
"((tblWelde rs.[Welder ID]) Like IIf(Forms!frmTe stSubform!
chkWID=True,For ms!frmTestSubfo rm!wID,'*')) And " & _
"((tblWelderQua lification.[WPS No]) Like IIf(Forms!frmTe stSubform!
chkWPS=True,For ms!frmTestSubfo rm!subWPS,'*')) And " & _
"((tblWelderQua lification.Stam ped) Like IIf(Forms!frmTe stSubform!
chkCertified='T rue','True','*' )) And " & _
"((tblWelderQua lification.Proj ect) Like IIf(Forms!frmTe stSubform!
chkProject=True ,Forms!frmTestS ubform!txtProje ct,'*')))"
stDocName = "rptTest"
DoCmd.OpenRepor t 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 tblWelderQualif ication.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
"tblWelderQuali fication.Stampe d". 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 = "(((tblWelderQu alification.[Test Number]) Like
IIf(Forms!frmTe stSubform!chkTe stNo=True,Forms !frmTestSubform !
TestNo,'*')) And " & _
"((tblWelde rs.[Welder ID]) Like IIf(Forms!frmTe stSubform!
chkWID=True,For ms!frmTestSubfo rm!wID,'*')) And " & _
"((tblWelderQua lification.[WPS No]) Like IIf(Forms!frmTe stSubform!
chkWPS=True,For ms!frmTestSubfo rm!subWPS,'*')) And " & _
"((tblWelderQua lification.Stam ped) Like IIf(Forms!frmTe stSubform!
chkCertified='T rue','True','*' )) And " & _
"((tblWelderQua lification.Proj ect) Like IIf(Forms!frmTe stSubform!
chkProject=True ,Forms!frmTestS ubform!txtProje ct,'*')))"
stDocName = "rptTest"
DoCmd.OpenRepor t 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 = "(((tblWelderQu alification.[Test Number]) Like
IIf(Forms!frmTe stSubform!chkTe stNo=True,Forms !frmTestSubform !
TestNo,'*')) And " & _
"((tblWelde rs.[Welder ID]) Like IIf(Forms!frmTe stSubform!
chkWID=True,For ms!frmTestSubfo rm!wID,'*')) And " & _
"((tblWelderQua lification.[WPS No]) Like IIf(Forms!frmTe stSubform!
chkWPS=True,For ms!frmTestSubfo rm!subWPS,'*')) And " & _
"((tblWelderQua lification.Stam ped)=IIf(Forms! frmTestSubform!
chkCertified=Tr ue,True,'*')) And " & _
"((tblWelderQua lification.Proj ect) Like IIf(Forms!frmTe stSubform!
chkProject=True ,Forms!frmTestS ubform!txtProje ct,'*')))"

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 = "(((tblWelderQu alification.[Test Number]) Like
IIf(Forms!frmTe stSubform!chkTe stNo=True,Forms !frmTestSubform !
TestNo,'*')) And " & _
"((tblWelde rs.[Welder ID]) Like IIf(Forms!frmTe stSubform!
chkWID=True,For ms!frmTestSubfo rm!wID,'*')) And " & _
"((tblWelderQua lification.[WPS No]) Like IIf(Forms!frmTe stSubform!
chkWPS=True,For ms!frmTestSubfo rm!subWPS,'*')) And " & _
"((tblWelderQua lification.Stam ped)=IIf(Forms! frmTestSubform!
chkCertified=Tr ue,True,'*')) And " & _
"((tblWelderQua lification.Proj ect) Like IIf(Forms!frmTe stSubform!
chkProject=True ,Forms!frmTestS ubform!txtProje ct,'*')))"

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

WHERE (TestNumber = Forms!frmTestSu bform!TestNo
OR Forms!frmTestSu bform!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