A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

reporting



 
 
Thread Tools Display Modes
  #11  
Old November 13th, 2006, 08:08 PM posted to microsoft.public.access.forms
Natasha
external usenet poster
 
Posts: 83
Default 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  
Old November 13th, 2006, 08:35 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old November 14th, 2006, 11:15 AM posted to microsoft.public.access.forms
Natasha
external usenet poster
 
Posts: 83
Default 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  
Old November 14th, 2006, 03:36 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old November 15th, 2006, 11:41 AM posted to microsoft.public.access.forms
Natasha
external usenet poster
 
Posts: 83
Default 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  
Old November 15th, 2006, 03:33 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old November 15th, 2006, 04:09 PM posted to microsoft.public.access.forms
Natasha
external usenet poster
 
Posts: 83
Default 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  
Old November 15th, 2006, 04:21 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old November 15th, 2006, 05:55 PM posted to microsoft.public.access.forms
Natasha
external usenet poster
 
Posts: 83
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:15 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.