formula

[Chey]

I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

 

[James A. Fortune]

I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)

 

[Chey]

This sounds like it would work great. Althought I have no clue what you are
talking about.

where do I start??????

I have my report with a query
The query fields are
ID---this equals a month idetifier
Month
Vendor Number
Provider Name
Check Box-Zero Enrollement, Zero Attendance

I would like to use the month code.
Oh where do I put this code?

 

[Chey]

SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

 

[James A. Fortune]

This sounds like it would work great. Althought I have no clue what you are
talking about.

where do I start??????

I have my report with a query
The query fields are
ID---this equals a month idetifier
Month
Vendor Number
Provider Name
Check Box-Zero Enrollement, Zero Attendance

I would like to use the month code.
Oh where do I put this code?
:

I would start by saving the SQL, modified to fit your field names, as a
query. Afterward, you can use the DLookup function to see if the query
returns True or False. Since the query only returns one record I don't
believe you need a criteria string. The Dlookup would be in code on a
form that decides whether or not to open the report with
DoCmd.OpenReport based on the results from the query. This query is
totally separate from the query used to populate the report.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

:

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)

tblGrant Completion-March also needs to be in square brackets since it
contains a space. Also, realize that the SQL I posted assumes that the
data is in a single table. E.g.,

MyTable
ID theMonth CheckField
1 1/1/05 CheckMark
2 2/1/05 CheckMark
3 3/1/05 NoCheckMark
4 4/1/05 CheckMark
5 5/1/05 CheckMark
6 6/1/05 CheckMark
7 7/1/05 NoCheckMark
8 8/1/05 NoCheckMark
9 9/1/05 NoCheckMark
10 10/1/05 NoCheckMark
11 11/1/05 NoCheckMark
12 12/1/05 NoCheckMark
13 1/1/06 NoCheckMark

Maybe I misunderstood what you meant by "each month on the year for 05
and 06." The query based on the data above will produce False. If the
third record gets checked (remember to move off the record after
checking the box) then the query will produce True.

James A. Fortune
(e-mail address removed)

 

[Chey]

I have tried somethig else maybe you could help me with.
I have a report with a sub report.
On the sub report I have a count. How do I make it to only show if the
count result is greater than or equal to 6?
This might be an easier way for me to understand. Plus I have the layout
they way I like it.

But after I get only the ones after 6, I want to be able to the diffrence
between months. Is this a okay way to approach this?

I guess if I can tackle one thing at a time, I can get this working. If
this doesn't look good then I will try the other way again.

SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

:

Chey wrote:

I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)

tblGrant Completion-March also needs to be in square brackets since it
contains a space. Also, realize that the SQL I posted assumes that the
data is in a single table. E.g.,

MyTable
ID theMonth CheckField
1 1/1/05 CheckMark
2 2/1/05 CheckMark
3 3/1/05 NoCheckMark
4 4/1/05 CheckMark
5 5/1/05 CheckMark
6 6/1/05 CheckMark
7 7/1/05 NoCheckMark
8 8/1/05 NoCheckMark
9 9/1/05 NoCheckMark
10 10/1/05 NoCheckMark
11 11/1/05 NoCheckMark
12 12/1/05 NoCheckMark
13 1/1/06 NoCheckMark

Maybe I misunderstood what you meant by "each month on the year for 05
and 06." The query based on the data above will produce False. If the
third record gets checked (remember to move off the record after
checking the box) then the query will produce True.

James A. Fortune
(e-mail address removed)

 

[Chey]

please be patient with me. So I am placing a command button on my form?
Okay done that.
I have a form with a subform. Do I put my command button on the form or
subform?

Then I do a code, open report.
Then do I place the code you gave me? Is there a way I can put the command
button on another form I have a form for just looking up reports.
I would like to put it there. I can still tell it to open the report.
Do I need to add anything else?

Now when I veiw this I want to see the providers name, and the months.
Is that possiable or have I just screwed things up?

SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

:

Chey wrote:

I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)

tblGrant Completion-March also needs to be in square brackets since it
contains a space. Also, realize that the SQL I posted assumes that the
data is in a single table. E.g.,

MyTable
ID theMonth CheckField
1 1/1/05 CheckMark
2 2/1/05 CheckMark
3 3/1/05 NoCheckMark
4 4/1/05 CheckMark
5 5/1/05 CheckMark
6 6/1/05 CheckMark
7 7/1/05 NoCheckMark
8 8/1/05 NoCheckMark
9 9/1/05 NoCheckMark
10 10/1/05 NoCheckMark
11 11/1/05 NoCheckMark
12 12/1/05 NoCheckMark
13 1/1/06 NoCheckMark

Maybe I misunderstood what you meant by "each month on the year for 05
and 06." The query based on the data above will produce False. If the
third record gets checked (remember to move off the record after
checking the box) then the query will produce True.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

