Syntax Error in SQL (1 Viewer)

Z

Zydeceltico

Registered User.
Local time
Today, 07:59
Joined
Dec 5, 2017
Messages
843
Is anyone able to find the syntax error in this code? :

Code: 
    Set tmpRS = CurrentDb.OpenRecordset ("SELECT tblParts.Part_ID, tblParts.PartType, tblParts.PartDWG " & _
                    "FROM tblParts" & _
                    "WHERE (((tblParts.Part_ID)=intPartID))" & _
                    "GROUP BY tblParts.Part_ID" & _
                    "HAVING ((tblParts.Part_ID = ' "& Me.txtPartType_FK & " ')  " & _
                    "And (tblParts.PartType = ' "& Me.txtPartName & " ' ) " & _
                    "And(tblParts.PartDWG = ' " & Me.txtPartDrawing " '))")

I can't find it and it's driving me nuts.

Thanks!

Tim
 
Z

Zydeceltico

Registered User.
Local time
Today, 07:59
Joined
Dec 5, 2017
Messages
843
This much of works:

Code: 
SELECT tblParts.Part_ID, tblParts.PartType, tblParts.PartDWG
FROM tblParts
WHERE (((tblParts.Part_ID)=[intPartID]))
GROUP BY tblParts.Part_ID, tblParts.PartType, tblParts.PartDWG

But the HAVING clause is not playing well:

Code: 
HAVING (((tblParts.PartType) = ' " & Me.txtPartName & " ') AND ((tblParts.PartDWG) = ' " & Me.txtPartDrawing " ' ) AND ((tblParts.Part_ID) = ' " & Me.txtPartType_FK & " ' ))
 
essaytee

essaytee

Need a good one-liner.
Local time
Today, 21:59
Joined
Oct 20, 2008
Messages
512
Could it be a space at either end of your variables (form controls).
 
Z

Zydeceltico

Registered User.
Local time
Today, 07:59
Joined
Dec 5, 2017
Messages
843
essaytee said:
Could it be a space at either end of your variables (form controls).
Click to expand...

I was missing an ampersand.

Code: 
    Set tmpRS = CurrentDb.OpenRecordset(" SELECT tblParts.Part_ID, tblParts.PartType, tblParts.PartDWG " & _
                    " FROM tblParts " & _
                    " WHERE (((tblParts.Part_ID)=intPartID)) " & _
                    " GROUP BY tblParts.Part_ID, tblParts.PartType, tblParts.PartDWG " & _
                    " HAVING ((tblParts.Part_ID = ' " & Me.txtPartType_FK & " ') " & _
                    " And (tblParts.PartType = ' " & Me.txtPartName & " ' ) " & _
                    " And(tblParts.PartDWG = ' " & Me.txtPartDrawing [COLOR="Red"][B]& [/B][/COLOR]" ' ) ) ")
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 12:59
Joined
Sep 21, 2011
Messages
14,256
If you build and Debug.Print the sql string instead of placing it directly into the OpenRecordSet function, you will generally see your errors?

I am also surprised that it would match with spaces either side of the text?
 
D

Dreamweaver

Well-known member
Local time
Today, 12:59
Joined
Nov 28, 2005
Messages
2,466
' " & Me.txtPartDrawing & " '
Use
'" & Me.txtPartDrawing & "'"

If your not using an ID field I would use
""" & Me.txtPartDrawing & """"
Or
& Chr(34) & Me.txtPartDrawing & Chr(34) &

If somebody types "Shouldn't" I.E. ' It will error
 
Z

Zydeceltico

Registered User.
Local time
Today, 07:59
Joined
Dec 5, 2017
Messages
843
essaytee said:
Double quoting, single quoting, make it easier on yourself, write a function to do it. A sample can be found here (this site):
Click to expand...

Thank you. Yeah - all of the quoting makes me blind. :)

For the minor task I need to accomplish it was a prime opportunity to learn a bunch about recordsets. :)

Taking a look at your link also - thanks again.
 
Z

Zydeceltico

Registered User.
Local time
Today, 07:59
Joined
Dec 5, 2017
Messages
843
Gasman said:
If you build and Debug.Print the sql string instead of placing it directly into the OpenRecordSet function, you will generally see your errors?

I am also surprised that it would match with spaces either side of the text?
Click to expand...

Still learning my way around debugging. Thank you for the reminder that that tool is available. I forget. The "novice" in my title is for real. :)
 
P

plog

Banishment Pending
Local time
Today, 06:59
Joined
May 11, 2011
Messages
11,643
After you fix the syntax error that thing still shouldn't work. You are improperly grouping your data. Here's the rule:

