I am working on a query that keeps track of securities that are bought and sold. I am using the follwing fields: TradeDate, TodaysDate, PurchasePrice, CurrentPrice. I need to find the following for PriorPrice:

IIf([TodaysDate]=[TradeDate]+1,[PurchasePrice],[PriorPrice)

The securities price daily, but if we purchased the security on the previous day, it needs to price using the purchase price, not the previous day's price. I need to exclude holidays and weekends though. So if TradeDate is a Friday, the previous price will pull in Purchase Price instead of Prior Price.

If anyone can help it would be greatly appreciated.

Assuming your actions take place only on weekdays, it would appear that you need a work-around only on Mondays.

Try this and see if it meets your needs.

IIf([TodaysDate]=[TradeDate]+ Iif(weekday([TodaysDate])=2, 3, 1),[PurchasePrice],[PriorPrice)

Thanks raskew. I'll give it a try and see if it works. I'm flying blind here?!

I think this is going to work, but can someone explain what it is doing?

OK, here again assuming that you're only going to do this on a weekday (Monday - Friday), the idea is that you're going to move back one weekday. So, if today's Tuesday, you're going to look at the value of Monday. If today's Friday, you're going to look at the value of Thursday. Are we clear so far?

But, what happens if today's Monday? If we move back one day, we've got Sunday (not a weekday). If we move back two days, we've got Saturday (not a weekday). Thus, we've got to move back three days to arrive at Friday, the most recent weekday.

So, if weekday(today()) <> 2 (the value of Monday), we move back one day, otherwise, we move back three days. Is this making sense?

Bob

Thanks raskew. It works, and I understand what it is doing.

Thanks again
-Chad