I have tried somethig else maybe you could help me with.
I have a report with a sub report.
On the sub report I have a count. How do I make it to only show if the
count result is greater than or equal to 6?
This might be an easier way for me to understand. Plus I have the layout
they way I like it.

But after I get only the ones after 6, I want to be able to the diffrence
between months. Is this a okay way to approach this?

I guess if I can tackle one thing at a time, I can get this working. If
this doesn't look good then I will try the other way again.

:

SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

:



Chey wrote:


I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)

tblGrant Completion-March also needs to be in square brackets since it
contains a space. Also, realize that the SQL I posted assumes that the
data is in a single table. E.g.,

MyTable
ID theMonth CheckField
1 1/1/05 CheckMark
2 2/1/05 CheckMark
3 3/1/05 NoCheckMark
4 4/1/05 CheckMark
5 5/1/05 CheckMark
6 6/1/05 CheckMark
7 7/1/05 NoCheckMark
8 8/1/05 NoCheckMark
9 9/1/05 NoCheckMark
10 10/1/05 NoCheckMark
11 11/1/05 NoCheckMark
12 12/1/05 NoCheckMark
13 1/1/06 NoCheckMark

Maybe I misunderstood what you meant by "each month on the year for 05
and 06." The query based on the data above will produce False. If the
third record gets checked (remember to move off the record after
checking the box) then the query will produce True.

James A. Fortune
(e-mail address removed)

You can set the .Visible property of the control to False when the value
is 6 or greater. Perhaps try the Detail_Format event of the subreport.

To get more insight into how the SQL I posted works, try examining it
like this:

SELECT (SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE theMonth
IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
AS RunReport FROM MyTable;

with different combinations of checkmarks. The case where all the lines
are checked should be especially illuminating.

I don't understand what you want when you say "difference between
months." Perhaps providing an example would help.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

please be patient with me. So I am placing a command button on my form?
Okay done that.
I have a form with a subform. Do I put my command button on the form or
subform?

Then I do a code, open report.
Then do I place the code you gave me? Is there a way I can put the command
button on another form I have a form for just looking up reports.
I would like to put it there. I can still tell it to open the report.
Do I need to add anything else?

Now when I veiw this I want to see the providers name, and the months.
Is that possiable or have I just screwed things up?

:

SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

:



Chey wrote:


I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)

tblGrant Completion-March also needs to be in square brackets since it
contains a space. Also, realize that the SQL I posted assumes that the
data is in a single table. E.g.,

MyTable
ID theMonth CheckField
1 1/1/05 CheckMark
2 2/1/05 CheckMark
3 3/1/05 NoCheckMark
4 4/1/05 CheckMark
5 5/1/05 CheckMark
6 6/1/05 CheckMark
7 7/1/05 NoCheckMark
8 8/1/05 NoCheckMark
9 9/1/05 NoCheckMark
10 10/1/05 NoCheckMark
11 11/1/05 NoCheckMark
12 12/1/05 NoCheckMark
13 1/1/06 NoCheckMark

Maybe I misunderstood what you meant by "each month on the year for 05
and 06." The query based on the data above will produce False. If the
third record gets checked (remember to move off the record after
checking the box) then the query will produce True.

James A. Fortune
(e-mail address removed)

You can run the code from your search form or from a separate form if
you'd like. Put the DLookup and OpenReport code behind the button. It
should only take a few lines of code.

James A. Fortune
(e-mail address removed)

 

[Chey]

Okay so this is my code

SELECT (SELECT Sum(Nz([Zero Enrollment,Zero Attendance], 0)) FROM [tblGrant
Completion-March] AS A WHERE Month
IN (SELECT Month FROM [tblGrant Completion-March] AS A WHERE A.Month <=
[tblGrant Completion-March].Month AND A.Month >= DateAdd('m', -5, [tblGrant
Completion-March].Month)))
AS RunReport FROM [tblGrant Completion-March];

I have just a blank form, with a command button.
The command button opens the report.
Where do I paste this code.
Under the open report code?

Also I have the checkbox in my query as true, do I need to get rid of that?

Thank you for all your help. I know I will get this.

I have tried somethig else maybe you could help me with.
I have a report with a sub report.
On the sub report I have a count. How do I make it to only show if the
count result is greater than or equal to 6?
This might be an easier way for me to understand. Plus I have the layout
they way I like it.

But after I get only the ones after 6, I want to be able to the diffrence
between months. Is this a okay way to approach this?

I guess if I can tackle one thing at a time, I can get this working. If
this doesn't look good then I will try the other way again.

:

Chey wrote:

SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

:



Chey wrote:


I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)


tblGrant Completion-March also needs to be in square brackets since it
contains a space. Also, realize that the SQL I posted assumes that the
data is in a single table. E.g.,