In a query with a GROUP BY, every field in the SELECT must either be in the GROUP BY or be acted on by an aggregate function (SUM, COUNT, MAX, etc.).

You have 3 fields in the SELECT, 1 in the GROUP BY and no aggregate functions. You need to better define what it is you are trying to do with that query.

Additionally, you shouldn't use a HAVING. All that criteria should be in the WHERE. WHERE acts on individual records, HAVING acts on aggregate data. That means HAVING should be comprised of fields with aggregate functions (SUM, COUNT, MAX, etc.)
 
Z

Zydeceltico

Registered User.
Local time
Today, 07:59
Joined
Dec 5, 2017
Messages
843
plog said:
After you fix the syntax error that thing still shouldn't work. You are improperly grouping your data. Here's the rule:

In a query with a GROUP BY, every field in the SELECT must either be in the GROUP BY or be acted on by an aggregate function (SUM, COUNT, MAX, etc.).

You have 3 fields in the SELECT, 1 in the GROUP BY and no aggregate functions. You need to better define what it is you are trying to do with that query.

Additionally, you shouldn't use a HAVING. All that criteria should be in the WHERE. WHERE acts on individual records, HAVING acts on aggregate data. That means HAVING should be comprised of fields with aggregate functions (SUM, COUNT, MAX, etc.)
Click to expand...

I learned the truth of what you're saying around midnight last night - especially about the HAVING - and somehow stumbled upon how to call and pass the values from the fields of a Recordset which is going to get me where I need to be in a much simpler fashion.

Yeah - I dove into the deep water way over my head with the above SQL statement.
 
Z

Zydeceltico

Registered User.
Local time
Today, 07:59
Joined
Dec 5, 2017
Messages
843
plog said:
After you fix the syntax error that thing still shouldn't work.
Click to expand...

Does this look like it will work?

Code: 
Dim QCDB as DAO.Database
Dim rsPart As DAO.Recordset
DimstrSQL as String

Set QCDB = CurrentDb
Set rsPart = QCDB.OpenRecordset("tblParts")

strSQL = "SELECT tblParts.Part_ID, tblParts.PartType, tblParts.PartDWG" & _
	"FROM tblParts" & _
	"WHERE ((tblParts.Part_ID)=intPartID)"
where intPartID is a public variable that I know already has the value in it that I want to use.

QUESTION: will the above SQL recognize the way I call intPartID or do I need to call it differently? Through a function? Ampersands?

And then I would assign recordset column values to controls like:

Me.txtPartType_FK = rsPart.Field(0).Value
Me.txtPartName = rsPart.Field(1).Value
Me.txtPartDrawing = rsPart.Field(2).Value

That seems much simpler to me. I just didn't know about it.

I just need to try it now. Fingers crossed. :)
 
P

plog

Banishment Pending
Local time
Today, 06:59
Joined
May 11, 2011
Messages
11,643
No it will not. You are not treating intPartID as a variable. It will be interpreted as a field in tblParts.

You need to stop the string, insert the value of the variable:

TheVariable = 17

YourString ="The value of TheVariable is " & TheVariable & "."


In YourString above it will contain the name of the variable once and the value of the variable once.
 
Z

Zydeceltico

Registered User.
Local time
Today, 07:59
Joined
Dec 5, 2017
Messages
843
plog said:
No it will not. You are not treating intPartID as a variable. It will be interpreted as a field in tblParts.

You need to stop the string, insert the value of the variable:

TheVariable = 17

YourString ="The value of TheVariable is " & TheVariable & "."


In YourString above it will contain the name of the variable once and the value of the variable once.
Click to expand...

OK.

To reiterate one thing intPartID is a public variable declared in a standard module. It is also Long not a String. Does that matter considering this syntax: " & TheVariable & "." ???
 
P

plog

Banishment Pending
Local time
Today, 06:59
Joined
May 11, 2011
Messages
11,643
In VBA no, in SQL yes.

In SQL when inserting a string you should put single quotes before and strings:

"WHERE StringField='" & StringVariable & "'"
"WHERE NumberField=" & NumberVariable
 
Z

Zydeceltico

Registered User.
Local time
Today, 07:59
Joined
Dec 5, 2017
Messages
843
plog said:
In VBA no, in SQL yes.

In SQL when inserting a string you should put single quotes before and strings:

"WHERE StringField='" & StringVariable & "'"
"WHERE NumberField=" & NumberVariable
Click to expand...

If I am understanding and if PartID is a number and intPartID is a number then I believe this should work:

