Syntax problem

  • Thread starter Thread starter Wayne
  • Start date Start date
W

Wayne

Can anyone tell me whats wrong with the following code?

'Start
Dim strSqlSelect4 As String
Dim strSqlOrder4 As String
Dim strSqlWhere4 As String

'Create SQL Query
strSqlSelect4 = "SELECT Colour " & _
"FROM [PrintingFees] "

'Where
strSqlWhere4 = " WHERE [PrintingFees].Plotter =" _
& [Forms]![PlotSelect]![Plotter] & _
" AND [PrintingFees].Paper =" _
& [Forms]![PlotSelect]![Paper]

'Order by
strSqlOrder4 = " ORDER BY Colour DESC"


'Put strings together to form complete statement
Me!Colour.RowSource = strSqlSelect4 &
strSqlWhere4 & strSqlOrder4 & ";"

I think there's a syntax problem somewhere in the WHERE
clause but i dont know how to fix it.

Thanks
 
What are the data types of Plotter and Paper? What you've got should be fine
if they're numeric fields. If they're text fields, try

strSqlWhere4 = " WHERE [PrintingFees].Plotter =" _
& Chr$(34) & [Forms]![PlotSelect]![Plotter] & Chr$(34) _
" AND [PrintingFees].Paper =" _
& Chr$(34) & [Forms]![PlotSelect]![Paper] & Chr$(34)
 
Hi Doug

Plotter, Paper and Colour are all text fields. But the code
you gave me still comes up as a syntax error.

-----Original Message-----
What are the data types of Plotter and Paper? What you've got should be fine
if they're numeric fields. If they're text fields, try

strSqlWhere4 = " WHERE [PrintingFees].Plotter =" _
& Chr$(34) & [Forms]![PlotSelect]![Plotter] & Chr$(34) _
" AND [PrintingFees].Paper =" _
& Chr$(34) & [Forms]![PlotSelect]![Paper] & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Wayne said:
Can anyone tell me whats wrong with the following code?

'Start
Dim strSqlSelect4 As String
Dim strSqlOrder4 As String
Dim strSqlWhere4 As String

'Create SQL Query
strSqlSelect4 = "SELECT Colour " & _
"FROM [PrintingFees] "

'Where
strSqlWhere4 = " WHERE [PrintingFees].Plotter =" _
& [Forms]![PlotSelect]![Plotter] & _
" AND [PrintingFees].Paper =" _
& [Forms]![PlotSelect]![Paper]

'Order by
strSqlOrder4 = " ORDER BY Colour DESC"


'Put strings together to form complete statement
Me!Colour.RowSource = strSqlSelect4 &
strSqlWhere4 & strSqlOrder4 & ";"

I think there's a syntax problem somewhere in the WHERE
clause but i dont know how to fix it.

Thanks
Click to expand...


.
Click to expand...
 
What's the exact error? Is the PlotSelect form open when you're doing this?
Any chance you've got a typo for any of the field names in the table or
control names on the form?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Wayne said:
Hi Doug

Plotter, Paper and Colour are all text fields. But the code
you gave me still comes up as a syntax error.

-----Original Message-----
What are the data types of Plotter and Paper? What you've got should be fine
if they're numeric fields. If they're text fields, try

strSqlWhere4 = " WHERE [PrintingFees].Plotter =" _
& Chr$(34) & [Forms]![PlotSelect]![Plotter] & Chr$(34) _
" AND [PrintingFees].Paper =" _
& Chr$(34) & [Forms]![PlotSelect]![Paper] & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Wayne said:
Can anyone tell me whats wrong with the following code?

'Start
Dim strSqlSelect4 As String
Dim strSqlOrder4 As String
Dim strSqlWhere4 As String

'Create SQL Query
strSqlSelect4 = "SELECT Colour " & _
"FROM [PrintingFees] "

'Where
strSqlWhere4 = " WHERE [PrintingFees].Plotter =" _
& [Forms]![PlotSelect]![Plotter] & _
" AND [PrintingFees].Paper =" _
& [Forms]![PlotSelect]![Paper]

'Order by
strSqlOrder4 = " ORDER BY Colour DESC"


'Put strings together to form complete statement
Me!Colour.RowSource = strSqlSelect4 &
strSqlWhere4 & strSqlOrder4 & ";"

I think there's a syntax problem somewhere in the WHERE
clause but i dont know how to fix it.

Thanks
Click to expand...


.
Click to expand...
Click to expand...
 
No typos hat i can see. The Plot select form is open while
this is happening.
When I paste your code into the code window to replace my
where clause the entire clause dipalys red. I think thats a
pretty good indication that something is wrong. In your
code there is: & Chr$(34) in several places, is that
correct or should it be someting else that went wrong in
the post?
The exact error is Compile Error: Syntax Error.
Here is the complete code:

Private Sub Paper_AfterUpdate()

'Start
Dim strSqlSelect4 As String
Dim strSqlOrder4 As String
Dim strSqlWhere4 As String

'Create SQL Query
strSqlSelect4 = "SELECT Colour " & _
"FROM [PrintingFees] "

'Where
strSqlWhere4 = " WHERE [PrintingFees].Plotter =" _
& Chr$(34) & [Forms]![PlotSelect]![Plotter] &
Chr$(34) _
" AND [PrintingFees].Paper =" _
& Chr$(34) & [Forms]![PlotSelect]![Paper] &
Chr$(34)

'Order by
strSqlOrder4 = " ORDER BY Colour DESC"


'Put strings together to form complete statement
Me!Colour.RowSource = strSqlSelect4 &
strSqlWhere4 & strSqlOrder4 & ";"

End Sub
-----Original Message-----
What's the exact error? Is the PlotSelect form open when you're doing this?
Any chance you've got a typo for any of the field names in the table or
control names on the form?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Wayne said:
Hi Doug

Plotter, Paper and Colour are all text fields. But the code
you gave me still comes up as a syntax error.

-----Original Message-----
What are the data types of Plotter and Paper? What you've got should be fine
if they're numeric fields. If they're text fields, try

strSqlWhere4 = " WHERE [PrintingFees].Plotter =" _
& Chr$(34) & [Forms]![PlotSelect]![Plotter] & Chr$(34) _
" AND [PrintingFees].Paper =" _
& Chr$(34) & [Forms]![PlotSelect]![Paper] & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Can anyone tell me whats wrong with the following code?

'Start
Dim strSqlSelect4 As String
Dim strSqlOrder4 As String
Dim strSqlWhere4 As String

'Create SQL Query
strSqlSelect4 = "SELECT Colour " & _
"FROM [PrintingFees] "

'Where
strSqlWhere4 = " WHERE [PrintingFees].Plotter =" _
& [Forms]![PlotSelect]![Plotter] & _
" AND [PrintingFees].Paper =" _
& [Forms]![PlotSelect]![Paper]

'Order by
strSqlOrder4 = " ORDER BY Colour DESC"


'Put strings together to form complete statement
Me!Colour.RowSource = strSqlSelect4 &
strSqlWhere4 & strSqlOrder4 & ";"

I think there's a syntax problem somewhere in the WHERE
clause but i dont know how to fix it.

Thanks


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top