Retrieving Data between 2 dates

[falroc]

I have a data base that is used for billing. I have a form called a report selector form. Below is the code used to create the report from the user inputs on the form. What I want to do is add a way for the users to input dates and when they push the intake button they get all the date between the two dates the user/s input.

Private Sub cmdIntake_Click()
    Dim strCountyName As String
    Dim strAgencyStatus As String
    Dim strReferalSource As String
    Dim StrServices As String
    Dim StrPOStatus As String
    Dim strFilter As String
    Dim strStaff As String
    Dim strReferalDate As String
 
' HAVE the report auto open for user
If SysCmd(acSysCmdGetObjectState, acReport, "rptIntakeBilling") <> acObjStateOpen Then
    DoCmd.OpenReport "rptIntakeBilling", acViewPreview
End If
' Build criteria string for CountyName field
    If IsNull(Me.cboCountyName.Value) Then
        strCountyName = "Like '*'"
    Else
        strCountyName = "='" & Me.cboCountyName.Value & "'"
    End If
' Build criteria string for AgencyStatus field
    If IsNull(Me.cboAgencyStatus.Value) Then
        strAgencyStatus = "Like '*'"
    Else
        strAgencyStatus = "='" & Me.cboAgencyStatus.Value & "'"
    End If
' Build criteria string for ReferalSource field
    If IsNull(Me.cboReferalSource.Value) Then
        strReferalSource = "Like '*'"
    Else
        strReferalSource = "='" & Me.cboReferalSource.Value & "'"
    End If
' Build criteria string for Services field
    If IsNull(Me.cboServices.Value) Then
        StrServices = "Like '*'"
    Else
        StrServices = "='" & Me.cboServices.Value & "'"
    End If
' Build criteria string for POStatus field
    If IsNull(Me.cboPOStatus.Value) Then
        StrPOStatus = "Like '*'"
    Else
        StrPOStatus = "='" & Me.cboPOStatus.Value & "'"
    End If
' Build criteria string for POStaff field
    If IsNull(Me.cboStaff.Value) Then
        strStaff = "Like '*'"
    Else
        strStaff = "='" & Me.cboStaff.Value & "'"
    End If
 
' Combine criteria strings into a WHERE clause for the filter
    strFilter = "[CountyName] " & strCountyName & " AND [AgencyStatus] " & strAgencyStatus & " AND [RefCode] " & strReferalSource & " AND [Service] " & StrServices & " AND [POStatus] " & StrPOStatus & " AND [Staff Initials] " & strStaff
' Apply the filter and switch it on
    With Reports![rptIntakeBilling]
        .Filter = strFilter
        .FilterOn = True
        '.txtIntakeReportHeader.Value = _
        '"INTAKE REPORT: " & vbCrLf & "Referal Source: " & Me.cboReferalSource.Value _
        '& vbCrLf & "County: " & Me.cboCountyName.Value _
        '& vbCrLf & "Staff: " & Me.cboStaff.Value
    End With
End Sub

I am havnig a heck of a time trying to get the syntax correct I want to use the ReferalDate field. Which is the only short date in the table used to retrieve all the other data. Any help would be appriciated.



Thanks Falroc

[EManning]

Add 2 text boxes on your form for beginning date and ending date. Then in your code, add coding something like "ReferralDate BETWEEN #" & Forms!frmReportSelector!txtBeginningDate & "# AND #" & Forms!frmReportSelector!txtEndingDate & "#".

Did that help???

[falroc]

Add 2 text boxes on your form for beginning date and ending date. Then in your code, add coding something like "ReferralDate BETWEEN #" & Forms!frmReportSelector!txtBeginningDate & "# AND #" & Forms!frmReportSelector!txtEndingDate & "#".

Did that help???

First of all thank you so much for answering. I am a complete noob. The syntax looks like it will work but I haven't got a clue how to put it into the module. The module I attached was written by someone else and I have mangaged to reverse engineer most of it. But, now I am at a complete loss. I have been trying to teach myself VBA. I am under the gun now as my bosses want to me to have it ready by next week. This is the last thing I have to do but it is really causing me some frustration.