"Where condition" in OpenReport Action

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i'm trying to limit the records that are printed in a report using the vba
code immediately following. although the msgbox part works, when the code
executes beyond it, the value of strWhere is literally '"' (two single quotes
encapsulating a single double quote)! can anybody help keep me from pulling
my hair out one strand at a time :-)

Private Sub PrintReport_Click()
On Error GoTo Err_PrintReport_Click
Dim response1 As Integer
Dim stDocName As String
Dim strWhere As String
Dim response2 As Integer

If IsNull(Me.Months) = True Or IsNull(Me.StartDate) = True Or
Me.StopDate = #1/1/1899# Then
response2 = MsgBox("You have entered at least one invalid date. Review
and correct your entries.", vbOKOnly + vbCritical, "Error")
GoTo Line1
Else: response1 = MsgBox( _
"Your specifications call for printing a report for patients at their "
& Forms("Tracking Print Filtered By Months").Controls("Months") & _
" months visit which spans the dates " & Forms![Tracking Print Filtered
By Months]![StartDate] & " and " _
& Forms![Tracking Print Filtered By Months]![StopDate] _
& ". If this is correct, click 'OK' to proceed or else click 'Cancel'
and re-enter them.", vbOKCancel + vbInformation, "Attention User")

If response1 = 1 Then
stDocName = "Selected Months Patients on Follow-Up"
strWhere = "[Months] = " & Forms("Tracking Print Filtered By
Months").Controls("Months") & _
[FollowUp] & "Between #" & Forms("Tracking Print Filtered By
Months").Controls("StartDate") & _
"# And #" & Forms("Tracking Print Filtered By
Months").Controls("StopDate") & "#"

DoCmd.OpenReport stDocName, acViewNormal, , strWhere

ElseIf response1 = 2 Then GoTo Line1
End If
Exit_PrintReport_Click:
Exit Sub


Err_PrintReport_Click:
MsgBox Err.description
Resume Exit_PrintReport_Click

End If

Line1:
End Sub
 
Hi Ted,

put

debug.print strWhere

before
DoCmd.OpenReport stDocName, acViewNormal, , strWhere

after you run the code, press CTRL-G to open the debug
window and manually instect the where string that was
constructed. Make sure the fields that you are referring to
are in the RECORDSET of your report.

if you are in the code behind "Tracking Print Filtered By
Months"

instead of (ie:)

strWhere = "[Months] = " & _
Forms("Tracking Print Filtered By
Months").Controls("Months") & _
[FollowUp] & "Between #" & Forms("Tracking Print
Filtered By Months").Controls("StartDate") & _
"# And #" & Forms("Tracking Print Filtered By
Months").Controls("StopDate") & "#"

do this:

strWhere = "[Months] = " & me.Months _
& " and [FollowUp] >= #" & me.StartDate _
& "# And <=#" & me.StopDate & "#"

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
Back
Top