Searching/Filtering Form by Date Calendar: Week Beginning...

[Mela]

Hi,

I have a tracking database where documents are being tracked for receipt and
signature. I'd like the primary users to be able to update information in
specific records by filtering/searching records from a popup calendar for
data entered in a specific week. For example, "find all all records entered
for week beginning 8/17/09". The key field which would search for the
relevant dates is ""Date Submitted for Signature" in TblContracts.

I can create a popup calendar form but how would I create code to tell it to
find all records submitted for signature in a specific week beginning on a
Monday? I only want to limit the search query to a 7-day period.

Thank you.

 

[Allen Browne]

We assume you have a form bound to this table, and it has unbound control
named FromDate, where you enter the start of the week. You would filter your
form like this:

Const strcJetDate = "\#mm\/dd\/yyyy\#"
If Me.dirty Then Me.dirty = False 'Save first
With Me.FromDate
If IsDate(.Value) Then
strWhere = "([Date Submitted for Signature] >= " & _
Format(.Value, strcJetDate) & _
") AND ([Date Submitted for Signature] < " & _
Format(.Value + 8, strcJetDate) & ")"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "Enter a date"
End If
End With