Hello there! I am going crazy. I am writing a VBA module in my Access Database to query down a Table showing in a Subform. The one issue I am having right now is getting the Date Range part of the SQL Query to work. What happens now is it acts like it doesn't recognize the "StartDate" and "EndDate" variables, and instead, prompts me twice for each. WHAT AM I DOING WRONG? JUST USE MY VARIABLES! Lol.
Private Sub runQueryBtn_Click()
'On Error GoTo runQueryBtn_Click_Err
Dim SearchUpdate As String
Dim WUC As String
Dim TMS As String
Dim StartDate As Date
Dim DateCalcMonth As Date
Dim EndDate As Date
whatTMS.SetFocus
TMS = whatTMS.Text
whatWUC.SetFocus
WUC = whatWUC.Text
If (dateRangeGroup = 1) Then
EndDate = DMax("[Comp Date Time]", "tblMAFs", "[Type Model Series] LIKE '*" & TMS & "*'")
DateCalcMonth = DateSerial(Year(EndDate), Month(EndDate), 1)
StartDate = DateAdd("M", -11, DateCalcMonth)
Else
StartDate = DMin("[Comp Date Time]", "tblMAFs", "[Type Model Series] LIKE '*" & TMS & "*'")
EndDate = DMax("[Comp Date Time]", "tblMAFs", "[Type Model Series] LIKE '*" & TMS & "*'")
End If
SearchUpdate = " SELECT tblMAFs.* " _
& " FROM tblMAFs " _
& " WHERE tblMAFs.[Type Model Series] LIKE '*" & TMS & "*' " _
& " AND tblMAFs.[WUC] LIKE '*" & WUC & "*' " _
& " AND tblMAFs.[Comp Date Time] BETWEEN StartDate and EndDate " _
& " ORDER BY tblMAFs.[Comp Date Time]; "
Forms!frmSearch!subfrmMAFs.Form.RecordSource = SearchUpdate
Forms!frmSearch!subfrmMAFs.Form.Requery
runQueryBtn_Click_Exit:
Exit Sub
runQueryBtn_Click_Err:
MsgBox ("There is either missing or mismatched search criteria, or an unexpected error occurred. Please check your criteria and try again.")
Resume runQueryBtn_Click_Exit
End Sub