Date Range

[Scott]

Hey,
I inherited a large Access Database. I have almost everything under control
that I need except for this: Users can print a form, and are asked for the
date of the form to be printed [ENTER DATE TO BE PRINTED] via a pop up. I
would like to alter this function to accept a date range, possibly via the
"Between And" functions, but cannot get it done. Any easy help?

 

[Jeanette Cunningham]

Scott,
build a little popup form with 2 text boxes - one for start date and one
for end date.

Use code something like this:

Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
'End date, but no start.
strWhere = YourTableName.TheDate <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
'Start date, but no End.
strWhere = YourTableName.TheDate >= " & Format(Me.txtStartDate,
conDateFormat)
Else
'Both start and end dates.
strWhere = YourTableName.TheDate >= Format(Me.txtStartDate,
conDateFormat) _
& " And " & <=Format(Me.txtEndDate, conDateFormat)
End If
End If

Jeanette Cunningham

 

[Steve Schapel]

Scott,

If you want to continue with the Parameter Query concept (i.e. the user
being prompted for the criteria), you could go to design view of the
query, and replace the existing parameter prompt with something like this:
Between [Enter Start Date] And [Enter End Date]

Many (including me) find this a bit ugly. Instead, you can put a couple
of unbound textboxes on a form, where the user will enter the dates.
Let's suppose the textboxes are named DateFrom and DateTo, in which case
you put the equivalent of this in the criteria of the date field in the
query:
Between [Forms]![NameOfForm]![DateFrom] And [Forms]![NameOfForm]![DateTo]