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
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