How search for records based on a specified date range
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:
But on the form I will have two date fields. txtStartDate and txtEndDate. So instead of this part of the code:
I want to search where RevisionDate is between txtStartDate and txtEndDate. How would I handle this? What would the code:
Be changed to?
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?
Last Comment
If Nz(Me.txtStartDate, "") <> "" Then
If Len(s) > 0 Then s = s & " AND "
s = s & "([RevisionDate] >= #" & Me.txtStartDate & "#)"
End If
'I use the following syntax to make certain that if the RevisionDate field contains a time
'component, then all of the records pertaining to that date are included in the results.
If Nz(Me.txtEndDate, "") <> "" Then
If Len(s) > 0 Then s = s & " AND "
s = s & "([RevisionDate] < #" & DateAdd("d", 1, Me.txtEndDate) & "#)"
End If
ASKER
Dale, Your suggested code doesn't seem to be working. I'm getting no records when I click the command button
You just miss some quotes and - to play safe - to apply a forced format of the date expressions:
s = s & " [RevisionDate] BETWEEN #" & Format([txtStartDate], "yyyy\/mm\/dd") & "# AND #" & Format([txtEndDate], "yyyy\/mm\/dd") & "#"
ASKER
Still getting no re3cords with:
If Nz(Me.txtStartDate, "") <> "" Then
If Len(s) > 0 Then s = s & " AND "
s = s & "([RevisionDate] >= #" & Me.txtStartDate & "#)"
End If
If Nz(Me.txtEndDate, "") <> "" Then
If Len(s) > 0 Then s = s & " AND "
s = s & " [RevisionDate] BETWEEN #" & Format([txtStartDate], "yyyy\/mm\/dd") & "# AND #" & Format([txtEndDate], "yyyy\/mm\/dd") & "#"
End If
ASKER
Just for kicks I put this after the last line:
MsgBox s
And got:
MsgBox s
And got:
ASKER
When I entered 9/6/2005 and 4/11/2007
you need to remove the first set of code
If Nz(Me.txtRevisionDate, "") <> "" Then
If Len(s) > 0 Then
s = s & " AND "
End If
s = s & " [RevisionDate] Like '*" & Me.txtRevisionDate & "*'"
End If
get rid of that and just use the code for txtStartDate and txtEndDate
If Nz(Me.txtRevisionDate, "") <> "" Then
If Len(s) > 0 Then
s = s & " AND "
End If
s = s & " [RevisionDate] Like '*" & Me.txtRevisionDate & "*'"
End If
get rid of that and just use the code for txtStartDate and txtEndDate
ASKER
Still no records with: (Note I changed the formatting because I thought that might be the issue. It didn't work either way)
If Nz(Me.txtEndDate, "") <> "" Then
If Len(s) > 0 Then s = s & " AND "
' s = s & " [RevisionDate] BETWEEN #" & Format([txtStartDate], "yyyy\/mm\/dd") & "# AND #" & Format([txtEndDate], "yyyy\/mm\/dd") & "#"
s = s & " [RevisionDate] BETWEEN #" & Format([txtStartDate], "mm\/dd\/yyyy") & "# AND #" & Format([txtEndDate], "mm\/dd\/yyyy") & "#"
End If
Your message clearly shows your error.
I assumed your second code, thus:
I assumed your second code, thus:
If Nz(Me.txtStartDate, "") <> "" And Nz(Me.txtEndDate, "") <> "" Then
If Len(s) > 0 Then
s = s & " AND "
End If
s = s & " [RevisionDate] BETWEEN #" & Format([txtStartDate], "yyyy\/mm\/dd") & "# AND #" & Format([txtEndDate], "yyyy\/mm\/dd") & "#"
End If
ASKER
I don't know what I'm doing wrong. Please see attached test file. Enter 9/6/2005 and 4/11/2007 in the bottom two fields. Then click [Search]. Nothing shows up in the results form. Two records should show up.
Date-Range-Test.accdb
Date-Range-Test.accdb
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Redesigned the forms and used your latest code. All is well. Thank you.
You are welcome!
/gustav
/gustav
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
Open in new window