Open Report with 2 conditions

[rck3]

Ok so I have my database about complete, just working on more dynamic reports. I have 5 standard reports set up. I then set them up so I could filter by one of the fields. I was just asked to add a data range to the report now. I was able to create a date range report for the overall data and was trying to get it to include the other varibale. Below is what I have so far but it opens blank. What am I missing?



DoCmd.OpenReport strReport, lngView, , strWhere & "AND [Project worksheet] = '" & Me.PW_Filter & "'"

StrReport is the report name. StrWhere is the data ranges (it works if I end the command here). I added the variable & "AND XXXXXXXX" which works seperately to bring up all records that match my PW_Filter.

[pbaldy]

Does this work (added space)?

DoCmd.OpenReport strReport, lngView, , strWhere & " AND [Project worksheet] = '" & Me.PW_Filter & "'"

If not, try this technique to print out the finished string to the Immediate window:

http://www.baldyweb.com/ImmediateWindow.htm

[rck3]

Nope, I did test it with no selection in the PW_Filter filter and it still came up blank.

[pbaldy]

So, did you use that technique to print out the finished string?

[rck3]

Not sure why there are 2 output lines and not sure why it says 2011. I have 2012 in the entry field.

StrWhere = [Project worksheet] = '2001' And ([Date_Worked] >= #04/01/2011#) AND ([Date_Worked] < #05/02/2012#)

The immediate out is
([Date_Worked] >= #01/01/2011#) AND ([Date_Worked] < #05/02/2012#)
([Date_Worked] >= #04/01/2011#) AND ([Date_Worked] < #05/02/2012#)

[rck3]

Here is the code, I played around and created a variable for the project worksheet field so I can reuse once it's working.

Const strcJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Time by Project Worksheet" 'Replace for Report Name.
strWhat = "[Project worksheet] = '" & Me.PW_Filter & "'" 'Replace for new report type.
strDateField = "[Date_Worked]" 'Replace for Date Field
lngView = acViewPreview
strWhere = strWhat & " AND "
If IsDate(Me.txtStartDate) Then
strWhere = strWhere & "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

[pbaldy]

So it's working now?

[rck3]

No sorry if I confused. It still isnt working. Weird thing is I tried a fwe different selections and sometimes it worked but mostly came up blank. I checked the data and my options should have generated data.

[pbaldy]

Can you post the db here, or a representative sample?

[rck3]

AL_Project_Worksheets - Copy.zipAL_Project_Worksheets - Copy.zip

I stripped out some of the other stuff to just leave the 1 report I was working on, I can apply to the others in my actual db version. I left in some test data though so you can see. I am trying to get the report to populate with both a date selection and project selected. Thanks!

[pbaldy]

Unless I'm missing something, nothing is returned because the data doesn't match up. The Project Worksheet field contains values like:

MABMJ01
Multiple

not

0001
0002

[rck3]

Thanks, let me load the real data and try it. Sometimes you get so focused on code issues you forget you might have bad data. I just removed all data and entered in new test data and it did seem to work. I will try it with the full data and let you know. Thanks