using recordset as query criteria?

[JCross]

Hi All! I am stuck and I posted something about this before, but then I went back and reread it and it didn't make much sense........so here goes. in a nutshell, can you use a recordset, created in a module, as criteria for a query? I have one query that brings up a list of dates, and then I need to take each date in turn and use it as the criteria in another query. How do I do this? I tried querydef.parameters, and I get an error that the criteria can not be found. HELP!

Jennifer

 

[Fornatian]

If you don't necessarily need to use recordsets you may like to consider the use of a subquery as criteria for your field.

By using a query as the criteria for field you can return a list of values to be used as criteria.

Look subquery up in the help index, it doesn't show in the normal help widget.

 

[JCross]

Argh! I've made a million different queries, and i had NO IDEA about subqueries. thank you so much!

ok - tried it but i need to use the recordset to bring back a list of dates, and then loop through the dates one by one in the criteria.....

J

 

[Pat Hartman]

Almost anything you can do in a code loop, you can do faster and with much less effort in an update query that joins the query with the list of dates to some table or other query. Why not tell us what you want to do in the code loop.

 

[JCross]

Well, I need to retrieve a list of dates where the criteria is >= a field on a form. Then I retrieve the Inventory Details for each date, and for each detail I need to update the Cost field to either

1) the cost the last time the item was delivered OR
2) if the item was not delivered since the last inventory, the cost at the last inventory. ( i use this somewhere else, and it looks like this :


Dim x As Double
Dim y As Double
Dim z As Double

x = Nz(DLookup("Conversion", "tblVendor_Item", "InventoryItemNumber = " & "Forms!frmInventoryEdit!InventoryItemNumber"), 1)

y = Nz(DLookup("Con", "qryInventory", "InventoryItemNumber = " & "Forms!frmInventoryEdit!InventoryItemNumber"), 1)

z = Nz(DLookup("UnitPrice", "qryMostRecentCost2", "InventoryItemNumber = " & "Forms!frmInventoryEdit!InventoryItemNumber"), 0)

If IsNull(z) Or z = 0 Then
Me.LastCost = Nz(DLookup("LastCost", "qryPrevLastCost3", "InventoryItemNumber = " & "Forms!frmInventoryEdit!InventoryItemNumber"), 0)
Else
Me.LastCost = (z / x) * y
End If

Then move to the next date in the list, in ascending order.

 

[Lamb2087]

Subquery

I tried the subquery with no luck as far as adding new records.