Run-time error '3142': Characters found after end of SQL statement. : VBA, Run-time error '2465': MS Access can't find the field '|' referred to in your expression.

May 17, 2008 09:23am pdt

1. PaulHews 283,395
2. GrahamSkan 217,464
3. RobSampson 156,525
4. matthews... 138,463
5. jpaulino 110,988
6. zorvek 107,092
7. rorya 97,060
8. Idle_Mind 81,760
9. ee_rlee 76,307
10. angelIII 74,870
11. webtubbs 57,322
12. danaseaman 49,735
13. capricorn1 49,600
14. FER_G 48,447
15. EDDYKT 48,128

1. Idle_Mind 1,708,296
2. PaulHews 1,416,334
3. AzraSound 1,400,747
4. GrahamSkan 1,255,319
5. vinnyd79 1,242,790
6. Erick37 1,211,724
7. angelIII 1,171,946
8. EDDYKT 1,130,771
9. emoreau 1,058,294
10. TimCottee 958,241
11. Ark 856,902
12. ryancys 854,007
13. BlueDevi... 777,152
14. Shauli 718,021
15. egl1044 698,871

04.23.2008 at 09:59AM PDT, ID: 23347418

	[x] Attachment Details

[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!

8.8

Run-time error '3142': Characters found after end of SQL statement.

Zones: Visual Basic Programming, Microsoft Access Database

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:

qdf1.SQL = strFilterSQL

and gives the error msg:

Run-time error '3142': Characters found after end of SQL statement.

Can anyone tell me why?

Also, the values that come out of cmb_Planner3 are names with a space between the first and last name. Do I need to put a & "[" & cmb_Planner3.Value & "]" to deal with the space?

         
           
             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
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

Related Solutions: Run-time error '2465': MS Access can't find the field '|' referred to in your exp…

Question Stats

Zone: Programming

Question Asked By: ltdanp22

Solution Provided By: LSMConsulting

Participating Experts: 1

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

04.23.2008 at 10:02AM PDT, ID: 21422888

ltdanp22:

Also, when I add a watch to strFilterSQL the value after the code breaks is:

"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

Note that there is no quotation mark at the end. Is this the source of my problem?

04.23.2008 at 10:09AM PDT, ID: 21422972

Rank: Genius

LSMConsulting:

Remove the trailing semicolon from the first call to strFilterSQL:

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"

04.23.2008 at 10:41AM PDT, ID: 21423303

ltdanp22:

Now it gives me the error:

Run-time error 3061: Too few parameters. Expected 6.

Breaks on

Set rst_Var = qdf1.OpenRecordset()

Also, watch shows StrFilterSQL equals:

"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 tbl

Note that it's cut off at the end. Why?

Fixed a few more bugs so the most updated function is below:

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 & "]"
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 & " AND tbl_TotalSLT.ABC = " & cmb_ABC3.Value
ElseIf cmb_PS3.Value <> "" Then
strFilterSQL = strFilterSQL & " P/S = " & cmb_PS3.Value
ElseIf cmb_ABC3.Value <> "" Then
strFilterSQL = strFilterSQL & " tbl_TotalSLT.ABC = " & cmb_ABC.Value
End If

If opt_SortBy = 1 Then
strFilterSQL = strFilterSQL & " ORDER BY [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

04.23.2008 at 10:46AM PDT, ID: 21423343

Rank: Genius

LSMConsulting:

That's odd ... try stepping through your code and printing the results of strFilterSQL as needed. You can do this by issuing this in the Immediate window:

?strFilterSQL

Also, why are you using a QueryDef to open a recordset ... it's much easier to just do so directly:

Dim rst_Var As Recordset
Set rst_Var = Currentdb.OpenRecordset(strFilterSQL)

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

04.23.2008 at 11:13AM PDT, ID: 21423615

ltdanp22:

Never used the intermediate window before. From quick google search here's what I did:

Ctrl + G to bring up window, typed > ?strFilterSQL, ran code.

Nothing happens in intermediate window. Then I tried ?strFilterSQL (without greater than sign). Still nothing. How do I use this window?

Also, Is it possible that it's just that the watch window has a limit on the number of characters it shows?

04.23.2008 at 11:23AM PDT, ID: 21423706

ltdanp22:

so it looks like the watch window does place limit on the length of a string.

I tried setting a test var = to a long string and it was cut off in the watch window around the same place as the SQL statement I'm trying to build. If that's not the problem, what is?

04.23.2008 at 11:35AM PDT, ID: 21423835

Rank: Genius

LSMConsulting:

To use the Immediate window, you must be in Break mode ... place a breakpoint at the beginning of your code, then run your application normally ... when your code runs, you'll be thrown into the VBA environment in Break mode when your code "hits' you breakpoint ... this is where you'd use the Immediate window. You can then step through your code by pressing the F8 key, which will walk you through line-by-line. Use the Immediate window anytime your code changes the value of strFilterSQL, and see where it's being truncated. You're nowhere near the max length of a string, so that's not it.

04.23.2008 at 11:46AM PDT, ID: 21423925

ltdanp22:

Here's the output. Note that there are two small calculations (for Coeff of Var & Holding Cost). Any problems there?

SELECT tblPN.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.[Current LTFCE Inv] * tblPN.MMPrice * 0.2) As [Holding Cost] FROM tblPN INNER JOIN tbl_TotalSLT ON tblPN.PN=tbl_TotalSLT.PN WHERE tblPN.Planner = [JEFF JOHNSON] AND P/S = BW AND tbl_TotalSLT.ABC = A ORDER BY [Coeff of Var] DESC;

04.23.2008 at 01:59PM PDT, ID: 21425344

Rank: Genius

LSMConsulting:

