Filter a Report via Unbound Form

[bobbye_69]

Can someone give me a suggestion as to where my code is wrong? When I have a value in "WorkCenter" everything works great and graph looks fine. When I leave blank in order to see all records, I get a blank graph.

Here's the code:

Private Sub Open_Downtime_Graph_Click()
Dim strCriteria As String
If IsNull(Me.WorkCenter) Then
strCriteria = "[WorkCenter] Like '*'"
Else
strCriteria = "[WorkCenter] ='" & Me.WorkCenter.Value & "'"
End If

' Date fields are set with Default values for the Start and End dates:
' =DMin("[EventDate]","tblDowntimeData") and =DMax("[EventDate]","tblDowntimeData")

strCriteria = " AND [EventDate] BETWEEN # & Me.[EventDateStart] & "" And "" & Me.[EventDateEnd] & """

DoCmd.OpenReport "grhDowntimeData2", acPreview, , strCriteria
End Sub

 

[Cronk]

strCriteria = " AND [EventDate] BETWEEN # & Me.[EventDateStart] & "" And "" & Me.[EventDateEnd] & """

Is that correctly copied? I'd expect a syntax error. In any case, whatever was in strCriteria from the If construct, it would be overwritten by this statement.

 

[pbaldy]

Plus your syntax is wrong:

strCriteria = " AND [EventDate] BETWEEN #" & Me.[EventDateStart] & "# And #" & Me.[EventDateEnd] & "#"

 

[bobbye_69]

Can you help me fix it? I'm self taught with VB and not sure I have all the pieces in the right places.

 

[pbaldy]

I guess Cronk has gone offline. You get past the variable getting overwritten like this:

strCriteria = strCriteria & " AND [EventDate] BETWEEN #" & Me.[EventDateStart] & "# And #" & Me.[EventDateEnd] & "#"

 

[bobbye_69]

Awesome. Thanks very much I'll try tomorrow.

 

[Cronk]

Paul

Yes, I had closed out - sometimes I've got to sleep, others, do paid work.

Consequentially I don't mind others interceding, although you're more generous than me in your support. I'll start off steering questioners in the direction of a fix. Otherwise they don't really understand the code and how it works. For users who are advanced and don't need to "rediscover the wheel", I'll supply the code as I did for for namliam who wanted to extract data from an XML file.

 

[bobbye_69]

Pbaldy & Cronk,
Thanks guys. I appreciate your help. I have a good amount of computer experience in many different areas. In my past, I have always had a "programmer" that I could rely on and give him/her projects. I am now with a company that doesn't have one and I'm kind of becoming the default guy. I've never had a VB class but I'm learning. This forum has been a GREAT resource and folks like you are great to help.
Thanks again.