If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Displaying Inspection Due Date
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200910/1 |
#2
|
|||
|
|||
Displaying Inspection Due Date
On Tue, 20 Oct 2009 15:25:57 GMT, "kevtrucker via AccessMonster.com"
u53597@uwe wrote: 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. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Displaying Inspection Due Date
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" wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200910/1 . |
#4
|
|||
|
|||
Displaying Inspection Due Date
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 John W. Vinson wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200910/1 |
#5
|
|||
|
|||
Displaying Inspection Due Date
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 KARL DEWEY wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200910/1 |
#6
|
|||
|
|||
Displaying Inspection Due Date
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. "kevtrucker via AccessMonster.com" wrote: 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 KARL DEWEY wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200910/1 . |
Thread Tools | |
Display Modes | |
|
|