Create recordset from SQL Issue (1 Viewer)

I'm haveing a tough time converting a query I created into a recordset.
Here is my vba code:

Public Function PriceLookup(PackagingPK As Integer, HarvestDate As Date) As Integer
Dim dbs As Database
Dim rsSQL As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT GrpFinal.PackagingPK AS PackagingPK, Min(GrpFinal.Price) AS MinOfPrice " & _
"FROM (SELECT Final.PackagingPK AS PackagingPK, Final.Price AS Price, Final.PriceDate, Final.SupplierPK FROM tblPackagingPrice AS Final, (SELECT Max(MaxDate.PriceDate) AS PriceDate, MaxDate.PackagingPK AS PackagingPK, MaxDate.SupplierPK AS SupplierPK FROM tblPackagingPrice AS MaxDate WHERE (MaxDate.PriceDate <= " & Date & " AND MaxDate.PackagingPK = 1) GROUP BY MaxDate.PackagingPK, MaxDate.SupplierPK) AS Filter WHERE Final.PackagingPK=Filter.PackagingPK And Final.PriceDate=Filter.PriceDate And Final.SupplierPK=Filter.SupplierPK) AS GrpFinal" & "GROUP BY GrpFinal.PackagingPK"
Debug.Print strSQL
Set rsSQL = dbs.OpenRecordset(strSQL)
If Not (rsSQL.BOF And rsSQL.EOF) Then
PriceLookup = rsSQL("MinOfPrice")
End If
PriceLookup = 0
End Function

I'm getting a runtime error '3131' Syntax error in FROM clause.

I would really be appreciative if someone could help my find my issue.

Thank you,

Anthony

I do not understand this.. Why do you have..
Why not simply have it...
Think it might be the 'no space' after the AS GrpFinalGROUP BY GrpFinal.PackagingPK.. Hope it makes sense..

Thank you, that got me past the syntax error, but the recordset is still coming back empty. I copied the text into a new query dropped all the code used for formatting and it returns the record I expect. Not sure what I'm doing wrong in code. It's pretty convoluted, but what I'm trying to get to is pass in a key and a date and it's supposed to spit out the appropriate price in a large price list with multiple suppliers.

Gonna take a stab at it tomorrow, maybe thinking I can create a larger recordset and use the filtering and sorting functions to get the right price.

Thanks for your help!

I guess it will always return 0?? that is because you have asked it to return 0 all the time..
It actually should have been..

thanks again, but I put in a break and looked at the recordset and it is indeed empty so never gets a chance to even get to the first assignment statement. I'm sure i'll have to fix the code to match yours once I get the query figured out.

Here is my working query if run directly from Access
SELECT GrpFinal.PackagingPK AS PackagingPK, Min(GrpFinal.Price) AS MinOfPrice
FROM (SELECT Final.PackagingPK AS PackagingPK, Final.Price AS Price, Final.PriceDate, Final.SupplierPK FROM tblPackagingPrice AS Final, (SELECT Max(MaxDate.PriceDate) AS PriceDate, MaxDate.PackagingPK AS PackagingPK, MaxDate.SupplierPK AS SupplierPK FROM tblPackagingPrice AS MaxDate WHERE MaxDate.PriceDate<= #9/20/2012# AND MaxDate.PackagingPK = 1 GROUP BY MaxDate.PackagingPK, MaxDate.SupplierPK) AS Filter WHERE Final.PackagingPK=Filter.PackagingPK And Final.PriceDate=Filter.PriceDate And Final.SupplierPK=Filter.SupplierPK) AS GrpFinal
GROUP BY GrpFinal.PackagingPK;

I did a copy paste back to VBA
strSQL = "SELECT GrpFinal.PackagingPK AS PackagingPK, Min(GrpFinal.Price) AS MinOfPrice " & _
"FROM (SELECT Final.PackagingPK AS PackagingPK, Final.Price AS Price, Final.PriceDate, Final.SupplierPK FROM tblPackagingPrice AS Final, (SELECT Max(MaxDate.PriceDate) AS PriceDate, MaxDate.PackagingPK AS PackagingPK, MaxDate.SupplierPK AS SupplierPK FROM tblPackagingPrice AS MaxDate WHERE (MaxDate.PriceDate <= 09/20/2012 AND MaxDate.PackagingPK = 1) GROUP BY MaxDate.PackagingPK, MaxDate.SupplierPK) AS Filter WHERE Final.PackagingPK=Filter.PackagingPK And Final.PriceDate=Filter.PriceDate And Final.SupplierPK=Filter.SupplierPK) AS GrpFinal GROUP BY GrpFinal.PackagingPK"

removed the two ## and, but get an empty recordset in vba.

I appreciate your help - gonna keep plugging at it today and see if I can either figure it out, or figure out a better way to do it.

Anthony

Well for one you do need the octothorpes (#).

Bob,

That was exactly it - thank you so much!

Anthony

AnthonyE said:

Bob,

That was exactly it - thank you so much!

Anthony

Click to expand...

Yeah, dates need the octothorpes as delimiters most of the time. And text needs quotes.