Syntax problem

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
 
D

Douglas J. Steele

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)
 
W

Wayne

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


.
 
D

Douglas J. Steele

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


.
 
W

Wayne

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


.


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top