error; [2465] Mircosoft Access cant find the field '|" referred to in your expression

[dowlingm815]

the following code is generating an error; [2465] Mircosoft Access cant find the field '|" referred to in your expression"

any suggestions would be appreciated.

Private Sub Expire_Date_Qry_Parameter_AfterUpdate()
  On Error GoTo Err_Hndlr
 
    Forms!F_Contract_Mgt_Entry!Expire_Date_Temp_Parameter = Forms!F_Contract_Mgt_Entry!Expire_Date_Qry_Parameter
    DoCmd.OutputTo acOutputQuery, stDocName, [Excel 97 - Excel 2003 Workbook (*.xls)], AutoStart
 
   MsgBox "Task Complete!"
 
 
Exit_Expire_Date_Qry_Parameter_AfterUpdate:
    Exit Sub
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Expire_Date_Qry_Parameter_AfterUpdate()"
End Sub
 

[Jim Doherty]

@dowlingm815
There is nothing in your code to suggest what stDocName is.
Is the query that presumably is stDocName getting its value from the form control Expire_Date_Temp_Parameter? If so what does the query syntax say?

[dowlingm815]

stDocName is a global variable. Depending the client's response, stDocName receives a Query Export, as in this case, or Report Preview. The Query receives date parameters from the form.

unfortunately, i don't have access to the code until monday.

[NeoPa]

Posting the full error message is helpful. Identifying the line the error occurred on would be even more helpful. I guess it's probably line #5, but confirmation would be good.

[NeoPa]

If this is indeed the case then I expect the problem is within your query rather than the VBA code.

What happens when you try running the query normally (to show on screen) at the point this crashes (I'm talking about suspending the code to do the test)?

[dowlingm815]

the non-vba query has a field name "ExpirationDate" that will receive the value from the form's field. the criteria is "<[Forms]![F_Contract_Mgt_Entry]![Expire_Date_Qry_Parameter]"

everything looks fine to me. funny thing is the query is called twice with two separate conditions. the first condition generates a report, the other condition, this condition, generates a query to be exported.

[NeoPa]

Could you explain that second paragraph. I couldn't work out what you were trying to say.

If it helps, drop the relevant code in.

[dowlingm815]

The application has a screen that offers three choices. When the second or third choice is selected, the event procedure kicks off a query that receives a date field from the screen. there are two different date fields to coordinate with the user's selection.

the code has a temp field that receives the date value and passes it to a query. when the second choice is selected, the query runs without errors. when the third choice is selected, the following error code is generated:

[2465] Mircosoft Access cant find the field '|" referred to in your expression"

when the query has the parameter removed, no error is generated. again, what is brothersome, it basically runs the same code for the second selection, and generates an error on the third run.

the entire code is as follows:

 
Option Compare Database
 
 
Private Sub Contract_Mgt_Rpt_by_Contract_Status_Click()
 
On Error GoTo Err_Hndlr
 
 
    Forms!F_Contract_Mgt_Entry!Expire_Date_Temp_Parameter = Null
    Forms!F_Contract_Mgt_Entry!Expire_Date_Qry_Parameter = Null
    Forms!F_Contract_Mgt_Entry!Expire_Date_Rpt_Parameter = Null
    Forms!F_Contract_Mgt_Entry!Expire_Date_Rpt_Parameter.Visible = True
    Forms!F_Contract_Mgt_Entry!Expire_Date_Qry_Parameter.Visible = False
    stDocName = "R_Contract_Status"
 
 
Exit_Contract_Mgt_Rpt_by_Contract_Status_Click:
    Exit Sub
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Contract_Mgt_Rpt_by_Contract_Status_Click()"
 
End Sub
 
Private Sub Expire_Date_Rpt_Parameter_AfterUpdate()
 
On Error GoTo Err_Hndlr
 
 
    Forms!F_Contract_Mgt_Entry!Expire_Date_Temp_Parameter = Forms!F_Contract_Mgt_Entry!Expire_Date_Rpt_Parameter
    DoCmd.OpenReport stDocName, acPreview
 
 
Exit_Expire_Date_Rpt_Parameter_AfterUpdate:
    Exit Sub
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Expire_Date_Rpt_Parameter_AfterUpdate()"
End Sub
 
Private Sub Expire_Date_Rpt_Parameter_Enter()
 On Error GoTo Err_Hndlr
 
 Forms!F_Contract_Mgt_Entry!Expire_Date_Rpt_Parameter = Null
 
Exit_Expire_Date_Rpt_Parameter_Enter:
    Exit Sub
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Expire_Date_Rpt_Parameter_Enter()"
 
End Sub
 
Private Sub Expire_Date_Qry_Parameter_AfterUpdate()
  On Error GoTo Err_Hndlr
 
    Forms!F_Contract_Mgt_Entry!Expire_Date_Temp_Parameter = Forms!F_Contract_Mgt_Entry!Expire_Date_Qry_Parameter
    DoCmd.OutputTo acOutputQuery, stDocName, [Excel 97 - Excel 2003 Workbook (*.xls)], AutoStart
 
   MsgBox "Task Complete!"
 
 
Exit_Expire_Date_Qry_Parameter_AfterUpdate:
    Exit Sub
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Expire_Date_Qry_Parameter_AfterUpdate()"
End Sub
 
Private Sub Expire_Date_Qry_Parameter_Enter()
 On Error GoTo Err_Hndlr
 
 Forms!F_Contract_Mgt_Entry!Expire_Date_Rpt_Parameter = Null
 
Exit_Expire_Date_Qry_Parameter_Enter:
    Exit Sub
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Expire_Date_Qry_Parameter_Enter()"
End Sub
 
Private Sub ExportContractData_Click()
 
 On Error GoTo Err_Hndlr
 
 Forms!F_Contract_Mgt_Entry!Expire_Date_Temp_Parameter = Null
 Forms!F_Contract_Mgt_Entry!Expire_Date_Qry_Parameter = Null
    Forms!F_Contract_Mgt_Entry!Expire_Date_Rpt_Parameter.Visible = False
    Forms!F_Contract_Mgt_Entry!Expire_Date_Qry_Parameter.Visible = True
    stDocName = "Q_2_Recs_<_ExpireDateParameter"
 
Exit_ExportContractData_Click:
    Exit Sub
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "ExportContractData_Click()"
End Sub
 
 
 

[dowlingm815]

for some odd reason, i believe it is the following statement in the code:

 
 DoCmd.OutputTo acOutputQuery, stDocName, [Excel 97 - Excel 2003 Workbook (*.xls)], AutoStart
 
 

[dowlingm815]

Got it...the solution the error code is the syntax for the output...the code should be:

DoCmd.OutputTo acQuery, stDocName, "MicrosoftExcelBiff8(*.xls)", "", True, "", 0

** Edit **
The explanation is that quotes are needed around the format parameter. Changing the value of the parameter (as shown here) was not relevant.

[dowlingm815]

case closed...

[gershwyn]

@dowlingm815
You want quotes around the format parameter:

DoCmd.OutputTo acOutputQuery, stDocName, "Excel 97 - Excel 2003 Workbook (*.xls)", AutoStart

I don't know if that will fix everything or not, but I think Access is getting confused by the brackets.

EDIT: I was too slow, you solved it yourself. :)