By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

SQL Statement Error

100+
P: 171
Hi,

When I put this code in I get the error: Invalid SQL Statement, Expected 'DELETE', 'INSERT', 'PROCEEDURE', 'SELECT' or 'UPDATE'

Expand|Select|Wrap|Line Numbers
  1. strSqlYear = "SELECT tblJobDetails.TFN, tblJobDetails.ReturnType, tblYearsPerReceipt.Year FROM tblJobDetails, tblYearsPerReceipt WHERE (tblJobDetails.TFN)= " & Me.tbTFN & " AND (tblJobDetails.ReturnType)= " & Me.cboReturnType & " AND (tblYearsPerReceipt.Year)= " & Me.cboYear
Extra Info
1. I have done Dim strStrYear as String
2. Me.tbTFN is a textbox in the form to which this code relates
3. Me.cboReturnType is a ComboBox in the form to which this code relates
4. Me.cboYear is a ComboBox in the form to which this code relates

any help would be much appreciated.


Best Regards
Jan 31 '08 #1
Share this Question
Share on Google+
6 Replies


Delerna
Expert 100+
P: 1,134
Can't see anything immediately obvious.
Try returning the SQL string in a message box or setting a breakpoint
on the line after building the SQL string so that you can have a look at the contents of the string and see if it looks as it should.

The error suggests that the string hasn't been built correctly, for example
a space missing or a value comming in as a reserved word or some such thing
Jan 31 '08 #2

100+
P: 171
Can't see anything immediately obvious.
Try returning the SQL string in a message box or setting a breakpoint
on the line after building the SQL string so that you can have a look at the contents of the string and see if it looks as it should.

The error suggests that the string hasn't been built correctly, for example
a space missing or a value comming in as a reserved word or some such thing
Hi,

actually I've left out something quite important in my last post, I'm runing this query via ADO so the dimensions for cnn are Public cnn As ADODB.Connection
and the dimensions for rst are Public rst As New ADODB.Recordset. Don't Know if that makes a difference. Also I had previously made the SQL in the Query Builder (the queries name was "qryDuplicateYear") and just ran rst.open "qryDuplicateYear" , and when I removed the criteria it worked fine, when I put the criteria back in then it again gave this problem. so this is some additional code from that proceedure which may help.

Expand|Select|Wrap|Line Numbers
  1. Set cnn = CurrentProject.Connection
  2. If Me.cboReturnType = 1 Or 2 Or 3 Or 4 Or 5 Or 14 Or 15 Then
  3. 'StrSqlYear is shown in my first post
  4. rst.Open "strSqlYear", cnn, adOpenKeySet, adLockOptimistic
  5.     With rst
  6.     If rst.RecordCount > 0 Then
  7.     MsgBox "This Client Already has A " & Me.cboReturnType & " For The " & Me.cboYear & " Financial Year"
  8. End If
  9. End With
  10.  
Feb 1 '08 #3

Delerna
Expert 100+
P: 1,134
do either of the combo boxes have more than 1 column
Feb 1 '08 #4

100+
P: 171
do either of the combo boxes have more than 1 column
Yes They do but the information I want is in the 1st Column I.E. I do not need to say Me.cboReturnType.Column(1). and Even if I put just the Me.tbTFN crtieria it still gives the same problem
Feb 1 '08 #5

100+
P: 171
Excuse me Delerna my brain went on strike today so I havent got much to work with, I had set the string as strSqlYear and then I put it in quotes again when I did rst.open so i did rst.open "strSqlYear".
Feb 1 '08 #6

Delerna
Expert 100+
P: 1,134
LOL
I didn't notice that either, its obvious after you find it isn't it :)
Feb 1 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.