Vba sql

[315hughes]

Hi i am trying to cross reference SQL queries from within VBA. By using the Query Builder i can easily do it and get the correct result but not from within VBA
my table called location is like this
RackID-StockAge-PreAllocate-ProdID
1 01/08/12 true 5
2 02/08/12 false 3
3 01/08/12 false 3

The aim is to get the RackID with the lowest stockage where the ProdID = (var) and PreAllocate is set to false.

I cannot get this to work by using just one SQL query but can by using two. I have tried to put this in to VBA

strFirstQuery = "SELECT [Location].RackID, [Location].StockAge FROM [Location] WHERE [Location].ProdID = " & prodnumber & " AND [Location].PreAllocate = False ;"

So the above strFirstQuery would hold values rackID 2,3 and the dates associated. Then i would put the values from that in to

strOldSQL = "SELECT [strFirstQuery].RackID FROM strFirstQueryWHERE [strFirstQuery].StockAge = " & "(SELECT MIN(StockAge) FROM strFirstQuery) "";"

and that would return the value of rackID 3. so all together my VBA looks like

strFirstQuery = "SELECT [Location].RackID, [Location].StockAge FROM [Location] WHERE [Location].ProdID = " & prodnumber & " AND [Location].PreAllocate = False ;"
 
        strOldSQL = "SELECT [strFirstQuery ].RackID FROM strFirstQuery WHERE [strFirstQuery ].StockAge = " & "(SELECT MIN(StockAge) FROM strFirstQuery ) "";"
 
        l = CurrentDb.OpenRecordset(strOldSQL).Collect(0)

Hope that is clear enough

[twinnyfo]

315Hughes,

The problem is that your variable "strFirstQuery" is neither a recordset nor an actual query, so you cannot reference it as if it is one.

You could build it as an actual query and use the value from a form, for example, to set the value of "prodnumber", and then query on that query.

You could also do this by using a recordset, which may be your better way to go. Just use the recordset to find the particular record you want.

Hope this points you in the right direction.

[zmbd]

If you have a query that returns the correct results already, then you can open a recordset on that query within VBA...

'this is only a code snip
Dim zdbs As DAO.Database, zrs As DAO.Recordset
Set zdbs = CurrentDb
Set zrs = zdbs.OpenRecordset("qryname")

The only time I build SQL in the code is if I am trying to protect it from a user messing with it or it needs to be dynamic beyond what can be provided within normal parameters.

-z