Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
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
 
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




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





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