Due date query

[Melissa]

Hi. I'm building a database for a garage. I'm making a query for the
"maintenance due". I've used this expression to give me the due dates: MAINT
DUE: DateSerial(Year([Last Routine]),Month([last routine])+[Maint
Sched],Day([last routine]))

Now, I want to limit these results to any maintenance due prior to today, or
use a parameter expression. I can't figure out how to do either and keep
getting error messages such as "data type mismatch" or "this expression is
too complex to be evaluated"

Please help. This seems like it should be so simple. Thanks

 

[KARL DEWEY]

You have too much of one kind and not enough of the other.
Maintenace services have different intervals based on what is performed.
Oil change would be three months or 3000 miles but brakes would be six months
or 30,000 miles.
So you need to not only record the [Last routine] but what was done and the
mileage.
If by number of days onlt then [Last routine] with criteria like ---
<=DateAdd("m", -3, Date())
This check to see if the [Last routine] was at least 3 months ago. Better
still would be like this ---
<=DateAdd("m", -3, Date())-7
This will say it is due seven days before actual due date so it can be
scheduled in before it is overdue.

 

[Melissa]

Actually, the maintenance timing is already set the way they want it. That
is not the part I need help with. The part I want to know how to do is to
limit those results by which vehicles have maintenance due prior to today, or
within the next month.
--
Melissa

You have too much of one kind and not enough of the other.
Maintenace services have different intervals based on what is performed.
Oil change would be three months or 3000 miles but brakes would be six months
or 30,000 miles.
So you need to not only record the [Last routine] but what was done and the
mileage.
If by number of days onlt then [Last routine] with criteria like ---
<=DateAdd("m", -3, Date())
This check to see if the [Last routine] was at least 3 months ago. Better
still would be like this ---
<=DateAdd("m", -3, Date())-7
This will say it is due seven days before actual due date so it can be
scheduled in before it is overdue.

--
KARL DEWEY
Build a little - Test a little

Hi. I'm building a database for a garage. I'm making a query for the
"maintenance due". I've used this expression to give me the due dates: MAINT
DUE: DateSerial(Year([Last Routine]),Month([last routine])+[Maint
Sched],Day([last routine]))

Now, I want to limit these results to any maintenance due prior to today, or
use a parameter expression. I can't figure out how to do either and keep
getting error messages such as "data type mismatch" or "this expression is
too complex to be evaluated"

Please help. This seems like it should be so simple. Thanks

 

[John Spencer]

Is there ALWAYS a value in the field Last Routine and in the Maint Sched
field? If not, then the DateSerial function will generate an error. And if
there is an error in the calculation then when you try to apply criteria
against the calculation, you will get one of the two error messages you have
noted - usually this expressionis too complex.

Try the following for your calculation,
Field: DUE: IIF([Last Routine] Is Not Null,
DateSerial(Year([Last Routine]),Month([last routine])+Nz([Maint
Sched],30),Day([last routine]))
, Null)

And then your criteria should be
Criteria: < Date()
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Melissa]

That expression works without giving me error messages, however, now my due
dates aren't coming out right. I have them inputted by months: 3, 6, 9, 12,
etc.. But a vehicle with 12 month maintenance schedule is now turning out to
be due 2 1/2 years later (30 months). Also, a dialog box pops up and wants
me to input something. If those couple of "bugs" could be worked out, I
think I'd have it.

Thanks
--
Melissa

Is there ALWAYS a value in the field Last Routine and in the Maint Sched
field? If not, then the DateSerial function will generate an error. And if
there is an error in the calculation then when you try to apply criteria
against the calculation, you will get one of the two error messages you have
noted - usually this expressionis too complex.

Try the following for your calculation,
Field: DUE: IIF([Last Routine] Is Not Null,
DateSerial(Year([Last Routine]),Month([last routine])+Nz([Maint
Sched],30),Day([last routine]))
, Null)

And then your criteria should be
Criteria: < Date()
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. I'm building a database for a garage. I'm making a query for the
"maintenance due". I've used this expression to give me the due dates:
MAINT
DUE: DateSerial(Year([Last Routine]),Month([last routine])+[Maint
Sched],Day([last routine]))

Now, I want to limit these results to any maintenance due prior to today,
or
use a parameter expression. I can't figure out how to do either and keep
getting error messages such as "data type mismatch" or "this expression is
too complex to be evaluated"

