troubleshooting Question

How search for records based on a specified date range

Avatar of SteveL13
SteveL13Flag for United States of America asked on
Microsoft Access
14 Comments1 Solution59 ViewsLast Modified:
I have several unbound field on a "Search Form".  I also have a command button in the footer of the form.  The onclick event of the command button currently has this code:

Dim s As String

    If Nz(Me.txtRecordID, "") <> "" Then
        s = s & " [RecordID] Like '*" & Me.txtRecordID & "*'"
    End If

    If Nz(Me.txtPRn, "") <> "" Then
        If Len(s) > 0 Then
            s = s & " AND "
        End If
        s = s & " [PRn] Like '*" & Me.txtPRn & "*'"
    End If

    If Nz(Me.txtSKUn, "") <> "" Then
        If Len(s) > 0 Then
            s = s & " AND "
        End If
        s = s & " [SKUn] Like '*" & Me.txtSKUn & "*'"
    End If

    If Nz(Me.txtRevisionDate, "") <> "" Then
        If Len(s) > 0 Then
            s = s & " AND "
        End If
        s = s & " [RevisionDate] Like '*" & Me.txtRevisionDate & "*'"
    End If

    If Len(s) > 0 Then
        s = "SELECT * FROM [tblRecords] WHERE " & s & " Order by [RecordID]"
    Else
        s = "SELECT * FROM [tblRecords] " & " Order by [RecordID]"
    End If

    DoCmd.OpenForm "frmRecordsHeader", acNormal, , , acFormReadOnly, acWindowNormal
    
    Forms!frmRecordsHeader.Form!subfrmRecordsDS.Form.RecordSource = s

But on the form I will have two date fields.  txtStartDate and txtEndDate.  So instead of this part of the code:

    If Nz(Me.txtRevisionDate, "") <> "" Then
        If Len(s) > 0 Then
            s = s & " AND "
        End If
        s = s & " [RevisionDate] Like '*" & Me.txtRevisionDate & "*'"
    End If

I want to search where RevisionDate is between txtStartDate and txtEndDate.  How would I handle this?  What would the code:

    If Nz(Me.txtRevisionDate, "") <> "" Then
        If Len(s) > 0 Then
            s = s & " AND "
        End If
        s = s & " [RevisionDate] Like '*" & Me.txtRevisionDate & "*'"
    End If

Be changed to?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros