Limit Data to 12 months - Date range -HELP

[fluffyvampirekitten]

I have a form with 2 combo boxes( start range and end range)
and an update button .
This form will update the charts based on the Start_Range & End_Range values.

Thus , I would like to limit the range to 12 months
but I have no idea how to do it .

Anyone can help with this ?
Thanks in advance



The code below is for the Update button

Private Sub cmd_Update_Click()
Dim UpdateSQL As String
Dim StartRange, EndRange As Date
On Error GoTo ErrorHandler
    If (Me!cboStartRange.ListIndex = "-1" And cboEndRange.ListIndex = "-1") Or (Me!cboStartRange.ListIndex = "-1" Or cboEndRange.ListIndex = "-1") Then
    
    MsgBox "Please select Date Range!", vbOKOnly
    
    Me!cboStartRange.SetFocus
    
    Else
    StartRange = CDate(cboStartRange)
    EndRange = CDate(cboEndRange)
    UpdateSQL = "Update Setting Set Start_Range = '" & StartRange & "', End_Range = '" & EndRange & "' Where Type = 'SUMMARY_RANGE'"
    
    DoCmd.RunSQL UpdateSQL
    
    Me.Refresh
    
    Exit Sub
    End If
    
ErrorHandlerExit:
    Exit Sub
    
ErrorHandler:
    MsgBox "Error No: " & Err.Number & " ;  Description: " & _
    Err.Description
Resume ErrorHandlerExit
        
End Sub

[ranman256]

On The form , once the StartDate is filled , auto fill the EndDate, with the AFTERUPDATE event,
txtEndDate = dateAdd("m", 12, txtStartDate)

then the SQL would be,
update field from table where FldDate between forms!frmRpt!txtStartDate and forms!frmRpt!txtEndDate

you don't need any code, just the SQL above in a query and the form. No code.

[CJ_London]

you don't even need the txtEndDate field on your form unless you have good reason, you sql would just be

...
where FldDate between forms!frmRpt!txtStartDate and dateadd("y",1,forms!frmRpt!txtStartDate)

[fluffyvampirekitten]

So it will auto display 12 months data ?

And then I'm not gonna use this line anymore

UpdateSQL = "Update Setting Set Start_Range = '" & StartRange & "', End_Range = '" & EndRange & "' Where Type = 'SUMMARY_RANGE'"

?

[fluffyvampirekitten]

I need txtEndDate when I wanna view certain amount of data ( I only want to view 3 or 4 months data)

[CJ_London]

not sure if you are asking Ranman or myself - but yes, setting the end date 12 months or 1 year from start date will display 12 months data. If you want different periods, then Ranmans code is more appropriate