Need variables for rpt parameters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bonnie A - 27 Dec 2007 16:40 GMT

Hi everyone!!! Hope your holiday season is going smoothly. I'm using A02 on
XP.

I have a query that pulls the data I want from my table. I have a report
with the fields on it. I have a form with no record source and the following
fields:

[BeginDate] and [EndDate] date fields - These should pull All [TDATE] from
[BeginDate] to [EndDate]. If the fields are blank, ignore and move to next
criteria;

[PolicyNumber] text field with input mask '000000' - First I need to 'pad'
the letter 'E' to the front of [PolicyNumber]. Then this should pull All
[POLICY] that equal "E"&[PolicyNumber];

[ReasonCode] List Box with a query as it's row source. Multiselect is set
to simple. - One or more may be selected or none.

Finally, [CSR] List Box with a query as row source. Multiselect set to
simple. - One or more may be selected or none.

If I only put in a start and end date, I want all records from to the dates.
If I have a start and end date and 2 reason codes, I want all records that
meet the date and reason code criteria.

I've seen this done somewhere but don't know where to start.

My query SQL is: SELECT BackDateTable.TDATE, BackDateTable.POLICY,
BackDateTable.PLAN, BackDateTable.EDATE, BackDateTable.TCODE,
BackDateTable.REVCODE, BackDateTable.RCODE, BackDateTable.CSR,
BackDateTable.TOTALS, BackDateTable.REASON, BackDateTable.COMMENTS,
BackDateTable.SUBMIT, BackDateTable.DATESUBMITTED, BackDateTable.APPROVED,
BackDateTable.REVIEWEDBY, BackDateTable.DATEREVIEWED,
BackDateTable.MGMTCOMMENTS
FROM BackDateTable
WITH OWNERACCESS OPTION;

Do I do something here in the query with WHERE or do I put something in the
code that opens the report?

I really think this will be the best way to go so one query and one report
with one form can pull a variety of data. Can someone help?

Thanks in advance for your time and advice.

Signature

Bonnie W. Anderson
Cincinnati, OH

Reply to this Message

Klatuu - 27 Dec 2007 17:11 GMT

Make the query for your report plain vanilla with no filtering. Do the
filtering with the Where argument of the OpenReport method. Then you can
create the Where string based on the selections on your form. Note the
technique I use here where it checks each control on the form for value and
either adds it to the Where string or ingores it if it has no value. Also,
below you will find a function that will return a string from a list box.
You just have to add the field name to it.

Dim strWhere As String
Dim strListWhere As String

If Not IsNull(Me.BeginDate) And Not IsNull(Me.EndDate) Then
strWhere = "[TDate] Between #" & Me.BeginDate & "# And #" &
Me.EndDate & "#"
ElseIf Not IsNull(Me.BeginDate) And IsNull(Me.EndDate) Then
strWhere = "[TDate] >= #" & Me.BeginDate & "#"
ElseIf IsNull(Me.BeginDate) And Not IsNUll(Me.EndDate) Then
strWhere = "[TDate] <= #" & Me.EndDate & "#"
End If

If Not IsNull(Me.PolicyNumber) Then
strWhere = strWhere & AddAnd(strWhere)
strWhere = strWhere & "[PolicyNUmber] = 'E" & Me.PolicyNumber & "'"
End If

strListWhere = BuildWhereString(Me.ReasonCode)
If Len(strListWhere) > 0 Then
strWhere = strWhere & AddAnd(strWhere)
strWhere = strWhere & "[ReasonCode] " & strListWhere
End If

strListWhere = BuildWhereString(Me.CSR)
If Len(strListWhere) > 0 Then
strWhere = strWhere & AddAnd(strWhere)
strWhere = strWhere & "[CSR] " & strListWhere
End If

Docmd.OpenReport "MyReportName", , ,strWhere

Private Function AddAdd(strWhereString As String) As String

If Len(strWhereString) > 0 Then
AddAdd = " And "
Else
AddAnd = vbNullString
End If
End Function

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

