DSum Criteria 1

[enuf4u]

I have a query called "Request". I have a second query called "OpenPO". Each query has a field called "MHPartNumber" In the request query, MHPartNumber is the primary key, or it only will appear once. In the OpenPO query, there may be multiple occasions of the same MHPartNumber. In the OpenPO query, I have a field called "QtyOpen". What I am trying to do is use a DSum function in one of the Request query fields that calculates the sum of the QtyOpen fields in the OpenPO query where the MHPartNumber of the OpenPO query equal the MHPartNumber of the record or line in the Request query. Wow, what a mouth full. I hope this makes sense. I cannot get the criteria to work. Here is the function typed into the field of the Request Query:

Open: DSum("[QtyOpen]","OpenPO",What goes here?!?)

Without the criteria, I get the total sum of all entries in the OpenPO query. I only want the sum of the entries where the MHPartNumber of the summed items equals the MHPartNumber of the record or line the function is typed in.

God Bless anyone who can help me!

Thanks in advance!

 

[PHV]

Something like this ?
Open: DSum("[QtyOpen]","OpenPO","OpenPO.MHPartNumber=Request.MHPartNumber")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Oops, my bad :~/
Say that in Request query, MHPartNumber is a field of the table tblMH, then :
Open: DSum("[QtyOpen]","OpenPO","OpenPO.MHPartNumber=tblMH.MHPartNumber")
You may post the SQL code of the Request query if need more help.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

This depends on the data type of MHPartNumber. If it is numeric:

Open: DSum("[QtyOpen]","OpenPO","MHPartNumber = " & [MHPartNumber])

If it is text:

Open: DSum("[QtyOpen]","OpenPO","MHPartNumber = """ & [MHPartNumber] & """")


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[enuf4u]

Thanks for your reply. Just for your info, MHPartNumber is a text field. I tried the following:

Open: DSum"[QtyOpen]","OpenPO","OpenPO.MHPartNumber=Request.MHPartNumber")

I got the error that Access could not find Request.MHPartNumber. I tried several variations of stuff to try to get it to work, but I had no luck.

Here is the SQL for the Request Query. The table the data comes from is "MaintenanceInventory"

SELECT MaintenanceInventory.MHPartNumber, MaintenanceInventory.MfgPartNumber, MaintenanceInventory.Description, MaintenanceInventory.Minimum, MaintenanceInventory.OrderQty, MaintenanceInventory.Request, MaintenanceInventory.RequestDate, MaintenanceInventory.QOH
FROM MaintenanceInventory
WHERE (((MaintenanceInventory.Request)=False) AND ((MaintenanceInventory.Ordered)=False) AND (([QOH]-[Minimum])<=0));

Here is the SQL for the OpenPO query. This query has data from 4 different tables: Supplier, POHeader, MaintenanceInventory, and PODetails

SELECT POHeader.PO, POHeader.OrderDate, PODetails.MHPartNumber, MaintenanceInventory.MfgPartNumber, MaintenanceInventory.Description, PODetails.OrderQty, [PODetails]![OrderQty]-[QtyOpen] AS RecievedQty, PODetails.Cost, PODetails.QtyOpen, PODetails.Close, Supplier.SupplierName
FROM (Supplier RIGHT JOIN POHeader ON Supplier.SupplierID = POHeader.SupplierID) LEFT JOIN (MaintenanceInventory RIGHT JOIN PODetails ON MaintenanceInventory.MHPartNumber = PODetails.MHPartNumber) ON POHeader.POID = PODetails.POID
WHERE (((PODetails.Close)=No));

All I need to be able to do is sum the open quantity from the OpenPO query where the MHPartNumber in the OpenPO query equals the MHPartNumber in the Request query line... This should be simple!

Thanks for any help!

 

[enuf4u]

I also tried this:

Open: DSum("[QtyOpen]","OpenPO","MHPartNumber = """ & [MHPartNumber] & """")

The field was blank. I checked to see that some records matched the criteria and they did, so this one is not working either...

Thanks for you help, I will try some more...

 

[dhookom]

It should work if what you described is true. This assumes you have the same field name and same data types and values in your query table and in OpenPO.
Open: DSum("[QtyOpen]","OpenPO","MHPartNumber = """ & [MHPartNumber] & """")

You can do a test by opening the debug window (press Ctrl+G) and entering:

? DSum("[QtyOpen]","OpenPO","MHPartNumber = '123abc'")
Substitute a legitimate part number in place of 123abc.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[enuf4u]

THANK YOU VERY MUCH dhookom!

I don't know why it did not work the first time, but when I tried

Open: DSum("[QtyOpen]","OpenPO","MHPartNumber = """ & [MHPartNumber] & """")

this time it worked!

Thank all for your help!