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 |
#11
|
|||
|
|||
reporting
No good it says that the expresion is too complicated
"Klatuu" wrote: 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()) "Natasha" wrote: 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 "Klatuu" wrote: DateAdd("m", [Freg], [Service Date]) Date() will return all coolers that have not been serviced within their frequency period. "Natasha" wrote: 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 "Klatuu" wrote: Date() "Natasha" wrote: 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 "Klatuu" wrote: Just make a verison of the query you already have, but filter for records where the next service date is the current date. "Natasha" wrote: 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 "Klatuu" wrote: 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? "Natasha" wrote: 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 |
#12
|
|||
|
|||
reporting
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()) "Natasha" wrote: No good it says that the expresion is too complicated "Klatuu" wrote: 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()) "Natasha" wrote: 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 "Klatuu" wrote: DateAdd("m", [Freg], [Service Date]) Date() will return all coolers that have not been serviced within their frequency period. "Natasha" wrote: 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 "Klatuu" wrote: Date() "Natasha" wrote: 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 "Klatuu" wrote: Just make a verison of the query you already have, but filter for records where the next service date is the current date. "Natasha" wrote: 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 "Klatuu" wrote: 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? "Natasha" wrote: 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 |
#13
|
|||
|
|||
reporting
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 "Klatuu" wrote: 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()) "Natasha" wrote: No good it says that the expresion is too complicated "Klatuu" wrote: 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()) "Natasha" wrote: 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 "Klatuu" wrote: DateAdd("m", [Freg], [Service Date]) Date() will return all coolers that have not been serviced within their frequency period. "Natasha" wrote: 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 "Klatuu" wrote: Date() "Natasha" wrote: 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 "Klatuu" wrote: Just make a verison of the query you already have, but filter for records where the next service date is the current date. "Natasha" wrote: 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 "Klatuu" wrote: 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? "Natasha" wrote: 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 |
#14
|
|||
|
|||
reporting
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. "Natasha" wrote: 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 "Klatuu" wrote: 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()) "Natasha" wrote: No good it says that the expresion is too complicated "Klatuu" wrote: 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()) "Natasha" wrote: 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 "Klatuu" wrote: DateAdd("m", [Freg], [Service Date]) Date() will return all coolers that have not been serviced within their frequency period. "Natasha" wrote: 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 "Klatuu" wrote: Date() "Natasha" wrote: 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 "Klatuu" wrote: Just make a verison of the query you already have, but filter for records where the next service date is the current date. "Natasha" wrote: 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 "Klatuu" wrote: 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? "Natasha" wrote: 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 |
#15
|
|||
|
|||
reporting
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. "Klatuu" wrote: 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. "Natasha" wrote: 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 "Klatuu" wrote: 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()) "Natasha" wrote: No good it says that the expresion is too complicated "Klatuu" wrote: 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()) "Natasha" wrote: 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 "Klatuu" wrote: DateAdd("m", [Freg], [Service Date]) Date() will return all coolers that have not been serviced within their frequency period. "Natasha" wrote: 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 "Klatuu" wrote: Date() "Natasha" wrote: 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 "Klatuu" wrote: Just make a verison of the query you already have, but filter for records where the next service date is the current date. "Natasha" wrote: 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 "Klatuu" wrote: 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? "Natasha" wrote: 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 |
#16
|
|||
|
|||
reporting
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. "Natasha" wrote: 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. "Klatuu" wrote: 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. "Natasha" wrote: 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 "Klatuu" wrote: 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()) "Natasha" wrote: No good it says that the expresion is too complicated "Klatuu" wrote: 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()) "Natasha" wrote: 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 "Klatuu" wrote: DateAdd("m", [Freg], [Service Date]) Date() will return all coolers that have not been serviced within their frequency period. "Natasha" wrote: 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 "Klatuu" wrote: Date() "Natasha" wrote: 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 "Klatuu" wrote: Just make a verison of the query you already have, but filter for records where the next service date is the current date. "Natasha" wrote: 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 "Klatuu" wrote: 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? "Natasha" wrote: 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 |
#17
|
|||
|
|||
reporting
Yes it is a numeric filed. Why did you put in the 6?
"Klatuu" wrote: 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. "Natasha" wrote: 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. "Klatuu" wrote: 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. "Natasha" wrote: 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 "Klatuu" wrote: 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()) "Natasha" wrote: No good it says that the expresion is too complicated "Klatuu" wrote: 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()) "Natasha" wrote: 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 "Klatuu" wrote: DateAdd("m", [Freg], [Service Date]) Date() will return all coolers that have not been serviced within their frequency period. "Natasha" wrote: 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 "Klatuu" wrote: Date() "Natasha" wrote: 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 "Klatuu" wrote: Just make a verison of the query you already have, but filter for records where the next service date is the current date. "Natasha" wrote: 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 "Klatuu" wrote: 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? "Natasha" wrote: 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 |
#18
|
|||
|
|||
reporting
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. "Natasha" wrote: Yes it is a numeric filed. Why did you put in the 6? "Klatuu" wrote: 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. "Natasha" wrote: 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. "Klatuu" wrote: 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. "Natasha" wrote: 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 "Klatuu" wrote: 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()) "Natasha" wrote: No good it says that the expresion is too complicated "Klatuu" wrote: 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()) "Natasha" wrote: 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 "Klatuu" wrote: DateAdd("m", [Freg], [Service Date]) Date() will return all coolers that have not been serviced within their frequency period. "Natasha" wrote: 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 "Klatuu" wrote: Date() "Natasha" wrote: 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 "Klatuu" wrote: Just make a verison of the query you already have, but filter for records where the next service date is the current date. "Natasha" wrote: 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 "Klatuu" wrote: 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? "Natasha" wrote: 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 |
#19
|
|||
|
|||
reporting
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()) "Klatuu" wrote: 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. "Natasha" wrote: Yes it is a numeric filed. Why did you put in the 6? "Klatuu" wrote: 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. "Natasha" wrote: 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. "Klatuu" wrote: 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. "Natasha" wrote: 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 "Klatuu" wrote: 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()) "Natasha" wrote: No good it says that the expresion is too complicated "Klatuu" wrote: 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()) "Natasha" wrote: 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 "Klatuu" wrote: DateAdd("m", [Freg], [Service Date]) Date() will return all coolers that have not been serviced within their frequency period. "Natasha" wrote: 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 "Klatuu" wrote: Date() "Natasha" wrote: 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 "Klatuu" wrote: Just make a verison of the query you already have, but filter for records where the next service date is the current date. "Natasha" wrote: 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 "Klatuu" wrote: 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? "Natasha" wrote: 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 |
|
Thread Tools | |
Display Modes | |
|
|