Query based on existing rst in VB

[jaycast]

Ok, here's my scenario:

I have a complex query, referencing a SQL DB, already defined. I will be needing to add criteria to it as time progresses, and one of these criteria derives from a foreign table in an Access database.

My question is this:
How can I interpolate a recordset variable (Access) into an already-existing SQL string (SQL)?

I'm befuddled!

Thanks in advance.

 

[Scoty]

JayCast,

Just so I am clear on this do you want to be able to pull sql data based on criteria in the access tbl(1)? or do you want to set a critera "parameter field name" based on the values from access tbl(2)?

Ie:
(1)
With rst
lsSQL = "Select * From SQLtbl where MyCriteria = " & !AccessCriteria & ";"
End With

or
(2)
With rst
lsSQL = "Select * From SQLtlb Where " &!AccessFieldName & " = 'ThisStuffOnly';"
End With

Scoty :

"Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[jaycast]

Scoty,

I believe it is a variation of (1). I have a SQL statement already prepared. I'll see if I can visualize how I want the statement to look, if possible:

Through all the setting and declarations:

rst1 = "Select OS, Item from Oversize Where OS Like 'Actual'"

rst2 = "Select TrackNum, Charge, ChargeCode, Item FROM SQLTbl
Inner Join SQLTbl On SQLTbl.Item = rst1.item Where rst1.item Like 'Actual'"


Does this make any better sense? I basically want to reference another recordset WITHIN a new one that I am creating.

 

[Scoty]

JayCast,
So just use contantination (&) and place it. You can not place it within the quotes so place it outside quote

like this

rst1 = "Select OS, Item from Oversize Where OS Like 'Actual'"

rst2 = "Select TrackNum, Charge, ChargeCode, Item FROM SQLTbl
Inner Join SQLTbl On SQLTbl.Item = " & rst1!item & "  Where " & rst1!item & " Like 'Actual'"

(also note that I replaced your dot "rst1.item" with a slam "rst1!item" this should do the trick for you)

hth
Scoty :

"Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[jaycast]

Thanks for the input. I've tried the solution you've suggested, but I don't think I applied it properly. Here is what the entire code segment looks like:

Dim mysql As String, mysql2 As String, rst As New ADODB.Recordset, rst2 As New ADODB.Recordset, rst3 As New ADODB.Recordset


sql.Provider = "MSDataShape"
sql.ConnectionTimeout = 100
sql.Open "DSN=FreightInvoices", "sa", ""

mysql = "SELECT qryInvoiceFreightSums.InvoiceNum, qryInvoiceFreightSums.FedExFreight, tblFedExTrackNum.SumOffreight_xinvbox AS MunicsFreight, " & _
"qryInvoiceFreightSums.FedExFreight -tblFedExTrackNum.SumOffreight_xinvbox AS Difference, tblFedExTrackNum.shipdate_xinvbox, " & _
"tblFedExTrackNum.TrackingNum, tblFedExTrackNum.itemid_invdet, tblFedExTrackNum.SoldTo " & _
"FROM qryInvoiceFreightSums " & _
"INNER JOIN qryTrackingNumberCharges ON qryInvoiceFreightSums.TrackingNum = qryTrackingNumberCharges.TrackingNum " & _
"AND qryInvoiceFreightSums.FedExFreight = qryTrackingNumberCharges.ChargeSum " & _
"Inner Join tblFedExTrackNum ON qryInvoiceFreightSums.TrackingNum = tblFedExTrackNum.TrackingNum " & _
"GROUP BY qryInvoiceFreightSums.InvoiceNum, qryInvoiceFreightSums.FedExFreight, tblFedExTrackNum.SumOffreight_xinvbox, " & _
"qryInvoiceFreightSums.FedExFreight - tblFedExTrackNum.SumOffreight_xinvbox, tblFedExTrackNum.shipdate_xinvbox, " & _
"tblFedExTrackNum.TrackingNum, tblFedExTrackNum.itemid_invdet, tblFedExTrackNum.SoldTo " & _
"HAVING (qryInvoiceFreightSums.FedExFreight - tblFedExTrackNum.SumOffreight_xinvbox > 0)" & _
"ORDER BY tblFedExTrackNum.SoldTo, qryInvoiceFreightSums.InvoiceNum"



rst.Open mysql, sql, adOpenKeyset, adLockReadOnly
Set sql = Nothing

sql.Provider = "MSDataShape"
sql.ConnectionTimeout = 100
sql.Open "DSN=DataReporting", "sa", ""

rst2.Open "Oversized Rugs Table", sql, adOpenKeyset, adLockReadOnly

mysql2 = "SELECT InvoiceNum, FedExFreight, MunicsFreight, Difference, shipdate_xinvbox, TrackingNum, itemid_invdet, SoldTo FROM " & rst & _
"INNER JOIN " & rst & "ON " & rst!itemid_invdet & " = Left(" & rst2!pricematrix_item & &quot,5 " & _
"HAVING " & rst2![Oversize Status] & " Like 'Actual'"

rst3.Open mysql2, sql, adOpenKeyset, adLockReadOnly

Debug.Print "RST..." & rst.RecordCount
Debug.Print "RST2..." & rst2.RecordCount
Debug.Print "FinalRST..." & rst3.RecordCount


I'm not sure what I did wrong, but when I run the procedure, I get a "Type Mismatch" error when the first instance of RST is referenced in the mysql2 SQL string.

Any ideas on how I'm screwing this up?

Thanks

 

[Scoty]

Jay,
phew! I hope some one can help with this. I am not that fimiliar with the shape aspect of what you are asking for. good luck
Scoty :

"Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),