Trying to find Total Lapsed Days, (98)

Topic

New Reply

Trying to find Total Lapsed Days,

I’m trying to find the total lapsed days on a rental. I’m wanting to figure out the days between Ship Date and Return Date. I need to subtract the Ship Date from the Return Date. I’m having trouble doing this, is this one of Access limitations. It seems to have trouble with months and days.

Then i have one other question if the date is formatted in Short (04/23/03) how can I sort by months to see all items released that month.

Reply | Quote

Replies

Is this related to post 256915? You didn’t answer the last question I asked there…

Reply | Quote

Yes it is. I had tried that too, and it didn’t work.
That is why I’m asking is that a limitation to Access, or not.
I’m also trying to see if there is a way i can make a report that look at all item released in a month.
I can make look up boxes (or filter boxes) for reports but how would you make one that looks for a month.

Reply | Quote

For sure Access is able to calculate lapsed days; it should be very easy, nothing special is required. Are you sure that ReleaseDate and ReturnDate are both defined as Date/Time fields?

To filter for a specific month, I would use a query based on the table (or query) that contains the data. Add calculated fields to the query grid:
ReleaseMonth: Month([ReleaseDate])
ReleaseYear: Year([ReleaseDate])
You can then set criteria for these calculated fields. These can be fixed or dynamic:

• To return all items released in November 2002, set the criteria for MonthReleased to 11 and for YearReleased to 2002.
• To return all items released this month, set the criteria for MonthReleased to Month(Date()) and for YearReleased to Year(Date()).
  [/list]If you still can’t get the lapsed days to work, post more details about the ReleaseDate and ReturnDate fields.

Reply | Quote

Thanks HansV. It works I’ve check and double checked all properties of each field and it would not work but after the 100 time I found the mistake. Thanks again for all your guys help. I have yet to try the Filter, but thanks again.

Reply | Quote

I am not a pro at this but I think I can answer this for you.

To subtract two days you need to enclose the dates in a bracket, [Return Date]-[Ship Date].

The second is done by using sort, I presume you are using a query, in the Sort row set it to Ascending/Descending and set a filter for the month only.

Reply | Quote