Hi

Could someone help with showing me how to sync a DSUM to a record as i am currently summing all the records instead of the ones i need?

Thanks

A DSUM has a criteria clause. You use that to sum only the records that meet the criteria.

hi scottgem

i am using the current dsum

net stock=nz(DSum("QuauntityOut","IntStockControl","Transtype='issued'"))-(nz(DSum("QuauntityOut","IntStockControl","Transtype='returned'")))

any pointers on where i need to add the criteria clause?

Try:
net stock=nz(DSum("[QuauntityOut]","IntStockControl","[Transtype]='issued'"))-(nz(DSum("[QuauntityOut]","IntStockControl","[Transtype]='returned'")))

net stock=nz(DSum("QuauntityOut","IntStockControl","Transtype='issued' AND SomeKeyField = " & Me.SomeKeyField))-(nz(DSum("QuauntityOut","IntStockControl","Transtype='returned' AND SomeKeyField = " & Me.SomeKeyField)))

Didn't I tell you this already in another post?

Pat

Yes you did tell me to sync the records - but i did not know how.

thanks for the help above.

scott

Pat

Thanks for the reply - still can not get the calc to work.

sorry to keep asking.

I have an autonumber for the stockcontrolid as the primary key and a foreign key to the products table.

i presume the Me.SomeKeyField should be me.productid?

thanks again

In my databases the foreign key field keeps the same name as the primary key to which it refers because it makes relationships more visible. If yours don't then you will have two different names.

net stock=nz(DSum("QuauntityOut","IntStockControl","Transtype='issued' AND ForeignKeyField = " & Me.PrimaryKeyField))-(nz(DSum("QuauntityOut","IntStockControl","Transtype='returned' AND ForeignKeyField = " & Me.PrimaryKeyField)))

Pat

Really sorry.

I have the stock control table. PK StockcontrolID and this is linked to the Product table with a FK ProductID and the product table as ProductID as its PK.

In the stock control table. I have 'QuauntityOut' field and 'transactiontype'. I need to know what all this issued added up minus all the returned added up. The problem with the above DSUM is that it keeps returning the sum of them all.

I even tried

=Sum(nz(IIf([transtype]="issued",[QuauntityOut])))-(Sum(nz(IIf([transtype]="returned",[QuauntityOut]))))

which does work but as soon as the record has been selected on the main form it flashes the stock amount then vanishes?

Apologies for keeping on about this - but if you can help - thanks

scott

Why did you change to doing a Sum()?

Pat

I originally tried the Dsum and could not get it to work, so i tried the IIF. Only having a basic understanding i just wanted something that worked. The IIf works but when it shows the stock amount, which is correct it only shows it as the record is selected and then it disappears after literally a second?

Thanks again for your input

scott

Pat

To clarify the problem with the claculated field showing the caluclation then going invisible.

Once there is a record in the stock control form then calc stays visible?

Thanks again.

Scott

Pat

solved the problem now - i forgot the nz in the final calc field.

Thanks again for your input and all the help you give - much appreciated.

Scott