One Group or All: How to combine

[Andy]

Hi,
I have a form, developed with the help of others on this site, that allows a
user to do the following:
1st, select one of three groups
2nd, select a start date from a calendar
3rd, select an end date from a calendar
4th, preview report.
This works beautifully but now the users want me to add a fourth group: ALL.
The users want the capability to have a report for just one of the three
original groups, or have a report that combines all three groups. I have
added the fourth group to the table and it is selectable on the form.
However, the report comes back blank/empty as it thinks ALL is a valid,
distinct group. It is not a vaild, distinct group as it is a combination of
all three groups. I am stumped as to how to get the selection of ALL to bring
back data for all three groups.
If I clear the filter manually (open the report in design view and clear the
report properties filter box) on the report itself, then I get all the data
for all three groups but cannot seem to get the filter cleared via using the
form.

Attached is "my" code for the form. Thanks in advance. Let me know if
other info is required.

This is an XP system using Access 2003.

Andy

Option Explicit

Private Sub Command36_Click()

Dim strStartDate As String
Dim strEndDate As String

Dim strWhere As String

' build data conditions.

strStartDate = "#" & Format(Me.activeXCalStart.Value, "mm/dd/yyyy") & "#"
strEndDate = "#" & Format(Me.activeXCalEnd.Value, "mm/dd/yyyy") & "#"

strWhere = "(PROJ_START >= " & strStartDate & _
" and PROJ_START <= " & strEndDate & ")"

' combo box.

strWhere = strWhere & " and (OPR = '" & Me.cboOPR & "')"

'MsgBox strWhere
Dim intBox As Integer
Dim strMsg As String
Dim strDisplayStartDate As String
Dim strDisplayEndDate As String

strDisplayStartDate = Format(Me.activeXCalStart.Value, "mm/dd/yyyy")
strDisplayEndDate = Format(Me.activeXCalEnd.Value, "mm/dd/yyyy")

strMsg = "You will see " & Me.cboOPR & " data with a start date between "
& strDisplayStartDate & " And " & strDisplayEndDate & ". Is this the data you
want?"
'strMsg = "You will see " & Me.cboOPR & " data with a start date between
" & strStartDate & _
'" And " & strEndDate & " Is this the data you want?"

intBox = MsgBox(strMsg, vbQuestion + vbYesNo, "Total Cost Report")
If intBox = vbYes Then docmd.OpenReport "repCostTotalTEST",
acViewPreview, , strWhere

End Sub

Private Sub Form_Load()
activeXCalStart.Value = Date
activeXCalEnd.Value = Date
End Sub
Private Sub activeXCalEnd_AfterUpdate()
'Purpose: Update the text boxes
txtEndDate.Value = Format(activeXCalEnd.Object.Value, "dddddd")
End Sub

Sub cmdEndNextDay_Click()
activeXCalEnd.Object.NextDay
End Sub

Sub cmdEndPreviousDay_Click()
activeXCalEnd.Object.PreviousDay
End Sub

Sub cmdEndNextWeek_Click()
activeXCalEnd.Object.NextWeek
End Sub

Sub cmdEndPreviousWeek_Click()
activeXCalEnd.Object.PreviousWeek
End Sub

Sub cmdEndNextMonth_Click()
activeXCalEnd.Object.NextMonth
End Sub

Sub cmdEndPreviousMonth_Click()
activeXCalEnd.Object.PreviousMonth
End Sub

Sub cmdEndNextYear_Click()
activeXCalEnd.Object.NextYear
End Sub

Sub cmdEndPreviousYear_Click()
activeXCalEnd.Object.PreviousYear
End Sub
Private Sub activeXCalStart_AfterUpdate()
'Purpose: Update the text boxes
txtStartDate.Value = Format(activeXCalStart.Object.Value, "dddddd")
End Sub

Sub cmdStartNextDay_Click()
activeXCalStart.Object.NextDay
End Sub

Sub cmdStartPreviousDay_Click()
activeXCalStart.Object.PreviousDay
End Sub

Sub cmdStartNextWeek_Click()
activeXCalStart.Object.NextWeek
End Sub

