complex calculation. I'm in over my head 2

[simon551]

This is a calculated field in a query: BalShares: tblInventory.NumShares-Nz(qrySalesSales.NumSold,0)

I want to revise it so that it gives me the BalShares for a specific date, as follows: for tblInventory where Pdate is prior to RunDate and for qrySalesSales where soldDate is prior to RunDate.

Can someone help me write that. I'm not having much luck.

 

[PHV]

Take a look at the IIf function.

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

 

[simon551]

PHV,
Not sure how to find those Faq's. Is there a way to search by number? The links don't open for me.


Thanks for your help.

 

[PHV]

Seems your browser don't allow javascript.
Try this:

http://www.tek-tips.com/faqs.cfm?fid=2886

 

[simon551]

huh. It works in Explorer but not Firefox for me. Oh well, I see that's just your tagline.

Would you mind helping me a little further. Here is what I am trying to do. This is the sql for the query as it is, and below, what I want to insert, but don't know how to.

SELECT tblInventory.MgrID, tblInventory.SecID, tblInventory.Tckr, tblInventory.Description, tblInventory.Pdate, IIf(tblInventory.NumShares-Nz(qrySalesSales.NumSold,0) AS BalShares, tblInventory.Price1, ([balShares]*[Price1]) AS Cost1, tblInventory.Price2, ([BalShares]*[Price2]) AS Cost2, Forms!frmFMV!txtRD AS RunDate, tblFMV.Pr123104, [Pr123104]*[BalShares] AS FMVRD, [FMVRD]-[Cost1] AS Unrealized1, [FMVRD]-[Cost2] AS Unrealized2
FROM tblFMV INNER JOIN (tblInventory LEFT JOIN qrySalesSales ON tblInventory.LotID = qrySalesSales.LotID) ON tblFMV.SecID = tblInventory.SecID
WHERE (((tblInventory.SecID)=[Forms]![frmFMV]![SecID]) AND (([tblInventory].[NumShares]-Nz([qrySalesSales].[NumSold],0))>0));

tblInventory.Pdate=<RunDate AND qrySalesSales.SoldDate=<RunDate

Thanks in advance, again.
-simon

 

[lespaul]

the FAQs in PHV's sig line are only on getting great answers to your questions. You need to search the Access help (F1) for the iif function:

iif(some logical test, value if true, value if false)


in your case that would be something like:

iif(PDate < RunDate, true expression, false expression)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[simon551]

PHV and Leslie,
This is pretty mucked up of a thread. I think I'm close, but maybe I'm not. Are you able to help anymore?

I tried this:

IIf(tblInventory.Pdate<RunDate AND qrySalesSales.SoldDate<RunDate,(tblInventory.NumShares-
Nz(qrySalesSales.NumSold,0),0)