MyTable
ID theMonth CheckField
1 1/1/05 CheckMark
2 2/1/05 CheckMark
3 3/1/05 NoCheckMark
4 4/1/05 CheckMark
5 5/1/05 CheckMark
6 6/1/05 CheckMark
7 7/1/05 NoCheckMark
8 8/1/05 NoCheckMark
9 9/1/05 NoCheckMark
10 10/1/05 NoCheckMark
11 11/1/05 NoCheckMark
12 12/1/05 NoCheckMark
13 1/1/06 NoCheckMark

Maybe I misunderstood what you meant by "each month on the year for 05
and 06." The query based on the data above will produce False. If the
third record gets checked (remember to move off the record after
checking the box) then the query will produce True.

James A. Fortune
(e-mail address removed)

You can set the .Visible property of the control to False when the value
is 6 or greater. Perhaps try the Detail_Format event of the subreport.

To get more insight into how the SQL I posted works, try examining it
like this:

SELECT (SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE theMonth
IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
AS RunReport FROM MyTable;

with different combinations of checkmarks. The case where all the lines
are checked should be especially illuminating.

I don't understand what you want when you say "difference between
months." Perhaps providing an example would help.

James A. Fortune
(e-mail address removed)

 

[Chey]

I pasted this code under on click along with the open report. Can someone
help me with this. I am about to pull my hair out.
Maybe I can try to understand this in a different way.
I have a query along with a report. I only want to view the true
checkboxes, for each provider for a 6 month period. Can you walk me through
this.

Okay so this is my code

SELECT (SELECT Sum(Nz([Zero Enrollment,Zero Attendance], 0)) FROM [tblGrant
Completion-March] AS A WHERE Month
IN (SELECT Month FROM [tblGrant Completion-March] AS A WHERE A.Month <=
[tblGrant Completion-March].Month AND A.Month >= DateAdd('m', -5, [tblGrant
Completion-March].Month)))
AS RunReport FROM [tblGrant Completion-March];

I have just a blank form, with a command button.
The command button opens the report.
Where do I paste this code.
Under the open report code?

Also I have the checkbox in my query as true, do I need to get rid of that?

Thank you for all your help. I know I will get this.

I have tried somethig else maybe you could help me with.
I have a report with a sub report.
On the sub report I have a count. How do I make it to only show if the
count result is greater than or equal to 6?
This might be an easier way for me to understand. Plus I have the layout
they way I like it.

But after I get only the ones after 6, I want to be able to the diffrence
between months. Is this a okay way to approach this?

I guess if I can tackle one thing at a time, I can get this working. If
this doesn't look good then I will try the other way again.

:


Chey wrote:

SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

:



Chey wrote:


I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)


tblGrant Completion-March also needs to be in square brackets since it
contains a space. Also, realize that the SQL I posted assumes that the
data is in a single table. E.g.,

MyTable
ID theMonth CheckField
1 1/1/05 CheckMark
2 2/1/05 CheckMark
3 3/1/05 NoCheckMark
4 4/1/05 CheckMark
5 5/1/05 CheckMark
6 6/1/05 CheckMark
7 7/1/05 NoCheckMark
8 8/1/05 NoCheckMark
9 9/1/05 NoCheckMark
10 10/1/05 NoCheckMark
11 11/1/05 NoCheckMark
12 12/1/05 NoCheckMark
13 1/1/06 NoCheckMark

Maybe I misunderstood what you meant by "each month on the year for 05
and 06." The query based on the data above will produce False. If the
third record gets checked (remember to move off the record after
checking the box) then the query will produce True.

James A. Fortune
(e-mail address removed)

You can set the .Visible property of the control to False when the value
is 6 or greater. Perhaps try the Detail_Format event of the subreport.

To get more insight into how the SQL I posted works, try examining it
like this:

SELECT (SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE theMonth
IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
AS RunReport FROM MyTable;

with different combinations of checkmarks. The case where all the lines
are checked should be especially illuminating.

I don't understand what you want when you say "difference between
months." Perhaps providing an example would help.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Okay so this is my code

SELECT (SELECT Sum(Nz([Zero Enrollment,Zero Attendance], 0)) FROM [tblGrant
Completion-March] AS A WHERE Month
IN (SELECT Month FROM [tblGrant Completion-March] AS A WHERE A.Month <=
[tblGrant Completion-March].Month AND A.Month >= DateAdd('m', -5, [tblGrant
Completion-March].Month)))
AS RunReport FROM [tblGrant Completion-March];

I have just a blank form, with a command button.
The command button opens the report.
Where do I paste this code.
Under the open report code?

Also I have the checkbox in my query as true, do I need to get rid of that?

Thank you for all your help. I know I will get this.


:

I have tried somethig else maybe you could help me with.
I have a report with a sub report.
On the sub report I have a count. How do I make it to only show if the
count result is greater than or equal to 6?
This might be an easier way for me to understand. Plus I have the layout
they way I like it.

But after I get only the ones after 6, I want to be able to the diffrence
between months. Is this a okay way to approach this?

I guess if I can tackle one thing at a time, I can get this working. If
this doesn't look good then I will try the other way again.

:



Chey wrote:


SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

:




Chey wrote:



I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)


