Select Query with Sum formula from multiple tables

[jabenj]

Can someone please let me know why this isn't working? I'm trying to take the difference of two fields from two separate spreadsheets. I believe it's only reading from the first spreadsheet (tlbLatexBatch QC) and not the other. Any help would be greatly appreciated.



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


Set con = Application.CurrentProject.Connection
stSql = "SELECT Sum(tblLatexBatchQC.[TransferredWeight]) - Sum([Blendsheet Ouptput].Weight) 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 = TxtCurrentWeight.Text

 

[dhookom]

There is not any "FROM ..." clause in your stSql.

There is no reason to set focus to TxtCurrentWieght. You can use:

  currentweight = Me.TxtCurrentWeight

Duane
Hook'D on Access
MS Access MVP

 

[jabenj]

Tried this way also and didn't work

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

 

[PHV]

What about this ?

stSql = "SELECT Sum([TransferredWeight]) - Sum(Weight) AS currentweight" _
 & " FROM tblLatexBatchQC INNER JOIN [Blendsheet Output]" _
 & " ON tblLatexBatchQC.TankNum = [Blendsheet Output].TankN" & Me!cmbTankNum
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1
Me!TxtCurrentWeight = rs!currentweight

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

 

[jabenj]

No. Keep getting same error messages as before. Depending on how I arrange code keep getting:

No value given for one or more required parameters

Or

Run time Error - The select statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrect.

Leads me to believe I may have some stupid mistake in there somewhere

 

[PHV]

Debug.Print stSql and then copy/paste in a SQL view pane.

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

 

[jabenj]

Thanks. I'll keep plugging away. SQL pane says:

Compile error, Variable not yet created in this context

I defined the currentweight variable so I'm not sure what exactly it's talking about

 

[jabenj]

Got it to work:

stSql = "SELECT Sum([TransferredWeight]) - Sum(Weight) AS currentweight" _
& " FROM tblLatexBatchQC INNER JOIN [Blendsheet Output]" _&"ON tblLatexBatchQC.TankNum = [Blendsheet Output].TankN Where [Blendsheet Output].TankN = '" & Me!cmbTankNum & "'"

Needed the where clause to get it to work

Thanks again