Sub cmdStartPreviousWeek_Click()
activeXCalStart.Object.PreviousWeek
End Sub

Sub cmdStartNextMonth_Click()
activeXCalStart.Object.NextMonth
End Sub

Sub cmdStartPreviousMonth_Click()
activeXCalStart.Object.PreviousMonth
End Sub

Sub cmdStartNextYear_Click()
activeXCalStart.Object.NextYear
End Sub

Sub cmdStartPreviousYear_Click()
activeXCalStart.Object.PreviousYear
End Sub

Private Sub Command39_Click()

Dim strStartDate As String
Dim strEndDate As String

Dim strWheree As String

' build data conditions.

strStartDate = "#" & Format(Me.activeXCalStart.Value, "mm/dd/yyyy") & "#"
strEndDate = "#" & Format(Me.activeXCalEnd.Value, "mm/dd/yyyy") & "#"

strWheree = "(PROJ_START >= " & strStartDate & _
" and PROJ_START <= " & strEndDate & ")"

' combo box.

strWheree = strWheree & " and (OPR = '" & Me.cboOPR & "')"

MsgBox strWheree

docmd.OutputTo acOutputReport, "repCostTotalTEST"
End Sub

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click
Dim strStartDate As String
Dim strEndDate As String

Dim stDocName As String

stDocName = "(PROJ_START >= " & strStartDate & _
" and PROJ_START <= " & strEndDate & ")"

stDocName = stDocName & " and (OPR = '" & Me.cboOPR & "')"

docmd.SendObject acSendReport, "repCostTotalTEST", , , , , , , , stDocName

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click


docmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

 

[Rob Oldfield]

I assume that your combo is cboOPR?

If that's right then you only need to add the OPR= part where it doesn't
equal ALL. So something like...

if me.cboOPR<>"ALL" then
stDocName = stDocName & " and (OPR = '" & Me.cboOPR & "')"
endif

....and repeat wherever you don't want the filter applied.

 

[Andy]

Yes, you are correct in that the combo is cboOPR.

Since I only have one notation of stDocName in this code then I would guess
I only need to have your addition of
if me.cboOPR<>"ALL" then
stDocName = stDocName & " and (OPR = '" & Me.cboOPR & "')"
endif
next to that one notation.

Is this correct?
Also, by stating <>"ALL" I am assuming ALL is the name of my new, combined
group, so if I named the new group ALL OPRs then I would write <>"All OPRs".
Is this correct?

Thanks for the quick reply.

Andy

 

[Rob Oldfield]

It's difficult to tell exactly where you'd need to add the filter - your
code is a bit messy with apparently multiple command buttons running much
the same code. On the live ones you'd need to also have...

if me.cboOPR<>"ALL" then
strWheree = strWheree & " and (OPR = '" & Me.cboOPR & "')"
endif

Basically, wherever you're adding that additional part onto the criteria,
you need to check that it's needed - and that's only the case if cboOPR
isn't "ALL"

(Side issue - to clean things up, change the name of the live command button
to something meaningful like btnPrintReport, copy your live code into that
button's click event, and delete all the Private Sub Command36_Click()
routines. If you're unsure of this, then make sure you make a copy of the
form first.)

And on the All OPRs question then I *think* you're right. But only if by
'group' you mean an entry in your list of combo items.

 

[Andy]

Yes, ....And on the All OPRs question .... group does mean an entry in my
list of combo items.

I added the two pieces of code you wrote and cannot get this to work yet.
What do you mean by "live" command button?

Yes, there are mulitple command buttons pretty much running the same code.
One button may adjust the date by one day, another button adjusts the date by
week, another button adjusts the date by month, and another button adjusts
the date by year. There two each of those buttons: one for the starting date
and one for the ending date, as this form has two calendars, a starting date
calendar and an ending date calendar.


Andy

 

[Rob Oldfield]

Live - as in "is triggered by an actual button on your form". The multiple
bits of very similar code made me think that you have obsolete code sitting
around the place. There's a better way of doing it but I'll come back to
that when we get a working version.

Drop this into the load event of your report...

msgbox me.filter

What do you get?