Signature

Dave Hargis, Microsoft Access MVP

> Hi everyone!!! Hope your holiday season is going smoothly. I'm using A02 on
> XP.
[quoted text clipped - 40 lines]
>
> Thanks in advance for your time and advice.

Reply to this Message

Bonnie A - 27 Dec 2007 20:10 GMT

Hi there Klatuu,

Thank you very much for the code. I've inserted and tweaked the form field
names and also noticed a few AddAdd's and a few AddAnd's. I made them all
AddAdd's. Also noticed 'BuildWhereCondition' vs 'BuildWhereString' and made
them all 'BuildWhereCondition'. I have compiled and tweaked to an error I
cannot figure out. Can you help? I'm good until I try to build the first
list. Here is my code:

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
Dim strWhere As String
Dim strListWhere As String

If Not IsNull(Forms!fCriteriaTechFromToOneReasonCode!BeginDate) And Not
IsNull(Forms!fCriteriaTechFromToOneReasonCode!EndDate) Then
strWhere = "[TDate] Between #" &
Forms!fCriteriaTechFromToOneReasonCode!BeginDate & "# And #" &
Forms!fCriteriaTechFromToOneReasonCode!EndDate & "#"
ElseIf Not IsNull(Forms!fCriteriaTechFromToOneReasonCode!BeginDate) And
IsNull(Forms!fCriteriaTechFromToOneReasonCode!EndDate) Then
strWhere = "[TDate] >= #" & Forms!fCriteriaTechFromToOneReasonCode!BeginDate
& "#"
ElseIf IsNull(Forms!fCriteriaTechFromToOneReasonCode!BeginDate) And Not
IsNull(Forms!fCriteriaTechFromToOneReasonCode!EndDate) Then
strWhere = "[TDate] <= #" & Forms!fCriteriaTechFromToOneReasonCode!EndDate &
"#"
End If

If Not IsNull(Forms!fCriteriaTechFromToOneReasonCode!PolicyNumber) Then
strWhere = strWhere & AddAdd(strWhere)
strWhere = strWhere & "Forms!fCriteriaTechFromToOneReasonCode!PolicyNumber =
'E" & Forms!fCriteriaTechFromToOneReasonCode!PolicyNumber & "'"
End If

ERROR Yellow highlight on this line:::: strListWhere =
BuildWhereCondition(Forms!fCriteriaTechFromToOneReasonCode!ReasonCodeList)
If Len(strListWhere) > 0 Then
strWhere = strWhere & AddAdd(strWhere)
strWhere = strWhere & "Forms!fCriteriaTechFromToOneReasonCode!ReasonCodeList
" & strListWhere
End If

strListWhere =
BuildWhereCondition(Forms!fCriteriaTechFromToOneReasonCode!CSRList)
If Len(strListWhere) > 0 Then
strWhere = strWhere & AddAdd(strWhere)
strWhere = strWhere & "Forms!fCriteriaTechFromToOneReasonCode!CSRList " &
strListWhere
End If

DoCmd.OpenReport "BackDateVarietyReport", , , strWhere

End Sub

Private Function AddAdd(strWhereString As String) As String

If Len(strWhereString) > 0 Then
AddAdd = " And "
Else
AddAdd = vbNullString
End If
End Function

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select
End Function

Thanks again for your input. I may have to use one selection only for now
so I can get the form rolling. Hope to hear back from you when you have time!

Signature

Bonnie W. Anderson
Cincinnati, OH

> Make the query for your report plain vanilla with no filtering. Do the
> filtering with the Where argument of the OpenReport method. Then you can
[quoted text clipped - 113 lines]
> >
> > Thanks in advance for your time and advice.

Reply to this Message

Klatuu - 27 Dec 2007 20:14 GMT

It would be helpful if you identified the line on which the error occurs and
what the error is.

Also, did you notice the indentation in my code? The way you have it all
left aligned makes it almost impossible to follow. Good indentation really
helps.

Signature