tblGrant Completion-March also needs to be in square brackets since it
contains a space. Also, realize that the SQL I posted assumes that the
data is in a single table. E.g.,

MyTable
ID theMonth CheckField
1 1/1/05 CheckMark
2 2/1/05 CheckMark
3 3/1/05 NoCheckMark
4 4/1/05 CheckMark
5 5/1/05 CheckMark
6 6/1/05 CheckMark
7 7/1/05 NoCheckMark
8 8/1/05 NoCheckMark
9 9/1/05 NoCheckMark
10 10/1/05 NoCheckMark
11 11/1/05 NoCheckMark
12 12/1/05 NoCheckMark
13 1/1/06 NoCheckMark

Maybe I misunderstood what you meant by "each month on the year for 05
and 06." The query based on the data above will produce False. If the
third record gets checked (remember to move off the record after
checking the box) then the query will produce True.

James A. Fortune
(e-mail address removed)

You can set the .Visible property of the control to False when the value
is 6 or greater. Perhaps try the Detail_Format event of the subreport.

To get more insight into how the SQL I posted works, try examining it
like this:

SELECT (SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE theMonth
IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
AS RunReport FROM MyTable;

with different combinations of checkmarks. The case where all the lines
are checked should be especially illuminating.

I don't understand what you want when you say "difference between
months." Perhaps providing an example would help.

James A. Fortune
(e-mail address removed)

The first question I have is:

Does [tblGrant Completion-March] contain all of the month values like in
the example?

Next, Is it possible for you to change the field name from Month to
theMonth like in the example without causing problems with existing
queries or reports? Month is the name of a function in Access.

Do you know how to create code for the Click event of a command button?
If you just create the code without going through Properties and the
three dots, Access may not connect the code to the button properly.

The code will look something like (air code):

Private Sub cmdRunReport_Click()
If Nz(DLookup("RunReport", "SavedQuery")) = True Then
DoCmd.OpenReport "MyReport", acViewPreview
Else
MsgBox("There are not six months in a row.")
End If
End Sub

The checkbox in the query indicates that at least one record has its
checkbox and the previous five checked. That check indicates that a -1
will be returned from the query so DLookup will return True (-1).

James A. Fortune
(e-mail address removed)

 

[Chey]

Yes all the months are in [tblGrant Completion-March]
I changed the field to theMonth
Yes I know how to create codes

I placed the last code you gave me under on click.
where do I put the other code? Under this one?

Private Sub Zero_Enrollment__Zero_Attendece_Click()
If Nz(DLookup("WHAT DO I PUT HERE????", "CCAP")) = True Then
DoCmd.OpenReport "CCAP3", acViewPreview
Else
MsgBox ("There are not six months in a row.")
End If
End Sub

CCAP3 is my report name
CCAP is my query
Check box-Zero Enrollment, Zero Attendance

Thanks

Okay so this is my code

SELECT (SELECT Sum(Nz([Zero Enrollment,Zero Attendance], 0)) FROM [tblGrant
Completion-March] AS A WHERE Month
IN (SELECT Month FROM [tblGrant Completion-March] AS A WHERE A.Month <=
[tblGrant Completion-March].Month AND A.Month >= DateAdd('m', -5, [tblGrant
Completion-March].Month)))
AS RunReport FROM [tblGrant Completion-March];

I have just a blank form, with a command button.
The command button opens the report.
Where do I paste this code.
Under the open report code?

Also I have the checkbox in my query as true, do I need to get rid of that?

Thank you for all your help. I know I will get this.


:

Chey wrote:

I have tried somethig else maybe you could help me with.
I have a report with a sub report.
On the sub report I have a count. How do I make it to only show if the
count result is greater than or equal to 6?
This might be an easier way for me to understand. Plus I have the layout
they way I like it.

But after I get only the ones after 6, I want to be able to the diffrence
between months. Is this a okay way to approach this?

I guess if I can tackle one thing at a time, I can get this working. If
this doesn't look good then I will try the other way again.

:



Chey wrote:


SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

:




Chey wrote:



I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)


tblGrant Completion-March also needs to be in square brackets since it
contains a space. Also, realize that the SQL I posted assumes that the
data is in a single table. E.g.,

MyTable
ID theMonth CheckField
1 1/1/05 CheckMark
2 2/1/05 CheckMark
3 3/1/05 NoCheckMark
4 4/1/05 CheckMark
5 5/1/05 CheckMark
6 6/1/05 CheckMark
7 7/1/05 NoCheckMark
8 8/1/05 NoCheckMark
9 9/1/05 NoCheckMark
10 10/1/05 NoCheckMark
11 11/1/05 NoCheckMark
12 12/1/05 NoCheckMark
13 1/1/06 NoCheckMark

Maybe I misunderstood what you meant by "each month on the year for 05
and 06." The query based on the data above will produce False. If the
third record gets checked (remember to move off the record after
checking the box) then the query will produce True.

