Hi. Here is my problem. I have 3 tables i wish to link. Which are as follows

Part
------
PartID
Description
etc...

Part_Purchase_Order
------------------------
PartID
PurchaseorderID
QTY

Purchase_Order
------------------
PurchaseorderID
Date
etc....

My problem is, i wish to be able to pull the "purchase order date", "part ordered" and "QTY ordered", but only those records for a selected "PurchDate" I have managed to do this using the simple code

SELECT Description, Qty, PurchDate, Price, Model
FROM [Parts_purchase_order], [Part], [Purchase_order]
WHERE Parts_purchase_order.PartID = Part.PartID AND Parts_purchase_order.PurchaseorderID = Purchase_order.PurchaseorderID

However, i want to be able to do a search for purchase oders by date. I.e. two fields which are "Date from" and "Date To". I then wish every purchase order held within these two dates to be selected.

Any help would be greatful

Thanks
Aaron

Ok. I've made progress. I am just using two tables for the moment. I have managed to INNER JOIN two tables and i can now display the results based on a query. For example i tested it with Purcashe_order.PurchaseorderID = 2. It displayed the correct results.

However, i now wish to be able to serach it using PurchDate. however, i am guessing due to the fact it is a date format it is causing errors?

The code i have is as follows

SELECT PurchDate, QTY
FROM Parts_purchase_order INNER JOIN Purchase_Order ON Parts_purchase_order.PurchaseorderID = Purchase_Order.PurchaseorderID
WHERE Purchase_Order.PurchDate = ???????????????

?????? indicate i havent got a clue what to put there

Thanks

Aaron

Just thought i should let you know i am using MS Access

SELECT PurchDate, QTY
FROM Parts_purchase_order INNER JOIN Purchase_Order ON Parts_purchase_order.PurchaseorderID = Purchase_Order.PurchaseorderID
WHERE Purchase_Order.PurchDate LIKE #Param#

On your Parameters:

Param Name: Param
Type: Date
value: request.querystring("search" search is the name of your search form field.
Default value: %

I'm not sure if that would work but test it. It may.

Javier

Yep. That helped alot. its cos MS Access need the "#" around the date format. Sorted a treat
Thanks for your hlep