SQL Statement Error

[iheartvba]

Hi,

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

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

[Delerna]

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

[iheartvba]

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.

Set cnn = CurrentProject.Connection
If Me.cboReturnType = 1 Or 2 Or 3 Or 4 Or 5 Or 14 Or 15 Then
'StrSqlYear is shown in my first post
rst.Open "strSqlYear", cnn, adOpenKeySet, adLockOptimistic
    With rst
    If rst.RecordCount > 0 Then
    MsgBox "This Client Already has A " & Me.cboReturnType & " For The " & Me.cboYear & " Financial Year"
End If
End With
 

[Delerna]

do either of the combo boxes have more than 1 column

[iheartvba]

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

[iheartvba]

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".

[Delerna]

LOL
I didn't notice that either, its obvious after you find it isn't it :)