James A. Fortune
(e-mail address removed)


You can set the .Visible property of the control to False when the value
is 6 or greater. Perhaps try the Detail_Format event of the subreport.

To get more insight into how the SQL I posted works, try examining it
like this:

SELECT (SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE theMonth
IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
AS RunReport FROM MyTable;

with different combinations of checkmarks. The case where all the lines
are checked should be especially illuminating.

I don't understand what you want when you say "difference between
months." Perhaps providing an example would help.

James A. Fortune
(e-mail address removed)

The first question I have is:

Does [tblGrant Completion-March] contain all of the month values like in
the example?

Next, Is it possible for you to change the field name from Month to
theMonth like in the example without causing problems with existing
queries or reports? Month is the name of a function in Access.

Do you know how to create code for the Click event of a command button?
If you just create the code without going through Properties and the
three dots, Access may not connect the code to the button properly.

The code will look something like (air code):

Private Sub cmdRunReport_Click()
If Nz(DLookup("RunReport", "SavedQuery")) = True Then
DoCmd.OpenReport "MyReport", acViewPreview
Else
MsgBox("There are not six months in a row.")
End If
End Sub

The checkbox in the query indicates that at least one record has its
checkbox and the previous five checked. That check indicates that a -1
will be returned from the query so DLookup will return True (-1).

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

I pasted this code under on click along with the open report. Can someone
help me with this. I am about to pull my hair out.
Maybe I can try to understand this in a different way.
I have a query along with a report. I only want to view the true
checkboxes, for each provider for a 6 month period. Can you walk me through
this.

:

Okay so this is my code

SELECT (SELECT Sum(Nz([Zero Enrollment,Zero Attendance], 0)) FROM [tblGrant
Completion-March] AS A WHERE Month
IN (SELECT Month FROM [tblGrant Completion-March] AS A WHERE A.Month <=
[tblGrant Completion-March].Month AND A.Month >= DateAdd('m', -5, [tblGrant
Completion-March].Month)))
AS RunReport FROM [tblGrant Completion-March];

I have just a blank form, with a command button.
The command button opens the report.
Where do I paste this code.
Under the open report code?

Also I have the checkbox in my query as true, do I need to get rid of that?

Thank you for all your help. I know I will get this.


:

Chey wrote:

I have tried somethig else maybe you could help me with.
I have a report with a sub report.
On the sub report I have a count. How do I make it to only show if the
count result is greater than or equal to 6?
This might be an easier way for me to understand. Plus I have the layout
they way I like it.

But after I get only the ones after 6, I want to be able to the diffrence
between months. Is this a okay way to approach this?

I guess if I can tackle one thing at a time, I can get this working. If
this doesn't look good then I will try the other way again.

:



Chey wrote:


SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

:




Chey wrote:



I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)


tblGrant Completion-March also needs to be in square brackets since it
contains a space. Also, realize that the SQL I posted assumes that the
data is in a single table. E.g.,

MyTable
ID theMonth CheckField
1 1/1/05 CheckMark
2 2/1/05 CheckMark
3 3/1/05 NoCheckMark
4 4/1/05 CheckMark
5 5/1/05 CheckMark
6 6/1/05 CheckMark
7 7/1/05 NoCheckMark
8 8/1/05 NoCheckMark
9 9/1/05 NoCheckMark
10 10/1/05 NoCheckMark
11 11/1/05 NoCheckMark
12 12/1/05 NoCheckMark
13 1/1/06 NoCheckMark

Maybe I misunderstood what you meant by "each month on the year for 05
and 06." The query based on the data above will produce False. If the
third record gets checked (remember to move off the record after
checking the box) then the query will produce True.

James A. Fortune
(e-mail address removed)


You can set the .Visible property of the control to False when the value
is 6 or greater. Perhaps try the Detail_Format event of the subreport.

To get more insight into how the SQL I posted works, try examining it
like this:

