SQL In VBA getting Compile Error: Expected Case

[iheartvba]

Hi Everyone,
I keep on getting an error saying Compile Error: Expected Case in my (MS Access) VBA What I was trying to do was to do a DSUM function for a Table where the criteria was coming from another table, I later found the criteria needs to be from within the domain table I had set DSUM function (makes sense). I then when to Query Bulder to Build The SQL string as Below and put it in VBA

Private Sub tbTotalColl_AfterUpdate()
 'This should get the Sum of All the tblCollections.Amt figures grouped by  
 'tblJobDetails.TFN, tblJobDetails.RcptID is the 1 side & tblCollections.RcptID 
 'is the MANY side of the tables relationship 
    SELECT tblJobDetails.TFN, Sum tblCollections.Amt AS SumOfAmt _
    & FROM tblJobDetails INNER JOIN tblCollections ON tblJobDetails.RcptID =               
    tblCollections.RcptID _
    & GROUP BY tblJobDetails.TFN _
    & HAVING tblJobDetails.TFN = frmPinkCardsMAIN.cboFullName Or    
    tblJobDetails.TFN
 
End Sub

Thanks

[iburyak]

Try this:


[PHP]SELECT tblJobDetails.T FN, Sum(tblCollecti ons.Amt) AS SumOfAmt _
& FROM tblJobDetails INNER JOIN tblCollections ON tblJobDetails.R cptID = tblCollections. RcptID _
& WHERE tblJobDetails.T FN = frmPinkCardsMAI N.cboFullName _
& GROUP BY tblJobDetails.T FN [/PHP]


Hope it helps.

[Killer42]

The problem is nothing to do with the SQL syntax. It is the fact that you have simply typed SQL into your VBA module. You can't do that.

Your VBA code will need to place that SQL statement in a string, and then execute it in some way. Or place it in the appropriate property to have the desired effect - perhaps a form's Filter or RecordSource property, or some such.

The error you are receiving indicates that your VBA "Select Case" statement is not correctly written. And since it isn't VBA code, this is hardly surprising.

P.S. I have no idea whether the SQL syntax is correct or not - that's another story.

[iburyak]

The problem is nothing to do with the SQL syntax. It is the fact that you have simply typed SQL into your VBA module. You can't do that.

Your VBA code will need to place that SQL statement in a string, and then execute it in some way. Or place it in the appropriate property to have the desired effect - perhaps a form's Filter or RecordSource property, or some such.

The error you are receiving indicates that your VBA "Select Case" statement is not correctly written. And since it isn't VBA code, this is hardly surprising.

P.S. I have no idea whether the SQL syntax is correct or not - that's another story.

Syntax didn't look correct to me.

[Killer42]

Syntax didn't look correct to me.

You could be right. I didn't actually read the SQL - I just glanced at it and saw the VBA/SQL problem, so didn't bother to read further. (In fact, I was already expecting it, and just confirmed my suspicions).

[iburyak]

You are great.... :)