VBA value into SQL query

[315hughes]

Afternoon all
is it possiable to put a value from my VBA code into a SQL statement? my VBA code is as follows

strLowestRack = "SQLtoFindOldestStockfrmAssignOrders"
 
Stop
 
Set rs = Me.[sqlOrderDetails subform].Form.RecordsetClone
 
Do While Not rs.EOF
 
prodnumber = rs!ProdID
q = rs!Quantity
 
 
    For i = 1 To q
 
        l = DLookup("RackID", strLowestRack) 'change this sql
 
        DoCmd.RunSQL "UPDATE [Location] SET [Location].ProdID = 1 WHERE [Location].RackID =" & l
 
    Next i
 
rs.MoveNext
 
Loop

The value from within this code that is called prodnumber i would like to use in this sql query

SELECT Location.RackID
FROM Location
WHERE (((Location.ProdID)="prodnumber") 
    AND ((Location.StockAge)=(SELECT MIN(StockAge) 
    FROM Location 
        WHERE Location.ProdID = "prodnumber")));

Where ive put "prodnumber" is actually where i need that value. i have tried [rs]![prodnumber] and [rs]![prodID] with no luck. If someone could point me in the right direction
Thanks very much and for all the previous help i have recieved.

[TheSmileyCoder]

Dlookup takes 3 parameters, where the last one is optional. You can specify a Where clause as the third parameter, allthough I am not sure it would work in your case.

You cannot use [rs] directly in your query since that is a value only available to the VBA environment.

I could offer up some code that would set the querydefinition to a parsed string which would solve your immediate problem, however, I would like if you could explain WHAT you are trying to achieve, as I feel there may be more efficient ways to approach your problem.

[twinnyfo]

315Hughes,

Your code would look someting like this:

Dim strSQL as String
 
strSQL = "SELECT Location.RackID" & _
    " FROM Location" & _
    " WHERE Location.ProdID = " & prodnumber & _
    " AND Location.StockAge = " & _
        " (SELECT MIN(StockAge) FROM Location " & _
        " WHERE Location.ProdID = " & prodnumber & ");" 
 

I've stepped out the code to make it more readable, but it could be on one line. This would have to be within the same sub as above, so that prodnumber is a variable the DB recognizes.

[315hughes]

@TheSmileyCoder
Hi what i am trying to do is a stock system i use the value from prodnumber to search through a location table to find the oldest stock location number based on that prodnumber value.
The SQL query that does it is the one i posted (also called "strLowestRack" in the vba code). The value that is returned is held in the value "l"(rack number with the oldest stock) which comes from the Dlookup search. The value "l" is then used to update work sheets.
I hope this explains it better

[zmbd]

315Hughes,
It appears that your query would return the newest stock age not the oldest. (line 7 of TwinnyFo's code)
-z