SELECT (SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE theMonth
IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
AS RunReport FROM MyTable;

with different combinations of checkmarks. The case where all the lines
are checked should be especially illuminating.

I don't understand what you want when you say "difference between
months." Perhaps providing an example would help.

James A. Fortune
(e-mail address removed)

Chey,

Perhaps it would be good to start this process over since I still don't
understand everything you're trying to do. Maybe start a new thread
with a small abbreviated example with data and sample output that shows
the gist of what you are trying to accomplish. I've done the best I can
do with the information you've provided so far. I feel confident that
someone else or I can help you come up with a good plan once we
understand the problem completely.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Yes all the months are in [tblGrant Completion-March]
I changed the field to theMonth
Yes I know how to create codes

I placed the last code you gave me under on click.
where do I put the other code? Under this one?

Private Sub Zero_Enrollment__Zero_Attendece_Click()
If Nz(DLookup("WHAT DO I PUT HERE????", "CCAP")) = True Then
DoCmd.OpenReport "CCAP3", acViewPreview
Else
MsgBox ("There are not six months in a row.")
End If
End Sub

CCAP3 is my report name
CCAP is my query
Check box-Zero Enrollment, Zero Attendance

Thanks

:

Okay so this is my code

SELECT (SELECT Sum(Nz([Zero Enrollment,Zero Attendance], 0)) FROM [tblGrant
Completion-March] AS A WHERE Month
IN (SELECT Month FROM [tblGrant Completion-March] AS A WHERE A.Month <=
[tblGrant Completion-March].Month AND A.Month >= DateAdd('m', -5, [tblGrant
Completion-March].Month)))
AS RunReport FROM [tblGrant Completion-March];

I have just a blank form, with a command button.
The command button opens the report.
Where do I paste this code.
Under the open report code?

Also I have the checkbox in my query as true, do I need to get rid of that?

Thank you for all your help. I know I will get this.


:



Chey wrote:


I have tried somethig else maybe you could help me with.
I have a report with a sub report.
On the sub report I have a count. How do I make it to only show if the
count result is greater than or equal to 6?
This might be an easier way for me to understand. Plus I have the layout
they way I like it.

But after I get only the ones after 6, I want to be able to the diffrence
between months. Is this a okay way to approach this?

I guess if I can tackle one thing at a time, I can get this working. If
this doesn't look good then I will try the other way again.

:




Chey wrote:



SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

:





Chey wrote:




I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)


tblGrant Completion-March also needs to be in square brackets since it
contains a space. Also, realize that the SQL I posted assumes that the
data is in a single table. E.g.,

MyTable
ID theMonth CheckField
1 1/1/05 CheckMark
2 2/1/05 CheckMark
3 3/1/05 NoCheckMark
4 4/1/05 CheckMark
5 5/1/05 CheckMark
6 6/1/05 CheckMark
7 7/1/05 NoCheckMark
8 8/1/05 NoCheckMark
9 9/1/05 NoCheckMark
10 10/1/05 NoCheckMark
11 11/1/05 NoCheckMark
12 12/1/05 NoCheckMark
13 1/1/06 NoCheckMark

Maybe I misunderstood what you meant by "each month on the year for 05
and 06." The query based on the data above will produce False. If the
third record gets checked (remember to move off the record after
checking the box) then the query will produce True.

James A. Fortune
(e-mail address removed)


You can set the .Visible property of the control to False when the value
is 6 or greater. Perhaps try the Detail_Format event of the subreport.

To get more insight into how the SQL I posted works, try examining it
like this:

