openreport where condition with IIf statment

[Wysy]

Hi,
I have a filter form to values on which the openreport command button is based on like:
DoCmd.OpenReport "rpTreatments", acViewReport, , "Customer='" & Me.Customer & "' And [DOV]>" & Format(Me.[DOVx], "\#mm\/dd\/yyyy\#")

it works fine. However in case value is not provided i would like to use wildcard to open all the records. So if customer field is left empty then the report should show all the records, if value is provide then obviously only that one.
I have managed to use macro where condition successfully like:
TreatmentsTB.Customer=Forms!frmReportFilter!Customer And TreatmentsTB.DOV>Forms!frmReportFilter!DOVx And IIf(IsNull(Forms!frmReportFilter![Horsex]);TreatmentsTB.Horse Like "*";TreatmentsTB.Horse=Forms!frmReportFilter![Horsex])

So here the iif function worked fine. However here only 255 character can be use so i have tried to use VBA with openreport command. I do not get it worked.
So the where condition "Customer='" & me.customer &"'" works, but i would like to use it too when me. customer is null to show everything. How do i do it?
thanks
Andrew

 

[Uncle Gizmo]

The videos in my YouTube playlist --- VBA Beginner - Nifty Access --- from about video number 9 cover something similar but doing it with the form. However it should be roughly the same process for a report.

 

[isladogs]

Try this

If Nz(Me.Customer,"") <>"" Then
	DoCmd.OpenReport "rpTreatments", acViewReport, , "Customer='" & Me.Customer & "' And [DOV]>" & Format(Me.[DOVx], "\#mm\/dd\/yyyy\#")
Else
	DoCmd.OpenReport "rpTreatments", acViewReport
End If

Colin

 

[Wysy]

Thank you! This worked finally
Private Sub Command16_Click()
strc = IIf(Me.[Customer] & "" = "", Null, "Customer = '" & Me.Customer & "'and ")
strD = IIf(Me.[DOV] & "" = "", "Year([DOV]) = '" & 2017 & "'", "[DOV]=" & Format(Me.[DOV], "\#mm\/dd\/yyyy\#"))
StrH = IIf(Me.[Horse] & "" = "", Null, "and Horse = '" & Me.Horse & "'")
DoCmd.OpenReport "rpTreatments", acViewReport, , strc & strD & StrH
End Sub
I need to play with the and s because of Null statements.
thank you

 

[Cronk]

A suggested change so that the default is the current year

strD = IIf(Me.[DOV] & "" = "", "Year([DOV]) = year(date())", "[DOV]=" & Format(Me.[DOV], "\#mm\/dd\/yyyy\#"))