SQL Criteria for report

[Question Boy]

Hello,

I have used a form as a criteria to retrict the results of the SQL statement
used for my report. It limits the date range


Between [Forms]![frm_Report Date Selection].[Start] And [Forms]![frm_Report
Date Selection].[End]

Now I would like to add one more element and don't know how. I would like
it to use the Between statement when the form is open otherwise return all
the data regardless of date. i tried encapsulating each criteria in an Nz()
but it did not work. How can I do this?

QB

 

[Jeanette Cunningham]

Hi,
here is an example of building a where clause for start and end date and it
allows either start or end to be empty.

Dim strWhere As String
'if not in US, format dates to US date format
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtStartDate) Then
'End date, but no start date
If Not IsNull(Me.txtEndDate) Then
strWhere = YourTableName.TheDate <= " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
'Start date, but no End date
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

 

[Question Boy]

Jeanette,

You'll have to excuse my ignorance, but where do I place this code? I was
previously working at the SQL level and this is VBA so what event is used to
run it?

QB

Hi,
here is an example of building a where clause for start and end date and it
allows either start or end to be empty.

Dim strWhere As String
'if not in US, format dates to US date format
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtStartDate) Then
'End date, but no start date
If Not IsNull(Me.txtEndDate) Then
strWhere = YourTableName.TheDate <= " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
'Start date, but no End date
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

Hello,

I have used a form as a criteria to retrict the results of the SQL
statement
used for my report. It limits the date range


Between [Forms]![frm_Report Date Selection].[Start] And
[Forms]![frm_Report
Date Selection].[End]

Now I would like to add one more element and don't know how. I would like
it to use the Between statement when the form is open otherwise return all
the data regardless of date. i tried encapsulating each criteria in an
Nz()
but it did not work. How can I do this?

QB

 

[Jeanette Cunningham]

Sorry, I was thinking you were using code behind the form, which gives you
more flexibility.

Use 2 saved queries - the one with the criteria you posted using between -
TheFirstQuery
and the second query without any criteria for the date field -
TheSecondQuery

On the button that opens the report, put code like this in its click event:
Dim strSQL as String
If CurrentProject.AllForms("frm_Report Date Selection").IsLoaded Then
strSQL = "TheFirstQuery"
Else
strSQL = "TheSecondQuery"
End if

Replace TheFirstQuery and TheSecondQuery with the actual names of the 2
queries.

Jeanette Cunningham

Jeanette,

You'll have to excuse my ignorance, but where do I place this code? I was
previously working at the SQL level and this is VBA so what event is used
to
run it?

QB

Hi,
here is an example of building a where clause for start and end date and
it
allows either start or end to be empty.

Dim strWhere As String
'if not in US, format dates to US date format
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtStartDate) Then
'End date, but no start date
If Not IsNull(Me.txtEndDate) Then
strWhere = YourTableName.TheDate <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
'Start date, but no End date
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

Hello,

I have used a form as a criteria to retrict the results of the SQL
statement
used for my report. It limits the date range


Between [Forms]![frm_Report Date Selection].[Start] And
[Forms]![frm_Report
Date Selection].[End]

Now I would like to add one more element and don't know how. I would
like
it to use the Between statement when the form is open otherwise return
all
the data regardless of date. i tried encapsulating each criteria in an
Nz()
but it did not work. How can I do this?

QB