Parameter Query Help Needed

[Jonathan Smith]

I am using Access 2002, with the Access 2000 File Format.

I have an Unbound Form (frm_TherapistAnalysis) with the following fields:

cmbProviders
txtStartDate
txtEndDate

I am using the the Form to allow the End User to Select the appropriate
Provider, Start Date and End Date for the Therapist Analysis Report. I had
the Form and Report functioning correctly, using only the Start and End
Date Selections, but when I add the Provider selection I get errors.

HELP Please.


Private Sub OK_Click()

Dim strReport As String
Dim strProvider As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yy\#"

strReport = "rpt_TherapistAnalysis"
strField = "DateofService"

If IsNull(Me.cmbProvider) Then
MsgBox "Please Select a Provider from the Drop-Down List"
Else
strProvider = "me.cmbProvider"

End If

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strProvider And strField & " < " & Format
Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strProvider And strField & " > " & Format
(Me.txtStartDate, conDateFormat)
Else
strWhere = strProvider And strField & " Between" & Format
(Me.txtStartDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)
End If
End If



DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub

 

[Michael Keating]

Hi,

I see a problem with the WHERE condition you're creating .. I'll comment it
inline on the first occurrence.

I am using Access 2002, with the Access 2000 File Format.

I have an Unbound Form (frm_TherapistAnalysis) with the following fields:

cmbProviders
txtStartDate
txtEndDate

I am using the the Form to allow the End User to Select the appropriate
Provider, Start Date and End Date for the Therapist Analysis Report. I had
the Form and Report functioning correctly, using only the Start and End
Date Selections, but when I add the Provider selection I get errors.

HELP Please.


Private Sub OK_Click()

Dim strReport As String
Dim strProvider As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yy\#"

strReport = "rpt_TherapistAnalysis"
strField = "DateofService"

If IsNull(Me.cmbProvider) Then
MsgBox "Please Select a Provider from the Drop-Down List"
Else
strProvider = "me.cmbProvider"

End If

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strProvider And strField & " < " & Format
Me.txtEndDate, conDateFormat)

maybe this should be

strWhere = "(ProviderFieldName=" & strProvider & ") And (" &
strField & " < " & Format(Me.txtEndDate, conDateFormat) & ")"

although the brackets aren't essential, I think they make things more clear

End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strProvider And strField & " > " & Format
(Me.txtStartDate, conDateFormat)
Else
strWhere = strProvider And strField & " Between" & Format
(Me.txtStartDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)
End If
End If



DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub

Good luck with your concatenation

HTH

MFK.

 

[Reggie]

Jonathan, Looks like you're trying to pass the value selected in the combobox, but you are actually
passing the string "me.cmbProvider". Remove the quotes to pass the value selected in the combobox
into your string variable:

strProvider = Me.cmbProvider

Hope this helps