SQL Quotes

[scott]

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"

 

[Douglas J. Steele]

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

 

[scott]

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.

 

[scott]

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"

 

[Aaron Kempf]

MDB is inherently unpredictable

nobody should be using MDB for this reason

use SQL Server, kid