Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 6th, 2004, 10:24 AM
Registered User
 
Join Date: Sep 2004
Location: glasgow, , United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Syntax error on SQL statement

Hi,

This is all new to me and hope that I can explain my problem clearly.
I have been going through the Access 2000 vba book. So far so good. But when trying to apply some of the code to my database, I got the runtime error 3075, syntax error in query expression (missing operator). The debugger points to the following bold line of code in the following sub:

Private Sub cmdFind_Click()
 Dim strSQL As String

If Not EntriesValid Then Exit Sub

If Not BuildSQLString(strSQL) Then
    MsgBox "There was a problem building the SQL string"
    Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("qryExample").SQL = strSQL
DoCmd.OpenForm "frmResults", acNormal
End Sub

I have also created a sql query which will return the results of the search criteria selected by the users. It works fine except for the line above.
Perhaps this is coming from the following function?:

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

strSELECT = "s.*"

strFROM = "tblObjects s "
If chkOwnerID Then
    strFROM = strFROM & "INNER JOIN tblOwners i " & _
    "ON s.OwnerID = i.OwnerID"
    strWHERE = " AND i.OwnerID= " & cboOwnerID
End If

If chkAssetNumber Then
    strWHERE = strWHERE & "AND s.AssetNumber= " & cboAssetNumber
End If

If chkSerialNumber Then
    strWHERE = strWHERE & "AND s.SerialNUmber = " & cboSerialNumber
End If

If chkDescription Then
    strWHERE = strWHERE & "AND s.Description = " & cboDescription
End If

If chkManufacturer Then
    strWHERE = strWHERE & "AND s.Manufacturer = " & cboManufacturer
End If

If chkLocation Then
    strWHERE = strWHERE & "AND s.Location = " & cboLocation
End If

If chkPeriod Then
    strWHERE = strWHERE & "AND s.Period = " & cboPeriod
End If
strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
BuildSQLString = True

End Function

Any help will be appreciated!
Thanks a lot.


Chris
Reply With Quote
  #2 (permalink)  
Old September 7th, 2004, 07:47 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Take a look at all of your SQL statements in your function. If ANY of them pass text and not numbers, then change this

"[MyTextField] = " & cboMyTextField

to this

"[MyTextField] = '" & cboMyTextField & "'"

In other words, you must delimit text variables (i.e. surround them) with single quotes. Numbers get nothing, and dates get # signs.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
"Syntax error in INSERT INTO statement" jtmerchant Classic ASP Databases 1 April 12th, 2006 02:30 PM
Syntax error in INSERT INTO statement mega ASP.NET 1.0 and 1.1 Basics 3 January 12th, 2005 04:30 PM
Syntax error in INSERT INTO statement remnatch Access ASP 9 July 8th, 2004 12:09 PM



All times are GMT -4. The time now is 10:41 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.