Dynamic Where for Report from Query

  • Thread starter Thread starter Brendan
  • Start date Start date
B

Brendan

After the user clicks through several forms, a dynamic where clause is
created and saved to a textbox (txt_Where). This usually looks
something like: Chapter.ID in (5,8) AND ZIP = '21114'. There are a
total of 5 possible fields that could be in the where clause. The
form that calls the report is called frm_Report. Here lies the
problem. The acutal Report (Report_ROSTER) works well from a query
with no where clause. Im trying to use a command button to call the
report and use the where clause that was generated and in the field
txt_Where

So when I try:

Private Sub cmd_PreviewReport_Click()
Dim stDocName As String
Dim strCriteria As String

strCriteria = """" & txt_Where.Value & """"
stDocName = "CHAPTER Roster"
DoCmd.OpenReport stDocName, acPreview, , strCriteria
End Sub

The report opens, but the Where clause doesnt work. If I change the
query that the report is generated from to have "WHERE CHAPTER.ID in
(5,8) AND ZIP = '21114'" Then it works. This should be easy, but I am
definately missing something. Any help would be appreciated. Thanks
 
Try remove all the extra quotes:
strCriteria = Me.txt_Where

Also, is the a reason why you include the table name of "Chapter"? Do you
have more than one ID field in the report's record source?
 
Back
Top