Optin Group with dates in Access 2010 (1 Viewer)

[gcarpenter]

I have a retort that I want to use an option button to filter the report is a specific date is 48 hours before the current time. I'm not having any luck. Below is the code I have been playing with, but no go. :banghead:I appreciate any help provided. Thanks in advance....

Private Sub NoMove_Click()
Select Case Me.NoMove.VALUE
Case 1
Me.Filter = "CLM = 'Now() - 48'"
Me.FilterOn = True


End Select
If Me.Filter = "" Then
MsgBox "The filter returned Null"
Else
End If
End Sub

 

[pbaldy]

You could use the DateAdd() function to subtract 48 hours. Records would have to match down to the second (actually lower than that); is that practical?

 

[gcarpenter]

How do I implement that into my code, I have not used this function before in an option group. Thanks for your help.

 

[pbaldy]

Me.Filter = "CLM = DateAdd(...)"

but again, your realize Now() includes the time, so the match would have to be on that? Perhaps you wanted Date().

 

[gcarpenter]

I tried this sir, can't seem to get it to work.
Private Sub NoMove_Click()
Select Case Me.NoMove.VALUE
Case 1
Me.Filter = "CLM = Now() -2"
Me.FilterOn = True

End Select
If Me.Filter = "" Then
MsgBox "The filter returned Null"
Else
End If
End Sub

 

[pbaldy]

What does "can't seem to get it to work" mean exactly? As I've mentioned twice, using Now() would require that there be a matching record down to the second, not just the date.

 

[gcarpenter]

I got it working, I was thinking to much. You advice worked. The data field I was using is in time format down to the second.

 

[pbaldy]

Glad you got it working. Posting your solution could help others in the future. Presumably you just worked with the date portion?

 

[gcarpenter]

This is what worked for me, I use strFilter2, because strFilter1 is the company, there are 4 different company that this data would apply to. "SC" is sight code that is given by the railroad. I have to use a command button to fire the different filters, one apply strFilter1, and one button applies strFilter2, and I have a button that applies both filters. I could not figure out how to filter with srtFilter1, and then filter with strFilter2 because it would remove strFilter1.

Private Sub NoMoveCriteria_Click()
Dim srtFilter2 As String
Select Case Me.NoMove.VALUE
Case 1
strFilter2 = "CLM < Now()-2 and SC <> 'Y' and SC <> 'Z' And SC <> 'D' And SC <> 'S'"
Me.FilterOn = True

End Select
If Me.Filter = "" Then
MsgBox "The filter returned Null"
Else
strAddCriteria = strFilter2
DoCmd.OpenReport "rptCustRailcarStatus", acViewReport, , strAddCriteria
End If
End Sub

 

[gcarpenter]

The report being discuss in this thread ask for a parameter value for "Z" when I apply a filter, it does it on AllCriteria button, the NoMoveCriteria button, or the CompanyCriteria button. I cannot find out why. If any can review the code below and point me in the right direction it would be much appreciated.

Report Code:

 Option Compare Database
Dim CloseDialog As Boolean
 Private Sub AllCriteria_Click()
Dim strFilter1 As String
Dim strFilter2 As String
 Select Case Me.Company.VALUE
            Case 1
         strFilter1 = "Product = 'PETA'"
         Me.FilterOn = True
      Case 2
         strFilter1 = "Product = 'PET' Or Product = 'PEBM'"
         Me.FilterOn = True
      Case 3
         strFilter1 = "Product = 'PETS'"
         Me.FilterOn = True
      Case 4
         strFilter1 = "Product = 'AE3' Or Product = 'AE7' Or Product = 'IVOM' Or Product = 'IVOD' Or Product = 'IVOT' Or Product = 'IVEO'"
         Me.FilterOn = True
      
     End Select
 
Select Case Me.NoMove.VALUE
Case 1
strFilter2 = "CLM < Now()-2 and SC <> 'Y' and SC <> 'Z' And SC <> 'D' And SC <> 'S'"
Me.FilterOn = True
 
