Dlookup Date

[geno]

Hi,
I'm trying to return records from an orders table where the date of the order is >= to the date created on parts in a parts table. The syntax I'm using now is: >=DLookUp("[Date created]","[Parts]","[date created]") in the order date criteria field. I don't get any errors but the data returned is not correct..eg
Part number 802 was created on 07/10/2005 and there is an order with this part created on 07/04/2005, this order should not show but it does.
You probably need to know why this part is in the order but has been created after the order date....I'm adding inventory to an exsisting database and need to show parts on hand that are now in stock so I need to only do my calculations on parts sold that were created in the orders table with the date greater then the part created date.
Thanks for any help.

 

[mresann]

Using DLookup is not correct in this situation. It is only used to find a single record in a recordset without creating a recordset object. However, you will have to create an object to retrieve multiple records.

Basically, your sequence will be:

1. Declare recordset object.
2. Set the recordset using sql to include records that match your conditions.
3. Access the records you need.
4. Close the recordset object.

I'm not sure if you are using ADO or DAO, but the best direction I can give you is to check out the Northwind Database that ships with Access and check out recordset objects there to give you a better idea.

 

[KenHigg]

I think you need to tie this together with the part number:

>=DLookUp("[Date created]","[Parts]","tblParts!part_number = tblOrders.part_ number")

This would not be precise, but I think you can see the proble maybe...

???

 

[geno]

Ken,
this query information is going to drive a subform which will be put onto a parts form linked through the PartID. I've tested this already and it works great I just can't get the correct data into the subform....any other ideas?

 

[KenHigg]

That relationship should be set up with the sub form link fields. !?!

 

[geno]

Ken,
I've already have the form and subform working, instead of using dlookup in my query I added the parts table to the grid and in the criteria of the order date field I put: >=[parts].[created date]. All is well now as the data comes in correct. Thanks for your replies.

 

[KenHigg]

.
.
.
.
Cool.