Please help. This seems like it should be so simple. Thanks

 

[John Spencer]

WHAT does the dialog box say? Does it have a title?

Field: DUE: IIF([Last Routine] Is Not Null,
DateSerial(Year([Last Routine]),Month([last routine])+Nz([Maint
Sched],30),Day([last routine])) , Null)

Does the field Maint Sched contain 3,6,9,12, etc?
What is in Last Routine?

The Nz([Maint Sched],30) will default to adding 30 months (2.5 years) if
there is nothing in Maint Sched? Do you have a field with that name in your
table? IF not, the dialog is asking you to fill in a value for this unknown
object.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

That expression works without giving me error messages, however, now my
due
dates aren't coming out right. I have them inputted by months: 3, 6, 9,
12,
etc.. But a vehicle with 12 month maintenance schedule is now turning out
to
be due 2 1/2 years later (30 months). Also, a dialog box pops up and
wants
me to input something. If those couple of "bugs" could be worked out, I
think I'd have it.

Thanks
--
Melissa

Is there ALWAYS a value in the field Last Routine and in the Maint Sched
field? If not, then the DateSerial function will generate an error. And
if
there is an error in the calculation then when you try to apply criteria
against the calculation, you will get one of the two error messages you
have
noted - usually this expressionis too complex.

Try the following for your calculation,
Field: DUE: IIF([Last Routine] Is Not Null,
DateSerial(Year([Last Routine]),Month([last routine])+Nz([Maint
Sched],30),Day([last routine]))
, Null)

And then your criteria should be
Criteria: < Date()
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. I'm building a database for a garage. I'm making a query for the
"maintenance due". I've used this expression to give me the due dates:
MAINT
DUE: DateSerial(Year([Last Routine]),Month([last routine])+[Maint
Sched],Day([last routine]))

Now, I want to limit these results to any maintenance due prior to
today,
or
use a parameter expression. I can't figure out how to do either and
keep
getting error messages such as "data type mismatch" or "this expression
is
too complex to be evaluated"

Please help. This seems like it should be so simple. Thanks

 

[Melissa]

I found the problem. Somehow, a couple of extra spaces got in the formula
throwing of [Maint Sched]. It now works. Thank you so much for your help,
John. I couldn't have figured this out without your help.
--
Melissa

WHAT does the dialog box say? Does it have a title?

Field: DUE: IIF([Last Routine] Is Not Null,
DateSerial(Year([Last Routine]),Month([last routine])+Nz([Maint
Sched],30),Day([last routine])) , Null)

Does the field Maint Sched contain 3,6,9,12, etc?
What is in Last Routine?

The Nz([Maint Sched],30) will default to adding 30 months (2.5 years) if
there is nothing in Maint Sched? Do you have a field with that name in your
table? IF not, the dialog is asking you to fill in a value for this unknown
object.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

That expression works without giving me error messages, however, now my
due
dates aren't coming out right. I have them inputted by months: 3, 6, 9,
12,
etc.. But a vehicle with 12 month maintenance schedule is now turning out
to
be due 2 1/2 years later (30 months). Also, a dialog box pops up and
wants
me to input something. If those couple of "bugs" could be worked out, I
think I'd have it.

Thanks
--
Melissa

Is there ALWAYS a value in the field Last Routine and in the Maint Sched
field? If not, then the DateSerial function will generate an error. And
if
there is an error in the calculation then when you try to apply criteria
against the calculation, you will get one of the two error messages you
have
noted - usually this expressionis too complex.

Try the following for your calculation,
Field: DUE: IIF([Last Routine] Is Not Null,
DateSerial(Year([Last Routine]),Month([last routine])+Nz([Maint
Sched],30),Day([last routine]))
, Null)

And then your criteria should be
Criteria: < Date()
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. I'm building a database for a garage. I'm making a query for the
"maintenance due". I've used this expression to give me the due dates:
MAINT
DUE: DateSerial(Year([Last Routine]),Month([last routine])+[Maint
Sched],Day([last routine]))

Now, I want to limit these results to any maintenance due prior to
today,
or
use a parameter expression. I can't figure out how to do either and
keep
getting error messages such as "data type mismatch" or "this expression
is
too complex to be evaluated"

Please help. This seems like it should be so simple. Thanks