 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / November 2006reporting
I have a report that shows up any coolers that are due to be serviced on ie i can run the report/query for coolers due to be serviced in Nov and a list will come up. We have some companies that don't get their coolers serviced when they are supposed to. What i want to do is have a way of getting these coolers to come up on the same report so we don't forget about them. So basically i want is to have a report that will show all coolers due to be serviced between two dates and also to show any coolers that have a service that is overdue. Does anyone know how i would do the latter part of this. Thanks Natasha Need to know a little about your data. How do you know when service is due? How do you know if and/or when service was performed last? What tables are these data in? > I have a report that shows up any coolers that are due to be serviced on ie i > can run the report/query for coolers due to be serviced in Nov and a list [quoted text clipped - 7 lines] > Thanks > Natasha Hi I have a service history form that is filled in when a service is completed. In this form all customer and cooler info is put in including the date the service was done the next field is a frequency one when you put in either 3, 6, or 12 months. once this field is filled in the next which is next service date is automatically filled in 3, 6, or 12 months from the last service date. A report/Query can then be run using the next service date field to show what coolers need servicing say for next week. The thing is i need to keep a check on those coolers that don't get their scheduled service and this is because the customer decides they dont need it. So is ther a way of running the report to include those coolers that are overdue a service. Thanks Natasha > Need to know a little about your data. > How do you know when service is due? [quoted text clipped - 12 lines] > > Thanks > > Natasha Just make a verison of the query you already have, but filter for records where the next service date is < the current date. > Hi > [quoted text clipped - 28 lines] > > > Thanks > > > Natasha Hi What Criteria do i put in for < the current date. Can i use < today or something like that i cant remember what it is and i can find it anywhere. Thanks > Just make a verison of the query you already have, but filter for records > where the next service date is < the current date. [quoted text clipped - 31 lines] > > > > Thanks > > > > Natasha < Date() > Hi > [quoted text clipped - 37 lines] > > > > > Thanks > > > > > Natasha Hi Now i misunderstood the reporting of the out of date coolers. What needs to be reported is the coolers that are out of date, but only on their scheduled month. What i mean is if a cooler was scheduled to be serviced last may and it wasn't done and the cooler is supposed to be scheduled every 6 months, that company's cooler should come up again for a schedule in Nov. The way it runs now is when a cooler is serviced a form is filled in giving a service date the frequency is filled in and the nextservicedate is automatically filled in. Service Date Freq NextServiceDate 01/05/06 6 01/11/06 Then all the coolers due for service in Nov will come up on a report. But if a cooler wasn't serviced in may when it should have been a nextservicedate will not be generated for nov. I need these coolers to still come up on a report on a 3, 6 or12 monthly basis not every month. It needs to be run by the freq some how. Do you know how i can do this. Thanks Natasha > < Date() > [quoted text clipped - 39 lines] > > > > > > Thanks > > > > > > Natasha DateAdd("m", [Freg], [Service Date]) < Date() will return all coolers that have not been serviced within their frequency period. > Hi > [quoted text clipped - 61 lines] > > > > > > > Thanks > > > > > > > Natasha Hi That brings up all coolers that are overdue a service. What i want is say CompanyX Coolers need servicing every 12 months during Nov. of every year. I only want these overdue services to appear on Nov report. CompanyY Coolers need servicing every 6 months during Jan and July of every year. I only want these services to appear on the Jan and July reports. CompanyZ Coolers need servicing every 3 months during Jan, April, July and Oct of every year. I only want these services to appear on the Jan, April, July and Oct reports. Does this make sense, it's kind of hard to explain > DateAdd("m", [Freg], [Service Date]) < Date() > [quoted text clipped - 66 lines] > > > > > > > > Thanks > > > > > > > > Natasha I don't know that you have the data available to you to accomplish this. You could look at the last service date and assume that if the freq is 6 and the month of the service date is 5 it should be done again in Nov. Which would be: DateAdd("m", [Freg], [Service Date]) < Date() And Month(DateAdd("m", [Freg], [Service Date])) = Month(Date()) > Hi > That brings up all coolers that are overdue a service. What i want is say [quoted text clipped - 76 lines] > > > > > > > > > Thanks > > > > > > > > > Natasha No good it says that the expresion is too complicated > I don't know that you have the data available to you to accomplish this. You > could look at the last service date and assume that if the freq is 6 and the [quoted text clipped - 84 lines] > > > > > > > > > > Thanks > > > > > > > > > > Natasha One thing I notice is Freq is spelled with a G Freg. I don't know if that will help. The formula is not that complex. I don't know why it is a problem unless there is a syntax problem. I did a test on a table I have, but it does not have a frequecny field, so I plugged in 6 for testing purposes. This worked for me and returned the correct records: <Date() And Month(DateAdd("m",6,[begindate]))=Month(Date()) And Year(DateAdd("m",6,[begindate]))=Year(Date()) > No good it says that the expresion is too complicated > [quoted text clipped - 86 lines] > > > > > > > > > > > Thanks > > > > > > > > > > > Natasha Hi Ya freg field is ServiceFrequency but i already had that changed. i'm getting a data type mismatch in criteria expression. I have checked and all the field names are correct they are ServiceFrequency and ServiceDate > One thing I notice is Freq is spelled with a G Freg. I don't know if that > will help. The formula is not that complex. I don't know why it is a [quoted text clipped - 95 lines] > > > > > > > > > > > > Thanks > > > > > > > > > > > > Natasha Try doing to one piece at a time to see if you can find the data mismatch problem. Like start with <Date() and if that works keep adding pieces until you find the error. > Hi > [quoted text clipped - 101 lines] > > > > > > > > > > > > > Thanks > > > > > > > > > > > > > Natasha That brings up 120 records there should only be 6 coolers that have an overdue service. It seem to be bringing up all services to date for nov. I just want to show the 6. Do i have to do seperate queries for frequency 3 months and 12 months. I was putting in [serviceFrequency] instead of 6 to get all three frequencies into the expression, thats why it was giving me data mismatch. > Try doing to one piece at a time to see if you can find the data mismatch > problem. Like start with <Date() and if that works keep adding pieces until [quoted text clipped - 105 lines] > > > > > > > > > > > > > > Thanks > > > > > > > > > > > > > > Natasha Is service frequency not a numeric field? It should be. No, you don't need separate queries. That is what using the service frequency field is for - to determine when the cooler was due for service. > That brings up 120 records there should only be 6 coolers that have an > overdue service. It seem to be bringing up all services to date for nov. I [quoted text clipped - 112 lines] > > > > > > > > > > > > > > > Thanks > > > > > > > > > > > > > > > Natasha Yes it is a numeric filed. Why did you put in the 6? > Is service frequency not a numeric field? It should be. > No, you don't need separate queries. That is what using the service [quoted text clipped - 116 lines] > > > > > > > > > > > > > > > > Thanks > > > > > > > > > > > > > > > > Natasha As I stated previously, I did not have a table with a frequency in it, so I plugged the 6 in to test the logic. > Yes it is a numeric filed. Why did you put in the 6? > [quoted text clipped - 118 lines] > > > > > > > > > > > > > > > > > Thanks > > > > > > > > > > > > > > > > > Natasha Sorry So when i put in [ServiceFrequency] instead of the 6 i get a data mismatch i did it bit by bit <data() worked but the next one gave me a mismatch And Month(DateAdd("m",[ServiceFrequency],[ServiceDate]))=Month(Date()) > As I stated previously, I did not have a table with a frequency in it, so I > plugged the 6 in to test the logic. [quoted text clipped - 121 lines] > > > > > > > > > > > > > > > > > > Thanks > > > > > > > > > > > > > > > > > > Natasha |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.