SELECT (SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE theMonth
IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
AS RunReport FROM MyTable;

with different combinations of checkmarks. The case where all the lines
are checked should be especially illuminating.

I don't understand what you want when you say "difference between
months." Perhaps providing an example would help.

James A. Fortune
(e-mail address removed)

The first question I have is:

Does [tblGrant Completion-March] contain all of the month values like in
the example?

Next, Is it possible for you to change the field name from Month to
theMonth like in the example without causing problems with existing
queries or reports? Month is the name of a function in Access.

Do you know how to create code for the Click event of a command button?
If you just create the code without going through Properties and the
three dots, Access may not connect the code to the button properly.

The code will look something like (air code):

Private Sub cmdRunReport_Click()
If Nz(DLookup("RunReport", "SavedQuery")) = True Then
DoCmd.OpenReport "MyReport", acViewPreview
Else
MsgBox("There are not six months in a row.")
End If
End Sub

The checkbox in the query indicates that at least one record has its
checkbox and the previous five checked. That check indicates that a -1
will be returned from the query so DLookup will return True (-1).

James A. Fortune
(e-mail address removed)

From the SQL I Posted:

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

the field RunReport (or whatever you've named it) is the one you are
trying to get. So,

If Nz(DLookup("RunReport", "CCAP")) = True Then

should work. I didn't mean for the other answer to sound the way it
did. Sorry.

James A. Fortune
(e-mail address removed)

 

[Chey]

Okay take a look
SELECT Sum((SELECT Sum(Nz([A.Zero Enrollement, Zero Attendance], 0)) FROM
[tblGrant Completion-March] AS A WHERE ID
IN (SELECT ID FROM [tblGrant Completrion-March] AS A WHERE A.ID <= [tblGrant
Completion-March].ID AND A.ID >=
[tblGrant Completion-March.ID - 5)) = - 6) <> 0 AS RunReport FROM [tblGrant
Completion-March];

If Nz(DLookup("Vendor Number", "CCAP")) = True Then

The first part is all red. Can you alter my code to work.
I still don't know about the run report part. I just put a field in there
that I would like to see on my report. or do I put the report name?
Thanks
Chey

Yes all the months are in [tblGrant Completion-March]
I changed the field to theMonth
Yes I know how to create codes

I placed the last code you gave me under on click.
where do I put the other code? Under this one?

Private Sub Zero_Enrollment__Zero_Attendece_Click()
If Nz(DLookup("WHAT DO I PUT HERE????", "CCAP")) = True Then
DoCmd.OpenReport "CCAP3", acViewPreview
Else
MsgBox ("There are not six months in a row.")
End If
End Sub

CCAP3 is my report name
CCAP is my query
Check box-Zero Enrollment, Zero Attendance

Thanks

:

Chey wrote:

Okay so this is my code

SELECT (SELECT Sum(Nz([Zero Enrollment,Zero Attendance], 0)) FROM [tblGrant
Completion-March] AS A WHERE Month
IN (SELECT Month FROM [tblGrant Completion-March] AS A WHERE A.Month <=
[tblGrant Completion-March].Month AND A.Month >= DateAdd('m', -5, [tblGrant
Completion-March].Month)))
AS RunReport FROM [tblGrant Completion-March];

I have just a blank form, with a command button.
The command button opens the report.
Where do I paste this code.
Under the open report code?

Also I have the checkbox in my query as true, do I need to get rid of that?

Thank you for all your help. I know I will get this.


:



Chey wrote:


I have tried somethig else maybe you could help me with.
I have a report with a sub report.
On the sub report I have a count. How do I make it to only show if the
count result is greater than or equal to 6?
This might be an easier way for me to understand. Plus I have the layout
they way I like it.

But after I get only the ones after 6, I want to be able to the diffrence
between months. Is this a okay way to approach this?

I guess if I can tackle one thing at a time, I can get this working. If
this doesn't look good then I will try the other way again.

:




Chey wrote:



SELECT Sum((SELECT Sum(Nz([Zero Enrollement, Zero Attendance], 0)) FROM
tblGrant Completion-March AS A WHERE
Month IN (SELECT Month FROM tblGrant Completion-March AS A WHERE A.Month <=
tblGrant Completion-March.Month AND A. Month >= DateAdd('m', -5, tblGrant
Completion-March. Month)))
= - 6) <> 0 AS RunReport FROM tblGrant Completion-March;

this is what I came up with. Am I right?
Now wher do I put this?
Oh one more thing Zero Enrollement, Zero Attendance is a Check box-I only
need the true boxes.

:





Chey wrote:




I have a formula question. I hope someone can help.
I have a check box.
and each month on the year for 05 and 06

if the check box is check for 6 continuous months, I need it to generate a
report.
So I guess I need to make a query with this critiera.

and example. if it is checked 5 times in a row, then not checked for the
next month, but then checked on the 7th month, it would not generate a
report. Only if it is a period of 6 months.

I hope someone can help
Thanks so much

Try this:

MyTable
ID Auto
theMonth Date/Time
CheckField Y/N

If ID is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

If theMonth is used to define the order,

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE
theMonth IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
= - 6) <> 0 AS RunReport FROM MyTable;

This checks to see if the sum of the RunReport condition values for each
line of MyTable that get a six-in-a-row condition is non-zero.

James A. Fortune
(e-mail address removed)


tblGrant Completion-March also needs to be in square brackets since it
contains a space. Also, realize that the SQL I posted assumes that the
data is in a single table. E.g.,

MyTable
ID theMonth CheckField
1 1/1/05 CheckMark
2 2/1/05 CheckMark
3 3/1/05 NoCheckMark
4 4/1/05 CheckMark
5 5/1/05 CheckMark
6 6/1/05 CheckMark
7 7/1/05 NoCheckMark
8 8/1/05 NoCheckMark
9 9/1/05 NoCheckMark
10 10/1/05 NoCheckMark
11 11/1/05 NoCheckMark
12 12/1/05 NoCheckMark
13 1/1/06 NoCheckMark

Maybe I misunderstood what you meant by "each month on the year for 05
and 06." The query based on the data above will produce False. If the
third record gets checked (remember to move off the record after
checking the box) then the query will produce True.

James A. Fortune
(e-mail address removed)


You can set the .Visible property of the control to False when the value
is 6 or greater. Perhaps try the Detail_Format event of the subreport.

To get more insight into how the SQL I posted works, try examining it
like this:

SELECT (SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE theMonth
IN (SELECT theMonth FROM MyTable AS A WHERE A.theMonth <=
MyTable.theMonth AND A.theMonth >= DateAdd('m', -5, MyTable.theMonth)))
AS RunReport FROM MyTable;

with different combinations of checkmarks. The case where all the lines
are checked should be especially illuminating.

I don't understand what you want when you say "difference between
months." Perhaps providing an example would help.

James A. Fortune
(e-mail address removed)


The first question I have is:

Does [tblGrant Completion-March] contain all of the month values like in
the example?

Next, Is it possible for you to change the field name from Month to
theMonth like in the example without causing problems with existing
queries or reports? Month is the name of a function in Access.

Do you know how to create code for the Click event of a command button?
If you just create the code without going through Properties and the
three dots, Access may not connect the code to the button properly.

The code will look something like (air code):

Private Sub cmdRunReport_Click()
If Nz(DLookup("RunReport", "SavedQuery")) = True Then
DoCmd.OpenReport "MyReport", acViewPreview
Else
MsgBox("There are not six months in a row.")
End If
End Sub

The checkbox in the query indicates that at least one record has its
checkbox and the previous five checked. That check indicates that a -1
will be returned from the query so DLookup will return True (-1).

James A. Fortune
(e-mail address removed)

From the SQL I Posted:

SELECT Sum((SELECT Sum(Nz(A.CheckField, 0)) FROM MyTable AS A WHERE ID
IN (SELECT ID FROM MyTable AS A WHERE A.ID <= MyTable.ID AND A.ID >=
MyTable.ID - 5)) = - 6) <> 0 AS RunReport FROM MyTable;

the field RunReport (or whatever you've named it) is the one you are
trying to get. So,

If Nz(DLookup("RunReport", "CCAP")) = True Then

should work. I didn't mean for the other answer to sound the way it
did. Sorry.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Okay take a look
SELECT Sum((SELECT Sum(Nz([A.Zero Enrollement, Zero Attendance], 0)) FROM
[tblGrant Completion-March] AS A WHERE ID
IN (SELECT ID FROM [tblGrant Completrion-March] AS A WHERE A.ID <= [tblGrant
Completion-March].ID AND A.ID >=
[tblGrant Completion-March.ID - 5)) = - 6) <> 0 AS RunReport FROM [tblGrant
Completion-March];

If Nz(DLookup("Vendor Number", "CCAP")) = True Then

The first part is all red. Can you alter my code to work.
I still don't know about the run report part. I just put a field in there
that I would like to see on my report. or do I put the report name?
Thanks
Chey

SQL for query saved under the name CCAP:

SELECT Sum((SELECT Sum(Nz(A.[Zero Enrollement, Zero Attendance], 0)) FROM
[tblGrant Completion-March] AS A WHERE ID IN (SELECT ID FROM [tblGrant
Completion-March] AS A WHERE A.ID <= [tblGrant Completion-March].ID AND
A.ID >= [tblGrant Completion-March].ID - 5)) = - 6) <> 0 AS
ShouldIRunTheReport FROM [tblGrant Completion-March];

Code behind form to see whether or not to run the report:

If Nz(DLookup("[ShouldIRunTheReport]", "CCAP")) = True Then
.....

RecordSource for Report:

SELECT [Vendor Number] FROM [tblGrant Completion-March];

James A. Fortune
(e-mail address removed)

 

[Chey]

So I have a command button that will run this report
I want it to show all of the providers that this code falls under. Is that
what you have me doing?
So now can you walk me through were in the query I put the long code?
Also the short code?
I understand the recordsource one.
Thanks for being patient.

Okay take a look
SELECT Sum((SELECT Sum(Nz([A.Zero Enrollement, Zero Attendance], 0)) FROM
[tblGrant Completion-March] AS A WHERE ID
IN (SELECT ID FROM [tblGrant Completrion-March] AS A WHERE A.ID <= [tblGrant
Completion-March].ID AND A.ID >=
[tblGrant Completion-March.ID - 5)) = - 6) <> 0 AS RunReport FROM [tblGrant
Completion-March];

If Nz(DLookup("Vendor Number", "CCAP")) = True Then

The first part is all red. Can you alter my code to work.
I still don't know about the run report part. I just put a field in there
that I would like to see on my report. or do I put the report name?
Thanks
Chey

SQL for query saved under the name CCAP:

SELECT Sum((SELECT Sum(Nz(A.[Zero Enrollement, Zero Attendance], 0)) FROM
[tblGrant Completion-March] AS A WHERE ID IN (SELECT ID FROM [tblGrant
Completion-March] AS A WHERE A.ID <= [tblGrant Completion-March].ID AND
A.ID >= [tblGrant Completion-March].ID - 5)) = - 6) <> 0 AS
ShouldIRunTheReport FROM [tblGrant Completion-March];

Code behind form to see whether or not to run the report:

If Nz(DLookup("[ShouldIRunTheReport]", "CCAP")) = True Then
.....

RecordSource for Report:

SELECT [Vendor Number] FROM [tblGrant Completion-March];

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

So I have a command button that will run this report

Yes. I wouldn't put the code to decide whether to run the report or not
in the report itself. IMO, it's usually better to open reports from forms.

I want it to show all of the providers that this code falls under. Is that
what you have me doing?

I don't understand what you mean here. That can be a separate task of
the form or not.

So now can you walk me through were in the query I put the long code?

In the design area for queries (a.k.a., Query By Example or QBE), don't
add any tables. Then switch to SQL view. Paste the "SELECT ..." there.

Also the short code?

Behind the click event of the command button used to run the report.

I understand the recordsource one.
Thanks for being patient.

De nada.

James A. Fortune
(e-mail address removed)