Select statement, multiple tables

[jabenj]

Once again I'm stuck. Shocker. I'm trying to take data from multiple tables and Sum them. I think what I have should work, but I keep getting a runtime error 2465 saying the field "'" can't be found. Do I need to do a join statement? Help is appreciated. Thanks

Dim con As Object
Dim rs As Object
Dim stSql As String
Dim currentweight As Long


Set con = Application.CurrentProject.Connection
stSql = "SELECT Sum([TransferredWeight]) - Sum([[Weight]) FROM tblLatexBatchQC, [Blendsheet Output] AS currentweight"
stSql = stSql & " WHERE tblLatexBatchQC.[TankNum] = '" & [Blendsheet Output].TankN & Me.cmbTankNum & "';"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1

TxtCurrentWeight.SetFocus
currentweight = rs![currentweight]
TxtCurrentWeight.Text = currentweight

 

[PHV]

stSql = "SELECT Sum([TransferredWeight]) - Sum([[Weight]) FROM tblLatexBatchQC, [Blendsheet Output] AS currentweight"
stSql = stSql & " WHERE tblLatexBatchQC.[TankNum] = [Blendsheet Output].TankN" & Me.cmbTankNum & ";"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jabenj]

Thanks for the help. I tried that and now I get another message saying no value given for one or more parameters. Haven't had a chance to play with it yet to figure out why. I'm guessing something missing in my table or a problem with one of my variables.