Due within 2 months report

[CDF]

Hi,

I'm having trouble with a query.

I've a got a Monitoring due date field where you type a date where
some monitoring is due (obviously!). If the date in the field is 1st
March 2009, then as at the date I write this that means there are 20
days to go before the monitoring is due.

I've got a report that shows all the dates that are due within 2
months, but they only show in the list up to 5 days. From 4 days to go
onward they won't show.

I've got the following filter in my query

Field: Days Overdue: Format$((Date()-
[vehicle_Battery_MonitoringDueDate])/365.25,"0.00")
Criteria: Between "-0.16" And "-0.02"

I've obviously got the criteria wrong or is it the formula??? If I put
the date as 10th April 2009 it shows on the report as 60 days to go.
If I put 11th April it goes off so therefore the -0.16 must be ok???
I got this code from an old database I did many years ago, but it
calculated the date from a date obtained, not a date due. Therefore
I'm totally confused now as I had to use negative values to get even
close to what I'm trying to achieve. Unfortunately this database
requires a due date and not a date obtained.

Then I've got a a report that shows how many days overdue with the
below code in the query, but this only makes the items that don't show
(within 4 days) above as an item overdue -4 days.

Field: Days Overdue: Format$((Date()-
[vehicle_Battery_MonitoringDueDate])/365.25,"0.00")
Criteria: <"-0.02"

Any help would be appreciated.

 

[banem2]

Hi,

I'm having trouble with a query.

I've a got a Monitoring due date field  where you type a date where
some monitoring is due (obviously!). If the date in the field is 1st
March 2009, then as at the date I write this that means there are 20
days to go before the monitoring is due.

I've got a report that shows all the dates that are due within 2
months, but they only show in the list up to 5 days. From 4 days to go
onward they won't show.

I've got the following filter in my query

Field: Days Overdue: Format$((Date()-
[vehicle_Battery_MonitoringDueDate])/365.25,"0.00")
Criteria: Between "-0.16" And "-0.02"

I've obviously got the criteria wrong or is it the formula??? If I put
the date as 10th April 2009 it shows on the report as 60 days to go.
If I put 11th April it goes off so therefore the -0.16 must be ok???
I got this code from an old database I did many years ago, but it
calculated the date from a date obtained, not a date due. Therefore
I'm totally confused now as I had to use negative values to get even
close to what I'm trying to achieve.  Unfortunately this database
requires a due date and not a date obtained.

Then I've got a a report that shows how many days overdue with the
below code in the query, but this only makes the items that don't show
(within 4 days) above as an item overdue -4 days.

Field: Days Overdue: Format$((Date()-
[vehicle_Battery_MonitoringDueDate])/365.25,"0.00")
Criteria: <"-0.02"

Any help would be appreciated.

Hi,

I think you will need to use DateDiff() function to get a number of
days and then to filter by real numbers. Like:

DateDiff("d", #1/1/2008#, #1/1/2009#)

will return 366

or in your case

Days Overdue: DateDiff("d", [vehicle_Battery_MonitoringDueDate], Date
())

Now you can filter data by real number of days, like <4 or so.

Regards,
Branislav Mihaljev
Mirosoft Access MVP

 

[CDF]

Hi,

I think you will need to use DateDiff() function to get a number of
days and then to filter by real numbers. Like:

DateDiff("d", #1/1/2008#, #1/1/2009#)

will return 366

or in your case

Days Overdue: DateDiff("d", [vehicle_Battery_MonitoringDueDate], Date
())

Now you can filter data by real number of days, like <4 or so.

Regards,
Branislav Mihaljev
Mirosoft Access MVP

Thank you Branislav. You are a legend!

It works perfectly!!

Just a note to others wanting to use this feature, when using an
upcoming date like I did, the overdue query should contain; Days
Overdue: DateDiff("d", [FeildName], Date()) and the 'Upcoming' query
should contain; Days Due In: DateDiff("d",Date(),[Fieldname]). The
difference being the order in the code of the Date() function and visa
versa the [FieldName].

Thank you again Branislav.