Do not use on any
shared computer
July 24, 2008 01:39pm pdt
 
[x]
Attachment Details

Run-time error '2465': MS Access can't find the field '|' referred to in your expression.

Tags: VBA, Run-time error '2465': MS Access can't find the field '|' referred to in your expression.
I am building a query based on four user defined filters and outputting that into a list box.

When I run the code, the compiler breaks at:

strFilterSQL = strFilterSQL & " tblPN.Planner = " & [cmb_Planner3.Value]

and gives the error msg:

Run-time error '2465': MS Access can't find the field '|' referred to in your expression.

Can anyone tell me why?
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
Private Sub cmd_Var_Click()
'Variable to hold filtered SQL string
Dim strFilterSQL As String
strFilterSQL = "SELECT tbl_TotalSLT.PN, tblPN.Desc AS Description, tblPN.[P/S], tblPN.ABC, tbl_TotalSLT.[SD LTFCE]/tbl_TotalSLT.[Mean LTFCE] AS [Coeff of Var], tbl_TotalSLT.[Current LTFCE Inv], tbl_TotalSLT.[Holding LTFCE] FROM tblPN INNER JOIN tbl_TotalSLT ON tblPN.PN=tbl_TotalSLT.PN;"
If cmb_Planner3.Value <> "" Or cmb_PS3 <> "" Or cmb_ABC3 <> "" Then
    strFilterSQL = strFilterSQL & " WHERE"
End If
 
If cmb_Planner3.Value <> "" Then
    strFilterSQL = strFilterSQL & " tblPN.Planner = " & [cmb_Planner3.Value]'<------- BREAKS ON THIS LINE-----------
End If
 
If cmb_PS3.Value <> "" Or cmb_ABC3.Value <> "" Then
    strFilterSQL = strFilterSQL & " AND"
End If
 
If cmb_PS3.Value <> "" And cmb_ABC3.Value <> "" Then
    strFilterSQL = strFilterSQL & " P/S = " & [cmb_PS3.Value] & " ABC = " & [cmb_ABC3.Value]
ElseIf cmb_PS3.Value <> "" Then
    strFilterSQL = strFilterSQL & " P/S = " & [cmb_PS3.Value]
ElseIf cmb_ABC3.Value <> "" Then
    strFilterSQL = strFilterSQL & " ABC = " & [cmb_ABC.Value]
End If
 
If opt_SortBy = 1 Then
    strFilterSQL = strFilterSQL & " ORDER BY COV [Coeff of Var] DESC"
ElseIf opt_SortBy = 2 Then
    strFilterSQL = strFilterSQL & " ORDER BY [Holding LTFCE] DESC"
End If
 
strFilterSQL = strFilterSQL & ";"
    
Dim dbs As Database
Set dbs = DBEngine(0).Databases(0)
Dim qdf1 As DAO.QueryDef
Dim rst_Var As Recordset
Set qdf1 = dbs.CreateQueryDef("")
qdf1.SQL = strFilterSQL
Set rst_Var = qdf1.OpenRecordset()
 
If rst_Var.EOF = False Then
While Not rst_Var.EOF
lb_Var.AddItem rst_Var(0) '''get the required file
  rst_Var.MoveNext
Wend
End If
 
rst_TotalSLTSubset.Close
qdf1.Close
dbs.Close
End Sub
Start your free trial to view this solution
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

Question Stats
Zone: Programming
Question Asked By: ltdanp22
Solution Provided By: ee_rlee
Participating Experts: 3
Solution Grade: A
Views: 140
Translate:
Loading Advertisement...
 
[+][-]Accepted Solution by ee_rlee

Rank: Guru

Accepted Solution by ee_rlee:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Assisted Solution by DatabaseMX

Rank: Genius

Assisted Solution by DatabaseMX:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by DatabaseMX

Rank: Genius

Expert Comment by DatabaseMX:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by mshahzadhaider
Expert Comment by mshahzadhaider:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by DatabaseMX

Rank: Genius

Expert Comment by DatabaseMX:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
20080723-EE-VQP-34 / EE_QW_2_20070628