there's no reason why the criteria i posted should not work if set up in the
query correctly, and no hard limit of criteria on two fields only. having
said that, the problem with your code is that in an If, Then, Else statement
the code runs the actions for the first matching expression *and then skips
to End If*. so you're never going to get a concatenation of multiple
criteria from your code. instead, try separate If statements, as
If Not IsNull(Me!ChWard) Then
strWhere = "WardName = '" _
& Me!ChWard & "' And"
End If
If Not IsNull(Me!ChArea) Then
strWhere = strWhere & " Area = '" _
& Me!ChArea & "' And"
End If
If Not IsNull(Me!ChCaseOfficer) Then
strWhere = strWhere & " CaseOfficer = '" _
& Me!ChCaseOfficer & "' And"
End If
If Not IsNull(Me!ChProp) Then
strWhere = strWhere & " [Property Type] = '" _
& Me.ChProp & "'"
End If
If Right(strWhere, 4) = " And" Then
strWhere = Trim(Left(strWhere, Len(strWhere)-4))
Else
strWhere = Trim(strWhere)
End If
DoCmd.OpenReport RptName, acViewPreview, , strWhere
hth
"cesima via AccessMonster.com" <u6919@uwewrote in message
news:78a64e016ed7a@uwe...
Hi Tina
Thanks for your reply.
I tried this method yesterday, with very limited results. It would work
sometimes then not at all. I tested it using criteria I know appeared in
the
underlaying tables. It also seems to change the design grid everytime, its
run. This method only seems to work where there are only 2 parameters.
I tried again just now and the same thing happens. This is why I wanted to
try the vba where clause method instead but I just can't seem to get the
syntax right.
Do you know where I might find the code for 4 parameters using the code
similar to what I posted.
I have looked on various other posts but they seem to stop at 2 combo text
boxes on a form and I have 4. Does using this code make a difference when
you
want to open the report in preview mode. The other code seems to be where
you
print the report.
Thanks once again for your response though.
Ceebaby
London
tina wrote:
it would be a lot easier to add criteria to the query underlying the
report.
criteria for WardName would be
Forms!FormName!ChWard or Forms!FormName!ChWard Is Null
the above goes all on the top line in the criteria grid, regardless of
line
wrap in this post.
criteria for Area would be
Forms!FormName!ChArea or Forms!FormName!ChArea Is Null
again, all goes on the top line in the criteria grid.
criteria for CaseOfficer would be
Forms!FormName!ChCaseOfficer or Forms!FormName!ChCaseOfficer Is Null
...all on the top line...
and criteria for Property Type would be
Forms!FormName!ChProp or Forms!FormName!ChProp Is Null
...on the top line...
in all instances, replace FormName with the correct name of the form, and
make sure the form stays open while the report is opened (you can make
the
form invisible if you don't want it to appear open). since none of the
textbox control values are dates, you should not need to list the
criteria
in the Parameters box in query Design view.
hth
In relation to the above post I have declared
[quoted text clipped - 48 lines]
Where am I going wrong
Any help would be much appreciated.
--
Ceebaby
Trying to be great at Access
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200709/1