Problem with Date / Run-time error

[china99boy]

Below is a copy of my code. I keep getting a run-time error 2465 "Microsoft Office Access can't find the '|' referred to in your expression" at the portion highlighted in red. What am I doing wrong? Please help me solve this error.


Option Compare Database
Option Explicit

Private Sub cmdExportAutomation_Click()
On Error GoTo err_Handler

MsgBox ExportRequest, vbInformation, "Finished"
Application.FollowHyperlink CurrentProject.Path & "\AOSummary.xls"

exit_Here:
Exit Sub
err_Handler:
MsgBox Err.Description, vbCritical, "Error"
Resume exit_Here
End Sub



Public Function ExportRequest() As String
On Error GoTo err_Handler

Dim dbs As Database
Dim qdf As QueryDef
Dim frm As Form
' Set database variable to current database.
Set dbs = CurrentDb()
Set frm = Forms!AOSummaryReportForm
' Open QueryDef object.
Set qdf = dbs.QueryDefs("AOSummary")
' Set parameters for query based on values entered
' in AOSummaryReportForm.
qdf.Parameters("Forms!AOSummaryReportForm!StartDate") _
= frm!StartDate
qdf.Parameters("Forms!AOSummaryReportForm!EndDate") _
= frm!EndDate

' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim rst As DAO.Recordset
Dim strSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabOne As Byte = 1
Const cStartRow As Byte = 2
Const cStartColumn As Byte = 1

DoCmd.Hourglass True


' set to break on all errors
Application.SetOption "Error Trapping", 0

' start with a clean file built from the template file
sTemplate = CurrentProject.Path & "\AOSummaryTemplate.xls"
sOutput = CurrentProject.Path & "\AOSummary.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabOne)
strSQL = "SELECT * FROM AOSummary " & _
"WHERE APPROACH.Date_Field Between #" & [StartDate] & "# AND #" & [EndDate] & "#"


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)


' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow
If Not rst.BOF Then rst.MoveFirst
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.lblMsg.Caption = "Exporting record #" & lRecords & " to AOSummary.xls"
Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop

ExportRequest = "Total of " & lRecords & " rows processed."
Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."

exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

err_Handler:
ExportRequest = Err.Description
Me.lblMsg.Caption = Err.Description
Resume exit_Here

End Function

 

[MarkK]

It is unusual that the source table in the FROM clause, namely "AOSummary", is not used to qualify "Date_Field" in the WHERE clause.
What is "APPROACH" ?

 

[china99boy]

"APPROACH" is the name of my Table. "AOSummary" is the parameterized query. For the query, right now I use the Between statement which prompt the user to input "StartDate" and "EndDate" It works fine when I run the query by itself. I have a form name " frmExcelExport". The user should click a button on this form that should open the "AOSummaryReportForm" to enter the StartDate and EndDate. The information is passed to the query "AOSummary". Then from there, the information from the query is sent to excel. Without parameters, it works perfectly, but that shows all records in my database. So I am trying to get the parameter to work with my codes. I got the codes to transfer the query from a sample database, but it did not have a query with a parameter as an example, so I am really clueless. Thanks for any assistance.

 

[MarkK]

More explicitly perhaps, you cannot reference a table in a WHERE clause that does not appear in the FROM clause. In your situation Date_Field is a member of AOSummary, not Approach.