strSQL = "SELECT Part_ID, PartType, PartDWG" & _
"FROM tblParts" & _
"WHERE PartID =" & intPartID

By the way - -what are the ampersands for and why are there one on each side of a string and only one preceding a number?
 
Z

Zydeceltico

Registered User.
Local time
Today, 07:59
Joined
Dec 5, 2017
Messages
843
Zydeceltico said:
If I am understanding and if PartID is a number and intPartID is a number then I believe this should work:

strSQL = "SELECT Part_ID, PartType, PartDWG" & _
"FROM tblParts" & _
"WHERE PartID =" & intPartID

By the way - -what are the ampersands for and why are there one on each side of a string and only one preceding a number?
Click to expand...

Light Bulb - I think the ampersands must be "ands" connecting the string to it's quotation marks. Yes? No?
 
P

plog

Banishment Pending
Local time
Today, 06:59
Joined
May 11, 2011
Messages
11,643
Ampersands are how you connect parts of a string together. You can build strings with literals (text between quote marks, "This is a literal") and variables.

Variable = "a variable"
"This is a literal and this is " & Variable

In my string example:

"WHERE StringField='" & StringVariable & "'"

There are single quotes before and after the appearance of the value in the variable. I do that so that the SQL realizes that what appears next is a literal string and not a field name of the table it is using.

User = "plog"
StrSQL = "....WHERE UserName = '" & User & "'"

That makes StrSQL equal this:

...WHERE UserName ='plog'

That makes sure the SQL knows the user name is exactly 'plog'. Without the single quotes:

...WHERE UserName = plog

It would think plog is a field in the table and would want compare the value in the UserName field to the value in the plog field and error out.
 
Z

Zydeceltico

Registered User.
Local time
Today, 07:59
Joined
Dec 5, 2017
Messages
843
plog said:
Ampersands are how you connect parts of a string together. You can build strings with literals (text between quote marks, "This is a literal") and variables.

Variable = "a variable"
"This is a literal and this is " & Variable

In my string example:

"WHERE StringField='" & StringVariable & "'"

There are single quotes before and after the appearance of the value in the variable. I do that so that the SQL realizes that what appears next is a literal string and not a field name of the table it is using.

User = "plog"
StrSQL = "....WHERE UserName = '" & User & "'"

That makes StrSQL equal this:

...WHERE UserName ='plog'

That makes sure the SQL knows the user name is exactly 'plog'. Without the single quotes:

...WHERE UserName = plog

It would think plog is a field in the table and would want compare the value in the UserName field to the value in the plog field and error out.
Click to expand...

Thank you for that very helpful explanation. I couldn't find it that clear in any book I've been reading.
 
Z

Zydeceltico

Registered User.
Local time
Today, 07:59
Joined
Dec 5, 2017
Messages
843
plog said:
In VBA no, in SQL yes.

In SQL when inserting a string you should put single quotes before and strings:

"WHERE StringField='" & StringVariable & "'"
"WHERE NumberField=" & NumberVariable
Click to expand...

I am now getting an error that states "Syntax error (missing operator) in query expression 'PartDWGFROMtblPartsWHERE PartID =58'."

with this code (and I have a msgBox telling me that public variable intPartID is holding the correct value):

Code: 
Dim QCDB As DAO.Database
Dim rsPart As DAO.Recordset
Dim strSQL As String

Set QCDB = CurrentDb
strSQL = "SELECT Part_ID, PartType, PartDWG" & _
    "FROM tblParts" & _
    "WHERE PartID =" & GetPartID()
Set rsPart = QCDB.OpenRecordset(strSQL)
        
    Me.txtPartType_FK = rsPart.Fields(0).Value
    Me.txtPartName = rsPart.Fields(1).Value
    Me.txtPartDrawing = rsPart.Fields(2).Value


58 is the correct number that should be passed so that is getting at least that far.

intPartID is declared in a standard module as Dim intPartID as Long.
GetPartId is a public function declared like this:
Code: 
Public Function GetPartID()
    GetPartID = intPartID
End Function

What am I missing?

If I change GetPartID() to intPartID I get exactly the same error message.

Thanks!
 
You must log in or register to reply here.

Similar threads

Z
Find records with 1st five characters
Replies
4
Views
700
arnelgp
arnelgp
T
Error with Unions
Replies
6
Views
327
Josef P.
J
K
Ordered By Warning in Sql Server
Replies
10
Views
411
Pat Hartman
P
M
Run-Time Error 3131 Syntax error in FROM clause
Replies
6
Views
417
theDBguy
theDBguy
N
SQL syntax in SQL Server 2019
Replies
12
Views
685
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom