Displaying Inspection Due Date

[kevtrucker via AccessMonster.com]

Hopefully i am posting this in the correct section.

i have a form that shows vehicle details,from the vehicle table, one of the
fields i need to show the due date for the next inspection, this will be a
calculated field as i need to retrieve the date of the last inspection from a
2nd table that holds the booking details then add 6wks onto that date and
display the result. The main fields in the booking table are as follows,
jobnumber, date, regnumber, mileage, etc. the fields i need to work with from
the vehicle table are regnumber and inspduedate.

Each vehicle is booked in on various occasions for inspections, services, mot
and other jobs, the is a yes/no field to say if it is an inspection.

How do i populate the inspdue field in the vehicles form from the last
inspection carried out in the booking form? The vehicle will have had upto
roughly 8 inspections and i need to know when the last one was.

I hope that makes sense! I am fairly sure i need to use Dmax but cannot find
out how to do it correctly.

Regards

Kevin

 

[John W. Vinson]

Hopefully i am posting this in the correct section.

i have a form that shows vehicle details,from the vehicle table, one of the
fields i need to show the due date for the next inspection, this will be a
calculated field as i need to retrieve the date of the last inspection from a
2nd table that holds the booking details then add 6wks onto that date and
display the result. The main fields in the booking table are as follows,
jobnumber, date, regnumber, mileage, etc. the fields i need to work with from
the vehicle table are regnumber and inspduedate.

Each vehicle is booked in on various occasions for inspections, services, mot
and other jobs, the is a yes/no field to say if it is an inspection.

How do i populate the inspdue field in the vehicles form from the last
inspection carried out in the booking form? The vehicle will have had upto
roughly 8 inspections and i need to know when the last one was.

I hope that makes sense! I am fairly sure i need to use Dmax but cannot find
out how to do it correctly.

Regards

Kevin

You'll need both the DMax() and the DateAdd() functions. However, I would
question wehter you should *STORE* the due date in the table at all, if it can
be calculated from the actual inspection date! Storing the due date would be
redundant, and you could have a value stored which is inconsistant with the
inspection date. Is that OK? Do you want to be able to edit the due date
manually, overriding the calculated date?

The calculation would be something like

=DateAdd("ww", 6, DMax("[Date]", "[2nd table name]", "[Regnumber] = '" &
[Regnumber] & "'"))

assuming that regnumber is a Text field.

 

[KARL DEWEY]

Try this --
SELECT [vehicle].[regnumber], DateAdd("w", 6, (SELECT Max([Date]) FROM
[Booking] [XX] WHERE [XX].[regnumber] = [vehicle].[regnumber] AND
[Booking].[inspection] = -1)) AS Due_Inspection
FROM [vehicle] LEFT JOIN [Booking] ON [vehicle].[regnumber] =
[Booking].[regnumber];

--
Disclaimer: This author may have received products and services, free, at or
below market price, mentioned in this post at or below cost equal to that of
consumer. Mention and/or description of a product or service herein does not
constitute endorsement thereof. Any code or psuedocode included in this post
is offered "as is", with no guarantee as to suitability or functionality. You
can thank the FTC of the USA for making this disclaimer possible/necessary.

 

[kevtrucker via AccessMonster.com]

John

Thanks for your reply, it was most helpful, i am now partly there!

I understand what you were saying about the due date being a calculated field,
i did say it was going to be a calculated field, but probably not very
clearly. It will only be used to flag up when the next inspection is due.

Now i require further assistance, i will put an example to try and explain
what i need

a job is booked in with the booking table, fields as follows

Job number Regnumber Date Insp Service plus 4 others. (insp &
Service are yes/no fields)

32230 PO58DHD 15/10/09
31897 PO58DHD 11/09/09
31367 PO58DHD 21/08/09 y y

Your reply gives me a due date 6 weeks after the last job for that vehicle,
which is 6 weeks after 15/10/09 in example, i need it to tell me 6 weeks
after last inspection, which would be 6 weeks after 21/08/09 in example. So i
would need to use the insp field in the calculation.

Secondly, the vehicles have different inspection schedules, ie 6,8,10 or 12
weekly, i have a field in my vehicles table that tells me which it is, how
can i use that field to say due date is 6,8,10 or 12 weeks?

Thanks for your assistance it is much appreciated.

PS. Another, possibly more difficult question is, i have noticed from various
other posts while trying to sort this problem that i have used a reseved word
for the date field, Date, how can i change the field name without losing any
of the data held in that field, there are about 12,000 records in the booking
table?

Thanks again
Kevin

Hopefully i am posting this in the correct section.

[quoted text clipped - 19 lines]

Kevin

You'll need both the DMax() and the DateAdd() functions. However, I would
question wehter you should *STORE* the due date in the table at all, if it can
be calculated from the actual inspection date! Storing the due date would be
redundant, and you could have a value stored which is inconsistant with the
inspection date. Is that OK? Do you want to be able to edit the due date
manually, overriding the calculated date?

The calculation would be something like

=DateAdd("ww", 6, DMax("[Date]", "[2nd table name]", "[Regnumber] = '" &
[Regnumber] & "'"))

assuming that regnumber is a Text field.

 

[kevtrucker via AccessMonster.com]

Karl

Thanks for your reply.

I have tried it and can't get it to work, just get a #name error!

Am i supposed to substitute something for the XX's after [booking] and WHERE ?


Regards
Kevin

Try this --
SELECT [vehicle].[regnumber], DateAdd("w", 6, (SELECT Max([Date]) FROM
[Booking] [XX] WHERE [XX].[regnumber] = [vehicle].[regnumber] AND
[Booking].[inspection] = -1)) AS Due_Inspection
FROM [vehicle] LEFT JOIN [Booking] ON [vehicle].[regnumber] =
[Booking].[regnumber];

Hopefully i am posting this in the correct section.

[quoted text clipped - 19 lines]

Kevin

 

[KARL DEWEY]

just get a #name error!
A name error normally means Access cannot figure out a field due to a typo.
Can you figure out what part of the query it is giving the error. Open in
design view and look at the fields.

[XX] is an alias for [Booking] in the subquery.

Try it with '[Booking] AS [XX]' ---
SELECT [vehicle].[regnumber], DateAdd("w", 6, (SELECT Max([XX].[Date]) FROM
[Booking] AS [XX] WHERE [XX].[regnumber] = [vehicle].[regnumber] AND
[Booking].[inspection] = -1)) AS Due_Inspection
FROM [vehicle] LEFT JOIN [Booking] ON [vehicle].[regnumber] =
[Booking].[regnumber];

--
Build a little, test a little.

Karl

Thanks for your reply.

I have tried it and can't get it to work, just get a #name error!

Am i supposed to substitute something for the XX's after [booking] and WHERE ?


Regards
Kevin

Try this --
SELECT [vehicle].[regnumber], DateAdd("w", 6, (SELECT Max([Date]) FROM
[Booking] [XX] WHERE [XX].[regnumber] = [vehicle].[regnumber] AND
[Booking].[inspection] = -1)) AS Due_Inspection
FROM [vehicle] LEFT JOIN [Booking] ON [vehicle].[regnumber] =
[Booking].[regnumber];

Hopefully i am posting this in the correct section.

[quoted text clipped - 19 lines]

Kevin

--



.