End Select
If Me.Filter = "" Then
MsgBox "The filter returned Null"
Else
End If
  If strFilter1 = "" Then
MsgBox "Please choose Company", vbCritical, "No Company Selected"
End If
If strFilter2 = "" Then
MsgBox "Please choose Non Moving", vbCritical, "Non moving not Selected"
End If
 strAddCriteria = "(" & strFilter1 & ")" & " and " & "(" & strFilter2 & ")"
DoCmd.OpenReport "rptCustRailcarStatus", acViewReport, , strAddCriteria
 
End Sub
 Private Sub Exit_Click()
DoCmd.Close acReport, "rptCustRailcarStatus", acSaveYes
DoCmd.Close acForm, "CustomerLocationsfrm", acSaveYes
DoCmd.Close acForm, "CustomerDestinationfrm", acSaveYes
 End Sub
Private Sub Update_Click()
Dim Cancel As Integer
If MsgBox("Do you want to update this report? Hit Ok or hit cancel", vbOKCancel + vbCritical, "New Customer Data") = vbCancel Then
Cancel = True
End If
If Not Cancel Then
 
DoCmd.SetWarnings False
DoCmd.OpenQuery "Del_CustomerStatus", acViewNormal, acEdit
DoCmd.OpenQuery "qry_CustomerRailcarStatus", acViewNormal, acEdit
DoCmd.SetWarnings True
DoCmd.Close acReport, "rptCustRailcarStatus", acSaveYes
DoCmd.OpenReport "rptCustRailcarStatus", acViewReport
End If
End Sub
Private Sub Unfilter_Click()
Me.Filter = ""
Me.Company.VALUE = Null
Me.NoMove.VALUE = Null
End Sub
 Private Sub CompanyCriteria_Click()
Dim strFilter1 As String
Select Case Me.Company.VALUE
            Case 1
         strFilter1 = "Product = 'PETA'"
         Me.FilterOn = True
      Case 2
         strFilter1 = "Product = 'PET' Or Product = 'PEBM'"
         Me.FilterOn = True
      Case 3
         strFilter1 = "Product = 'PETS'"
         Me.FilterOn = True
      Case 4
         strFilter1 = "Product = 'AE3' Or Product = 'AE7' Or Product = 'IVOM' Or Product = 'IVOD' Or Product = 'IVOT' Or Product = 'IVEO'"
         Me.FilterOn = True
      
     End Select
 If strFilter1 = "" Then
 MsgBox "Please choose company"
Else
 strAddCriteria = strFilter1
DoCmd.OpenReport "rptCustRailcarStatus", acViewReport, , strAddCriteria
 End If
End Sub
 Private Sub NoMoveCriteria_Click()
Dim srtFilter2 As String
Select Case Me.NoMove.VALUE
Case 1
strFilter2 = "CLM < Now()-2 and SC <> 'Y' and SC <> 'Z' And SC <> 'D' And SC <> 'S'"
Me.FilterOn = True
 End Select
If Me.Filter = "" Then
MsgBox "The filter returned Null"
Else
 strAddCriteria = strFilter2
DoCmd.OpenReport "rptCustRailcarStatus", acViewReport, , strAddCriteria
 End If
 End Sub
Private Sub Report_Close()
Call LogDocClose(Me)
End Sub
Private Sub Report_Open(Cancel As Integer)
CloseDialog = False
Call LogDocOpen(Me)
End Sub
Private Sub Report_Resize()
DoCmd.Maximize
End Sub

 

[BlueIshDan]

Please edit to have code braces.

 

[pbaldy]

Does it open without the prompt if you open it from the Nav Pane?

 

[gcarpenter]

The report open fine, just when I click on one of the buttons to apply the filter from an option group selection, it pops up the window asking for a parameter value for "Z".

 

[pbaldy]

Don't see the reason for that right off, but noticed you're doing this:

strFilter1 = "Product = 'PETA'"
Me.FilterOn = True

You never set the filter property to the variable.

 

[gcarpenter]

pBaldy, this fixed the issue, works perfect. Thank you very much.

 

[pbaldy]

Happy to help!