Date range query

[Tony Williams]

I have a report that is based on a query. The user inputs a date range into
a form with this in the query criteria
Between [Forms]![frmdates1]![txtstartdate] And
[forms]![frmdates1]![txtenddate]
The date that the criteria is checking is caled txtmonthlabela. However I
want the query to check also whether there is data in txtmonthlabela as
well. So the query checks if there is data in txtmonthlabela and then only
pulls up records where the value of txtmonthlabela is between the range as
defined above. How do I do this? I assume I will have to put another control
on my prompt form, frmdates1, which the user will need to input the value of
txtmonthlabela but how does the query then check the value is not null and
then filter the records between the range.
Thanks
Tony

 

[Klatuu]

If txtmonthlabela is a field in your table, then records with Null in that
field should not be included in the records returned. If they are, you have
some other problem. If txtmothlabela is not a field in your table, what is
it?

 

[Tony Williams]

Hi there. Yes txtmonthlabela is a field in the table here is the code behind
the OnClick of the button on the prompt form
Private Sub cmdOK_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mmmm\/yyyy\#"

strReport = "rptsense3"
strField = "txtmonthlabel"


If IsNull(Me.txtstartdate) Then
MsgBox "You must enter a start date", vbOKOnly, "Missing Start Date"
Me.txtstartdate.SetFocus
Else

If IsNull(Me.txtenddate) Then
MsgBox "You must enter an end date", vbOKOnly, "Missing End Date"
Me.txtenddate.SetFocus
Else
strWhere = strField & " Between " & Format(Me.txtstartdate,
conDateFormat) _
& " And " & Format(Me.txtenddate, conDateFormat)


If Not IsNull(Me.cmbselectcompany) Then
strWhere = strWhere & " AND cmbCompany = """ & Me.cmbselectcompany & """"
End If

DoCmd.OpenReport strReport, acViewPreview, , strWhere
End If
End If
End Sub

The query on which the report is based also has the statement
Between [Forms]![frmdates1]![txtstartdate] And
[forms]![frmdates1]![txtenddate]
It idid occur to me that I didn't need this in the query because doesn't my
code do the same thing?
Does that help?
Thanks
Tony

If txtmonthlabela is a field in your table, then records with Null in that
field should not be included in the records returned. If they are, you
have
some other problem. If txtmothlabela is not a field in your table, what
is
it?

I have a report that is based on a query. The user inputs a date range
into
a form with this in the query criteria
Between [Forms]![frmdates1]![txtstartdate] And
[forms]![frmdates1]![txtenddate]
The date that the criteria is checking is caled txtmonthlabela. However I
want the query to check also whether there is data in txtmonthlabela as
well. So the query checks if there is data in txtmonthlabela and then
only
pulls up records where the value of txtmonthlabela is between the range
as
defined above. How do I do this? I assume I will have to put another
control
on my prompt form, frmdates1, which the user will need to input the value
of
txtmonthlabela but how does the query then check the value is not null
and
then filter the records between the range.
Thanks
Tony