Parameter query by Month/Year

OldCityCat · August 13th, 2010, 03:45 PM

I was thinking there has to be a way to prompt for monthly data without
using [Start Date] and [End Date].

I thought it would be a simple parameter query
that prompts the user with [Please enter Reporting Month/Year as mm/yy].

I've tried using different variation of DatePart and DateSerial as the criteria for
my date field ExpDate which is formated as mm/dd/yy.
and these Expressions: Month: Month([ExpDate]), Year: Year([ExpDate])

None are working as I had hoped.

I would be grateful for any and all suggestions.

Gil

allenm · August 27th, 2010, 04:03 PM

Is this for a form filter to be set when you first load a form? Instead of using a query for this I'd just set form parameters on load. To make it static (not removable by turning off or clearing a filter) you'd want to set the RecordSource instead of the filter.

Just put this code in as your Form_Load event:

Code:

Private Sub Form_Load()

'Sets Date From and Date To as filter criteria for form. Default is set to be 1 week including today.
  Dim dFromDate As Date, dToDate As Date, sTemp As String, bGood As Boolean
  
  Do While Not bGood
    sTemp = InputBox("Please Input Month/Year From (in DATE Format)", "Input Date From", _
      Format(Now() - 6, "mm/dd/yyyy") & "")
    If Not IsDate(sTemp) Then
      MsgBox "Improper Entry, needs to be In Valid DATE Format (example: 01/10/2010 for January 10, 2010)" _
        , , "Improper Format"
    Else
      bGood = True
    End If
  Loop
  dFromDate = CDate(sTemp)

bGood = False
  Do While Not bGood
    sTemp = InputBox("Please Input Month/Year From (in DATE Format)", "Input Date To", _
      Format(Now(), "mm/dd/yyyy") & "")
    If Not IsDate(sTemp) Then
      MsgBox "Improper Entry, needs to be In Valid DATE Format (example: 01/10/2010 for January 10, 2010)" _
        , , "Improper Format"
    Else
      bGood = True
    End If
  Loop
  dToDate = CDate(sTemp)

'You can make this a part of the actual RecordSource for the Form instead if desired.
'me.RecordSource = "Select * From YourTableName Where Date_Modified >= #" _
  & Format(dFromDate, "mm/dd/yyyy") & "# And Date_Modified <= #" _
  & Format(dToDate, "mm/dd/yyyy") & "#"

'But for this example I'll just set the filter property of the form and turn the filter on
  Me.Filter = "Date_Modified >= #" & Format(dFromDate, "mm/dd/yyyy") & "#" _
    & " And Date_Modified <= #" & Format(dToDate, "mm/dd/yyyy") & "#"
  Me.FilterOn = True

End Sub

Modify it to meet your specific needs. If you prefer you can spawn a child form that asks for date entry complete with calendar pop-up to 'spiff' it up instead of using 'mundane' inputboxes.

Old Pedant · August 27th, 2010, 04:30 PM

I think he is asking how to allow JUST entering MM/YY *INSTEAD* of insisting on MM/DD/YYYY.

And that shouldn't be hard.

Code:

bGood = False
Do Until bGood
    dTemp = "OOPS"
    sTemp = InputBox("Please Input FROM Month/Year ", "Input Date From", _
       Format(Now(), "mm/yy") & "")
    aTemp = Split( sTemp, "/" )
    If UBound(aTemp) = 1 Then
        dTemp = DateSerial( 2000+CINT(aTemp(1)), CINT(aTemp(0)), 1 )
    End If
    If Not IsDate(dTemp) Then
      MsgBox "Improper Entry, needs to be MM/DD (e.g., 1/10 fr January 2010)" _
        , , "Improper Format"
    Else
      bGood = True
    End If
Loop
dFromDate = dTemp
 
bGood = False
Do Until bGood
    dTemp = "OOPS"
    sTemp = InputBox("Please Input TO Month/Year From ", "Input Date To", _
       Format(Now(), "mm/yy") & "")
    aTemp = Split( sTemp, "/" )
    If UBound(aTemp) = 1 Then
        ' trick to get last day of given month:
        dTemp = DateSerial( 2000+CINT(aTemp(1)), CINT(aTemp(0)) + 1, 0 )
    End If
    If Not IsDate(dTemp) Then
      MsgBox "Improper Entry, needs to be MM/DD (e.g., 1/10 fr January 2010)" _
        , , "Improper Format"
    Else
      bGood = True
    End If
Loop
dToDate = dTemp

No?

The trick with DateSerial is documented by MS. Add 1 to the month number and use 0 as the day number and it gives you the last day of the given month.