Date Question!

[Guest]

I want to calculate the turn times of my records but there is something
tricky about it... I am now able to subtract the weekends and holidays but
the thing is that if there is holiday, the turn times would be plus 2 days
e.g.,
To process a 1040, it takes upto 2 days excluding weekends and Holidays so
if I received it on 12/30/05 then the document would be late on 01/09/06..
this is how my statement looks like:

="CompTime (1040, 1065,1120): " &
Abs(Sum(IIf(TaxDocStatus="Complete",(IIf(((TaxDocName In
("1040","1065","1120")) And
DateDiff("d",RqstDateRcvd,TxDocDateRcvd)-DateDiff("ww",TxDocDateRcvd,RqstDateRcvd,7)-DateDiff("ww",TxDocDateRcvd,RqstDateRcvd,1)-
DCount("*","Holiday","HdyDate Between " &
format$(RqstDateRcvd,"\#mm\/dd\/yyyy\#") & " And " &
Format$(TxDocDateRcvd,"\#mm\/dd\/yyyy\#"))<=2),1,0)))))


The above works well when in a query but crashes when I put it in my unbound
textbox in my report, it crashes any reason why??

 

[Wayne Morgan]

If you entered it exactly as you have it in your post (assuming it is all on
one line, since the newsreader won't allow a line that long) then you still
have the name of the calculated field in the statement as you would have it
in the query design grid. This syntax only works in the query design grid.
If you go to the SQL view of the query, you'll see that it really isn't even
stored that way in the query. The textbox doesn't know what to do with the
field name, since it is only going to display the value determined by the
equation. It doesn't need a field name, as the query does, since the textbox
only displays one record at a time. You could also look at it as the label
for the textbox is the "field name", but this isn't an exact analogy.

="CompTime (1040, 1065,1120): " &
Abs(Sum(IIf(TaxDocStatus="Complete",(IIf(((TaxDocName In
("1040","1065","1120")) And

The first line above should be removed and place the = in front of
"Abs(....".

=Abs(Sum(IIf(TaxDocStatus="Complete",(IIf(((TaxDocName In
("1040","1065","1120")) And

 

[Guest]

Thanks for the reply, the first part of my question add 2 more days to the
turn time was not answered, do you have any ideas?

 

[Wayne Morgan]

Rather than just using DateAdd or DateDiff, you need to create a user
defined function that will calculate the date for you. This function will
remove Saturday and Sunday and use a table of holidays that you've defined
so that they aren't included in the calculation. You will find an example of
doing this here:

http://www.mvps.org/access/datetime/date0012.htm