Dave Hargis, Microsoft Access MVP

> Hi there Klatuu,
>
[quoted text clipped - 208 lines]
> > >
> > > Thanks in advance for your time and advice.

Reply to this Message

Bonnie A - 27 Dec 2007 20:58 GMT

Hello again,

Thanks for the quick reply. I did identify the line with this text: ERROR
Yellow highlight on this line:::: It happens on the first strListWhere =
BuildWhereCondition

The error is Runtime '94': Invalid use of Null.
If I click on Debug, the line noted is highlighted.

I copied and pasted your text and didn't realize I would lose the
formatting. Looking at your post I do not see the indentation but will fix
mine and repaste below.

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
Dim strWhere As String
Dim strListWhere As String

If Not IsNull(Forms!fCriteriaTechFromToOneReasonCode!BeginDate) And Not
IsNull(Forms!fCriteriaTechFromToOneReasonCode!EndDate) Then
strWhere = "[TDate] Between #" &
Forms!fCriteriaTechFromToOneReasonCode!BeginDate & "# And #" &
Forms!fCriteriaTechFromToOneReasonCode!EndDate & "#"
ElseIf Not IsNull(Forms!fCriteriaTechFromToOneReasonCode!BeginDate) And
IsNull(Forms!fCriteriaTechFromToOneReasonCode!EndDate) Then
strWhere = "[TDate] >= #" &
Forms!fCriteriaTechFromToOneReasonCode!BeginDate & "#"
ElseIf IsNull(Forms!fCriteriaTechFromToOneReasonCode!BeginDate) And Not
IsNull(Forms!fCriteriaTechFromToOneReasonCode!EndDate) Then
strWhere = "[TDate] <= #" &
Forms!fCriteriaTechFromToOneReasonCode!EndDate & "#"
End If

If Not IsNull(Forms!fCriteriaTechFromToOneReasonCode!PolicyNumber) Then
strWhere = strWhere & AddAdd(strWhere)
strWhere = strWhere &
"Forms!fCriteriaTechFromToOneReasonCode!PolicyNumber = 'E" &
Forms!fCriteriaTechFromToOneReasonCode!PolicyNumber & "'"
End If

THE NEXT LINE IS THE ONE HIGHTLIGHTED - ERROR Yellow highlight on this
line::::
strListWhere =
BuildWhereCondition(Forms!fCriteriaTechFromToOneReasonCode!ReasonCodeList)

If Len(strListWhere) > 0 Then
strWhere = strWhere & AddAdd(strWhere)
strWhere = strWhere &
"Forms!fCriteriaTechFromToOneReasonCode!ReasonCodeList " & strListWhere
End If

strListWhere =
BuildWhereCondition(Forms!fCriteriaTechFromToOneReasonCode!CSRList)

If Len(strListWhere) > 0 Then
strWhere = strWhere & AddAdd(strWhere)
strWhere = strWhere &
"Forms!fCriteriaTechFromToOneReasonCode!CSRList " & strListWhere
End If

DoCmd.OpenReport "BackDateVarietyReport", , , strWhere

End Sub

Private Function AddAdd(strWhereString As String) As String

If Len(strWhereString) > 0 Then
AddAdd = " And "
Else
AddAdd = vbNullString
End If
End Function

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With

strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

End Select
End Function

Sorry to make it harder on you. Was I right to correct what I thought were
typos on AddAdd/And and BuildWhereCondition/String?

Thanks again for your time. It is appreciated!

Bonnie
Cincinnati OH

Reply to this Message

Klatuu - 27 Dec 2007 21:04 GMT

Sorry, I didn't see it before.

The problem is (and I gave you wrong info) that you don't pass the control,
you pass the name of the control. For example:

BuildWhereCondition(Me.ReasonCodeList.Name)

or

BuildWhereCondition("ReasonCodeList")

Signature

Dave Hargis, Microsoft Access MVP

> Hello again,
>
[quoted text clipped - 106 lines]
> Bonnie
> Cincinnati OH

Reply to this Message