SQL Criteria for report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Question Boy - 24 Mar 2008 22:14 GMT

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

Reply to this Message

Jeanette Cunningham - 24 Mar 2008 22:20 GMT

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,
>
[quoted text clipped - 13 lines]
>
> QB

Reply to this Message

Question Boy - 25 Mar 2008 00:09 GMT

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
[quoted text clipped - 40 lines]
> >
> > QB

Reply to this Message

Jeanette Cunningham - 25 Mar 2008 00:42 GMT

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,
>
[quoted text clipped - 54 lines]
>> >
>> > QB

Reply to this Message