Date problem (2000)

Topic

New Reply

Neb,
Can your query not just have a column with the criteria
=[requestdate] + 15

Also, I do not understand the significance of the received date, is there something you’re trying to do with it?

Reply | Quote

Replies

My question revolves around a date calculation. I am designing a query that calculates a due date from a request date that a someone types in. However there is a required add info date and time field that will stop the time elapsed. Once there is approval ,a received approval date and time in typed in . I need a calculation that would would calculate from request date to a time in the future(say 15 days) but not count the time between required add info date date and received approval date. Has anyone run across this before?

Thanks,
Neb

Reply | Quote

I wasn’t clear on my fields so I redid the post. The problem exists when there is a request add info date and recd add info date. The time stops between these 2 points and starts again after the reced add info date. Sorry for the error.

Neb

Reply | Quote

No worries, I just wanted to know more specifically what you were trying to achieve. Check out my example.
In it you will see tblOrderRequests, with the fields:
ID,
REQUEST_RECVD_DATE,
REQUEST_REVIEW_DATE,
REQUEST_APPROVED_DATE, and
REQUESTED_ACTION_DATE.
All the date fields are formatted as Date/Time (Short Date).

You will see there is also a query, named qryNumberOfDays

Field one is bound to your table, with ID (the order ID), and Field two is a calculated field, named Number Of Days. Don’t forget to select the calculated field, right click and go to properties. Change its format to ‘Number’. The calculated field is created as such:

Number Of Days: (([requested_action_date]-[request_recvd_date])-([request_approved_date]-[request_review_date]))

The formula above does exactly what you said, just a little bit simplified. It takes the total number of days between the submission of the request and the requested action date, and simply subtracts the number of days the request was away for revision.

Hope this helps

Reply | Quote