SQL Quotes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

scott - 02 May 2007 20:10 GMT

I've having problems with my vba sql syntax. When changing my forms
RecordSource with CODE 2 below, I get an error. CODE 1 works fine, but I
can't seem to concatenate the ORDER BY after the WHERE clause varible.

Me.idControl is an integer type. What am I doing wrong?

I should mention that this is an access adp file, but I don't think that
should matter.

CODE 1 ********************************

sSQL = "SELECT myField1, myField2 " & _
"FROM myTable " & _
"WHERE myField1= " & Me.idControl

CODE 2 ********************************

sSQL = "SELECT myField1, myField2 " & _
"FROM myTable " & _
"WHERE myField1= " & Me.idControl & " " & _
"ORDER BY myField2"

Reply to this Message

Douglas J. Steele - 02 May 2007 20:30 GMT

What's the error you're getting? I don't see anything wrong with the code
you've shown.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> I've having problems with my vba sql syntax. When changing my forms
> RecordSource with CODE 2 below, I get an error. CODE 1 works fine, but I
[quoted text clipped - 17 lines]
> "WHERE myField1= " & Me.idControl & " " & _
> "ORDER BY myField2"

Reply to this Message

scott - 02 May 2007 21:16 GMT

Here's the real syntax in CODE2 section and the error. As I stated, if I
drop the "ORDER BY" part as seen in CODE 1, the code runs fine and sets the
record source of a sub form

CODE 1 ****************************

sSQL = "SELECT tblContacts.ContactID, tblContacts.FirstName,
tblContacts.LastName, " & _
"tblContacts.EmailName, tblContacts.ContactTypeID " & _
"FROM tblClients LEFT JOIN tblContacts ON tblClients.CustomerID =
tblContacts.CustomerID " & _
"WHERE tblContacts.ContactID = " & Me.lstContacts

CODE 2 ****************************

sSQL = "SELECT tblContacts.ContactID, tblContacts.FirstName,
tblContacts.LastName, " & _
"tblContacts.EmailName, tblContacts.ContactTypeID " & _
"FROM tblClients LEFT JOIN tblContacts ON tblClients.CustomerID =
tblContacts.CustomerID " & _
"WHERE tblContacts.ContactID = " & Me.lstContacts & " " & _
"ORDER BY tblContacts.LastName"

ERROR ****************************

Run-ime error '107':

The column prefix 'tblContacts' does not match with a table name or
alias name used in the query.

> What's the error you're getting? I don't see anything wrong with the code
> you've shown.
[quoted text clipped - 20 lines]
>> "WHERE myField1= " & Me.idControl & " " & _
>> "ORDER BY myField2"

Reply to this Message

scott - 02 May 2007 21:23 GMT

I got it working. For some reason, if I use the CODE 1 only using "LastName"
in the ORDER BY, it works. But if I use "tblContacts.LastName" in the ORDER
BY, it gives error.

I'm happy it's working, but do you have any idea why this is happening?

CODE 1 ****************************

sSQL = "SELECT tblContacts.ContactID, tblContacts.FirstName,
tblContacts.LastName, " & _
"tblContacts.EmailName, tblContacts.ContactTypeID " & _
"FROM tblClients LEFT JOIN tblContacts ON tblClients.CustomerID =
tblContacts.CustomerID " & _
"WHERE tblContacts.ContactID = " & Me.lstContacts & " " & _
"ORDER BY LastName"
CODE 2 ****************************

sSQL = "SELECT tblContacts.ContactID, tblContacts.FirstName,
tblContacts.LastName, " & _
"tblContacts.EmailName, tblContacts.ContactTypeID " & _
"FROM tblClients LEFT JOIN tblContacts ON tblClients.CustomerID =
tblContacts.CustomerID " & _
"WHERE tblContacts.ContactID = " & Me.lstContacts & " " & _
"ORDER BY tblContacts.LastName"

> What's the error you're getting? I don't see anything wrong with the code
> you've shown.
[quoted text clipped - 20 lines]
>> "WHERE myField1= " & Me.idControl & " " & _
>> "ORDER BY myField2"

Reply to this Message

Aaron Kempf - 02 May 2007 22:44 GMT

MDB is inherently unpredictable

nobody should be using MDB for this reason

use SQL Server, kid

> I got it working. For some reason, if I use the CODE 1 only using "LastName"
> in the ORDER BY, it works. But if I use "tblContacts.LastName" in the ORDER
[quoted text clipped - 45 lines]
> >> "WHERE myField1= " & Me.idControl & " " & _
> >> "ORDER BY myField2"

Reply to this Message