Another Workday Add Question

[Gootz11]

thread701-1136806
Hello,
I been trying to follow the steps in thread thread701-1136806 to add my fields [P/U day] + [Transit days] while taking into account weekends. I'm however getting a zero as my value. not sure what im doing wrong but here's my Query

SELECT [Estimated Delivery Date].[Ship (P/U) Date], [Estimated Delivery Date].[SERVICE DAYS],
Deltadays([ship (p/u) Date],[service days]) AS ESTDLVDAY
FROM [Estimated Delivery Date];

I should probably mention that [transit days] is a numeric field not a date field. (so [ship (p/u) date]= 03/06/09 and [service days] = 1, I need my returned date to = 03/09/09 [ESTDLVDAY]

 

[HarleyQuinn]

The function in the FAQ listed in the thread you posted is expecting two dates, that is why I'd imagine you're getting 0 (as long as you've followed all the other steps) as you're passing in a number.

What you could do is modify the function to take a number and then add that to the first date parameter you pass in (that would be my choice) or you could pass in the date initially e.g.(untested):

SELECT [Estimated Delivery Date].[Ship (P/U) Date], [Estimated Delivery Date].[SERVICE DAYS],
Deltadays([ship (p/u) Date],DateAdd("d",[service days],[ship (p/u) Date]) AS ESTDLVDAY
FROM [Estimated Delivery Date];

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Gootz11]

Thanks for the reply. I actually took a different much easier approach but now i'm having a different issue. what i'm doing now is getting the actual delivery days it took, for example, package was picked up on 3/05/09 and it was delivered on 3/09/09 therefore my delivery days should be 2. the issue i'm having now is that when i have a pick up day of 3/05/09 and a delivery day of 3/06/09 i'm getting delivery days of 2 where it should be 1. i'm using the following:
DLVDAYS: Deltadays([ship (p/u) date],[deliverydate])
Not sure why it works when i'm adding days it works for every scenario except when the delivery day is the next day. Please help!