Error when trying to create report

[pauljkeenan]

Hi Everyone,

I am trying to create a report from the search results displayed in a subform. I have tried to use the openArgs function but it just displayed all records, not the searched ones. So now Im trying a different approach but am having the same amount of success, none.

Here is the code for the create report cmdbutton, the user selects two dates and the report (should) display these records


Private Sub cmdPreviewReport_Click()
On Error GoTo Err_Handler

Dim strSQL1 As String
Dim strOrder1 As String
Dim strWhere1 As String
Dim sCriteria As String

strSQL1 = "SELECT qrySearchVisaSub.App_Date, qrySearchVisaSub.Issue_Date, qrySearchVisaSub.Visa_Type, qrySearchVisaSub.Fee, qrySearchVisaSub.Currency, qrySearchVisaSub.Del_Sanc, qrySearchVisaSub.Nationality, qrySearchVisaSub.DateOfBirth, qrySearchVisaSub.Passport_No, qrySearchVisaSub.Sticker_No " & _
"FROM qrySearchVisaSub"

strWhere1 = "WHERE"

strOrder1 = "ORDER BY qrySearchVisaSub.App_Date;"

If Not IsNull(Me.txtDateFrom) And Not IsNull(Me.txtDateTo) Then
strWhere1 = strWhere1 & " (qrySearchVisaSub.App_Date) between #" & Format(Me!txtDateFrom, "dd-mm-yyyy") & "# and #" & Format(Me!txtDateTo, "dd-mm-yyyy") & "# AND"
End If

'Remove the last AND from the SQL statment
strWhere1 = Mid(strWhere1, 1, Len(strWhere1) - 5)
sCriteria = strSQL1 & " " & strWhere1 & " " & strOrder1

DoCmd.OpenReport "rptDates", acPreview, , sCriteria

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub



But after running I get an error saying
Error 3306
You've written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

Does anybody have any idea what the error is?
Thanks for any help lads, much appreciated.

 

[dhookom]

Your where clause as written contains the full SQL of a query. It should only contain the where clause:
[tt]App_Date between #...[/tt]. There shouldn't be any "Select" or "From" in the where clause of the DoCmd.OpenReport.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pauljkeenan]

Yes I just realised what my mistake was, its working fine now.

Thanks for the advice dhookom, appreciate the help