Date Add + Value in another field = due date (1 Viewer)

[bdhtexas]

I searched the forums and I couldn't find my answer.

Here is situation: For example:
We receive an appeal 10/1/06, the State mandates that we complete the Appeal in 45 calendar days. We send the Appeal to claims for handling and sometimes they don't reply back in time and we get fined. So, I want a due date on the form to show the date a reply is needed to the State.

I have a received date field
I have a sent to claims date field
I have a due date field
I have a State field
I have a follow-up days State field

Can you have a date add function that adds the number of follow-up days depending on the State, there are several different date rules, some States are 20 calendar and some are 30 calendar days.

I might be better off just running queries that lists the claims that are close to the due date.

And I might be totally off here, so if you have any suggestions or questions, let me know.

Thanks!

 

[Matt Greatorex]

The DateAdd function should be what you need.

 

[bdhtexas]

I am just not understanding how I can do that.

Example:
California is 15 calendar days
Arizona is 30 calendar days
Virginia is 60 calendar days

Can the date add function grab the # of days needed to figure the due date based on that number being contained in a field of it's own and based on that number being different depending upon what State it is?

 

[Matt Greatorex]

You would store the name of each state and the number of days pertaining to each in a table.

Now, say you have Texas = 40 days, California = 45, etc., when you enter a date value into a field on the form and a state value into another field, you could use the DLookup() function to find the number of days relating to that state e.g.

DaysToAdd = Dlookup("[I]name of field[/I]","[I]table name[/I]", ""StateName = '" & [I]name of state[/I] & "'")

Now you have the number of days to add on, you can use the DateDiff function to add that onto the date you first entered and display the new date in a third field.

Hope that helps.

 

[bdhtexas]

OKay, I already have a table with the State and the number of days pertaining to each.

What if I already have a TEXT BOX on my form that displays the number of days for the State? Example: Jim House CA 45days sent to claims 11/1/06. All this info is on my form and I just need a due date of 12/16/06. Oh, I see another problem, if it falls on a Saturday it needs to show the next business day. Oh well, if I can at least get the due date to show first, that would be great.

I think my biggest hurdle is trying to figure out how to input the actual dateadd function that's needed.

 

[CEH]

I would think a simple "If...Then"... or a "Select case" statement could be used...
Something like....

If [StateTextBox] = "CA" then
[DueDateTextbox] = [DateTextBox] + 45
endif

 

[boblarson]

CEH: DateAdd is still what they would want to use, even if they used your suggestion as [DateTextBox] + 45 will not guarantee the correct date, even if it does do it (which I'm not sure that would actually work). DateAdd will do it correctly, for sure each time.

 

[CEH]

Bob, I know dealing with weekends this would not work... But it seems to work with other dates I have tried. Take a look at this and tell me when it wouldn't work... I know your a lot better at coding then I am Not tryin to be a "smart@@@" here ..... I also would just like to know the correct method.
Thanks

 

[bdhtexas]

I still can't figure this out... here's what I tried

DateAdd("d",[Tech Unit Database Table]![Complaint Date/Rcvd]+[State]![Calendar Days])

 

[Rich]

DateAdd("d",[State]![Calendar Days],[Tech Unit Database Table]![Complaint Date/Rcvd])