ControlSource

[Guest]

I am creating a database that auto schedules service calls. So far I've had a
few run ins but right now I am stuck. I want to auto calculate the next
service date

in my table I have a lastservice date, next service date, and service interval
I am using the following formula in the nextservice date field.
=DateAdd("m",[ServiceInterval],[ServiceDate])

what I want to do is after the service has been completed add a check box.
when the check box is checked I want it to automatically schedule a next
service date, by moving the current servicedate to the lastservice date and
then my above formula will automatically do the rest.

please help me.

 

[Allen Browne]

Calvin, one of the basic rules of data design is not to store calculated
results. Do not store any LastServiceDate field in your table. Instead, get
Access to calculate the date for you with a query.

The query will depend on your fields, of course, but it will probably end up
looking somethingn like this:

SELECT VehicleID,
DateAdd("m", [ServiceInterval], Max([ServiceDate]) AS LastService
FROM tblService
GROUP BY VehicleID;

(In reality, there may be much more involved than this, e.g. where a service
may be due "after 5000 miles or 3 months, whichever comes first", or where
there are A, B, and C-level services, some of which incorporate the others,
or where some parts need periodic replacement that must be tracked
separately from the services, or ...)

 

[Guest]

There is no need to save the next service date in the table.
You can always get the desire resault using a query

Select TableName.* , DateAdd("m",[ServiceInterval],[ServiceDate]) As [next
service date] From TableName

Adding the check box
Select TableName.* ,
IIf([CheckBoxName]=True,DateAdd("m",[ServiceInterval],[ServiceDate]),Null) As
[next service date] From TableName

In the text box control source within the form you can write
=IIf([CheckBoxName]=True,DateAdd("m",[ServiceInterval],[ServiceDate]),Null)

It will display the next sevice name if the check box will be checked

 

[Guest]

I am not really following you. I need to store the last service date b/c I
need to know the service history. Also how would I do that in Access not
using SQL?

Calvin, one of the basic rules of data design is not to store calculated
results. Do not store any LastServiceDate field in your table. Instead, get
Access to calculate the date for you with a query.

The query will depend on your fields, of course, but it will probably end up
looking somethingn like this:

SELECT VehicleID,
DateAdd("m", [ServiceInterval], Max([ServiceDate]) AS LastService
FROM tblService
GROUP BY VehicleID;

(In reality, there may be much more involved than this, e.g. where a service
may be due "after 5000 miles or 3 months, whichever comes first", or where
there are A, B, and C-level services, some of which incorporate the others,
or where some parts need periodic replacement that must be tracked
separately from the services, or ...)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am creating a database that auto schedules service calls. So far I've had
a
few run ins but right now I am stuck. I want to auto calculate the next
service date

in my table I have a lastservice date, next service date, and service
interval
I am using the following formula in the nextservice date field.
=DateAdd("m",[ServiceInterval],[ServiceDate])

what I want to do is after the service has been completed add a check box.
when the check box is checked I want it to automatically schedule a next
service date, by moving the current servicedate to the lastservice date
and
then my above formula will automatically do the rest.

please help me.

 

[Douglas J. Steele]

If you need service history, then presumably you need more than just the
LastServiceDate: you'll want the dates of all servicing.

That implies you need another table: one that has a row for each servicing,
linked to your existing table.

You wouldn't need to store the expected date for the next service: you can
always calculate that.

I don't really understand your question about "how would I do that in Access
not using SQL?". Access is a database product, and SQL is the lingua franca
of database applications.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am not really following you. I need to store the last service date b/c I
need to know the service history. Also how would I do that in Access not
using SQL?

Calvin, one of the basic rules of data design is not to store calculated
results. Do not store any LastServiceDate field in your table. Instead,
get
Access to calculate the date for you with a query.

The query will depend on your fields, of course, but it will probably end
up
looking somethingn like this:

SELECT VehicleID,
DateAdd("m", [ServiceInterval], Max([ServiceDate]) AS LastService
FROM tblService
GROUP BY VehicleID;

(In reality, there may be much more involved than this, e.g. where a
service
may be due "after 5000 miles or 3 months, whichever comes first", or
where
there are A, B, and C-level services, some of which incorporate the
others,
or where some parts need periodic replacement that must be tracked
separately from the services, or ...)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am creating a database that auto schedules service calls. So far I've
had
a
few run ins but right now I am stuck. I want to auto calculate the next
service date

in my table I have a lastservice date, next service date, and service
interval
I am using the following formula in the nextservice date field.
=DateAdd("m",[ServiceInterval],[ServiceDate])

what I want to do is after the service has been completed add a check
box.
when the check box is checked I want it to automatically schedule a
next
service date, by moving the current servicedate to the lastservice date
and
then my above formula will automatically do the rest.

please help me.

 

[Pieter Wijnen]

But it should be saved in a log table instead of in the "serviceable
objects" table.
The only one of your fields that needs to be stored there is the service
interval.

Pieter

I am not really following you. I need to store the last service date b/c I
need to know the service history. Also how would I do that in Access not
using SQL?

Calvin, one of the basic rules of data design is not to store calculated
results. Do not store any LastServiceDate field in your table. Instead,
get
Access to calculate the date for you with a query.

The query will depend on your fields, of course, but it will probably end
up
looking somethingn like this:

SELECT VehicleID,
DateAdd("m", [ServiceInterval], Max([ServiceDate]) AS LastService
FROM tblService
GROUP BY VehicleID;

(In reality, there may be much more involved than this, e.g. where a
service
may be due "after 5000 miles or 3 months, whichever comes first", or
where
there are A, B, and C-level services, some of which incorporate the
others,
or where some parts need periodic replacement that must be tracked
separately from the services, or ...)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am creating a database that auto schedules service calls. So far I've
had
a
few run ins but right now I am stuck. I want to auto calculate the next
service date

in my table I have a lastservice date, next service date, and service
interval
I am using the following formula in the nextservice date field.
=DateAdd("m",[ServiceInterval],[ServiceDate])

what I want to do is after the service has been completed add a check
box.
when the check box is checked I want it to automatically schedule a
next
service date, by moving the current servicedate to the lastservice date
and
then my above formula will automatically do the rest.

please help me.

 

[Guest]

I've created a servicelog table to log all the service dates. Now how would I
go about calculating the dates???? I have a service interval for each
customer but I need it to calculate from the last service date. I am just
getting even more confused here. Here are my tables:

tblCustomer
CustomerID (PK)
FirstName
LastName
Address
Phone

tblService
ServiceID(PK)
CustomerID(FK)
ServiceInterval
TypeofService

tblLog
LogID(PK)
ServiceID(FK)
ServiceDate

I need to be able to add a service date and it automatically tell me when
the next one is. Where would I do this in a query or a report? and how would
i do it so that it keeps cycling?

thanks so much sorry to be a pain.

If you need service history, then presumably you need more than just the
LastServiceDate: you'll want the dates of all servicing.

That implies you need another table: one that has a row for each servicing,
linked to your existing table.

You wouldn't need to store the expected date for the next service: you can
always calculate that.

I don't really understand your question about "how would I do that in Access
not using SQL?". Access is a database product, and SQL is the lingua franca
of database applications.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am not really following you. I need to store the last service date b/c I
need to know the service history. Also how would I do that in Access not
using SQL?

Calvin, one of the basic rules of data design is not to store calculated
results. Do not store any LastServiceDate field in your table. Instead,
get
Access to calculate the date for you with a query.

The query will depend on your fields, of course, but it will probably end
up
looking somethingn like this:

SELECT VehicleID,
DateAdd("m", [ServiceInterval], Max([ServiceDate]) AS LastService
FROM tblService
GROUP BY VehicleID;

(In reality, there may be much more involved than this, e.g. where a
service
may be due "after 5000 miles or 3 months, whichever comes first", or
where
there are A, B, and C-level services, some of which incorporate the
others,
or where some parts need periodic replacement that must be tracked
separately from the services, or ...)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am creating a database that auto schedules service calls. So far I've
had
a
few run ins but right now I am stuck. I want to auto calculate the next
service date

in my table I have a lastservice date, next service date, and service
interval
I am using the following formula in the nextservice date field.
=DateAdd("m",[ServiceInterval],[ServiceDate])

what I want to do is after the service has been completed add a check
box.
when the check box is checked I want it to automatically schedule a
next
service date, by moving the current servicedate to the lastservice date
and
then my above formula will automatically do the rest.

please help me.

 

[Pieter Wijnen]

Nz(DMax("ServiceDate","tblLog","ServiceID=" & Me.ServiceID.Value),Date())

Should be pretty close

Pieter

I've created a servicelog table to log all the service dates. Now how
would I
go about calculating the dates???? I have a service interval for each
customer but I need it to calculate from the last service date. I am just
getting even more confused here. Here are my tables:

tblCustomer
CustomerID (PK)
FirstName
LastName
Address
Phone

tblService
ServiceID(PK)
CustomerID(FK)
ServiceInterval
TypeofService

tblLog
LogID(PK)
ServiceID(FK)
ServiceDate

I need to be able to add a service date and it automatically tell me when
the next one is. Where would I do this in a query or a report? and how
would
i do it so that it keeps cycling?

thanks so much sorry to be a pain.

If you need service history, then presumably you need more than just the
LastServiceDate: you'll want the dates of all servicing.

That implies you need another table: one that has a row for each
servicing,
linked to your existing table.

You wouldn't need to store the expected date for the next service: you
can
always calculate that.

I don't really understand your question about "how would I do that in
Access
not using SQL?". Access is a database product, and SQL is the lingua
franca
of database applications.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am not really following you. I need to store the last service date b/c
I
need to know the service history. Also how would I do that in Access
not
using SQL?

:

Calvin, one of the basic rules of data design is not to store
calculated
results. Do not store any LastServiceDate field in your table.
Instead,
get
Access to calculate the date for you with a query.

The query will depend on your fields, of course, but it will probably
end
up
looking somethingn like this:

SELECT VehicleID,
DateAdd("m", [ServiceInterval], Max([ServiceDate]) AS LastService
FROM tblService
GROUP BY VehicleID;

(In reality, there may be much more involved than this, e.g. where a
service
may be due "after 5000 miles or 3 months, whichever comes first", or
where
there are A, B, and C-level services, some of which incorporate the
others,
or where some parts need periodic replacement that must be tracked
separately from the services, or ...)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am creating a database that auto schedules service calls. So far
I've
had
a
few run ins but right now I am stuck. I want to auto calculate the
next
service date

in my table I have a lastservice date, next service date, and
service
interval
I am using the following formula in the nextservice date field.
=DateAdd("m",[ServiceInterval],[ServiceDate])

what I want to do is after the service has been completed add a
check
box.
when the check box is checked I want it to automatically schedule a
next
service date, by moving the current servicedate to the lastservice
date
and
then my above formula will automatically do the rest.

please help me.