No, the SQL looks okay. Step further through your code, checking again for the value of strFilterSQL each time it's changed, or each time you add/remove something from that string.

04.23.2008 at 03:17PM PDT, ID: 21426025

ltdanp22:

I found 3 of the missing six parameters: I forgot the single quotes around the text values.

I stepped through the code and didn't find any problems. It just steps through sequentially.

Here's the code as it is now:

Private Sub cmd_Var_Click()
'Variable to hold filtered SQL string
Dim strFilterSQL As String
strFilterSQL = "SELECT tblPN.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.[Current LTFCE Inv] * tblPN.MMPrice * 0.2) As [Holding Cost] FROM tblPN INNER JOIN tbl_TotalSLT ON tblPN.PN=tbl_TotalSLT.PN"
If cmb_Planner3.Value <> "None" Or cmb_PS3 <> "None" Or cmb_ABC3 <> "None" Then
strFilterSQL = strFilterSQL & " WHERE"
End If
If cmb_Planner3.Value <> "None" Then
strFilterSQL = strFilterSQL & " tblPN.Planner = " & "'[" & cmb_Planner3.Value & "]'"
End If

If cmb_PS3.Value <> "None" Or cmb_ABC3.Value <> "None" Then
strFilterSQL = strFilterSQL & " AND"
End If

If cmb_PS3.Value <> "None" And cmb_ABC3.Value <> "None" Then
strFilterSQL = strFilterSQL & " P/S = " & "'" & cmb_PS3.Value & "'" & " AND tbl_TotalSLT.ABC = " & "'" & cmb_ABC3.Value & "'"
ElseIf cmb_PS3.Value <> "None" Then
strFilterSQL = strFilterSQL & " P/S = " & "'" & cmb_PS3.Value & "'"
ElseIf cmb_ABC3.Value <> "None" Then
strFilterSQL = strFilterSQL & " tbl_TotalSLT.ABC = " & "'" & cmb_ABC.Value & "'"
End If

If opt_SortBy = 1 Then
strFilterSQL = strFilterSQL & " ORDER BY [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()
Set rst_Var = dbs.OpenRecordset(strFilterSQL)

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

Any ideas why I'm still "missing" 3 parameters. There are only 3 filters (Planner, P/S, and ABC). Why does it say I have 6?

04.23.2008 at 04:07PM PDT, ID: 21426390

Rank: Genius

LSMConsulting:

Generally this means that you've misspelled something ... the simplest way to determine this is to do this:

strFilterSQL = strFilterSQL & ";"
Debug.Print strFilterSQL
Dim dbs As Database

The Debug.Print statement will print the value of strFilterSQL to the Immediate window; you can then copy/paste that value into a new query (open a new query, don't add any tables, then select View - SQL and paste the text from the Immediate window there). Often the query designer will assist you with items like misspelled column names.

Accepted Solution

04.23.2008 at 04:47PM PDT, ID: 21426575

ltdanp22:

K, we're getting close!

When I ran this query from the immediate window...

SELECT tblPN.PN AS PN, tblPN.Desc AS Description, tblPN.[P/S] AS PS, tblPN.ABC AS ABC, (tbl_TotalSLT.[SD LTFCE]/tbl_TotalSLT.[Mean LTFCE]) AS [Coeff of Var], tbl_TotalSLT.[Current LTFCE Inv], (tbl_TotalSLT.[Current LTFCE Inv] * tblPN.MMPrice * 0.2) As [Holding Cost] FROM tblPN INNER JOIN tbl_TotalSLT ON tblPN.PN = tbl_TotalSLT.PN WHERE tblPN.Planner = '[IRMTRAUD STEURER]' AND P/S = 'KW' AND tbl_TotalSLT.ABC = 'B' ORDER BY [Coeff of Var] DESC;

...in Access some pop up boxes popped up asking me to input the following four parameters: P, S, ABC, and Coeff of Var.

So I simply put braces around P/S and realized that I had written tbl_TotalSLT.ABC instead of tblPN.ABC (there's no ABC column in tbl_TotalSLT). Ran it again in Access and it only asked for one parameter: Coeff of Var.

I think the problem is that this is a field name for the column that is being generated in one of the two calculations. Coeff of Var does not exist as a field in either of the two joined tables. Can you verify that I'm not allowed to ORDER BY a column in the recordset? Is there a workaround? Or do I simply need to add the column to one of the two tables?

04.23.2008 at 05:02PM PDT, ID: 21426653

Rank: Genius

LSMConsulting:

You can't order by a calculated field, but if need be you could order by this:

tbl_TotalSLT.[SD LTFCE]/tbl_TotalSLT.[Mean LTFCE]

Which is what your [Coeff of Var] is based on

04.23.2008 at 05:09PM PDT, ID: 21426689

ltdanp22:

One more thing: with regard to your code snippet...

If rst_Var.EOF = False Then
[ out put msgbox and exit function] <--------------how do i add this code?
While Not rst_Var.EOF
lb_Var.AddItem rst_Var(0) '''get the required file
rst_Var.MoveNext
Wend
End If

...

04.23.2008 at 05:17PM PDT, ID: 21426715

Rank: Genius

LSMConsulting:

I'd suggest you do this properly and check both EOF and BOF:

If Not (rst_Var.EOF and rst_Var.BOF) Then
While Not rst_Var.EOF
lb_Var.AddItem rst_Var(0) '''get the required file
rst_Var.MoveNext
Wend
Else
Msgbox "No Data found"
End If

04.23.2008 at 05:21PM PDT, ID: 21426725

ltdanp22:

Whoohoo! Everything works!!! Thank you, thank you, thank you LSM. I wish I could give you more points.

Dan

20080236-EE-VQP-29 / EE_QW_Related_20080208