formula

[Chey]

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];
This is what I pasted
It is telling me that I am missing ],)or item. Do you know what I could be
missing?
I belive I have all my file names correct.
Could it mean it can not find a file name of sort.
Oh ID is in another table. Do I have to change that?
The table is tblmonth.
Oh I hope this doesn't screw anything up.
Thanks

 

[Chey]

Private Sub Zero_Enrollment__Zero_Attendece_Click()
If Nz(DLookup("[ShouldIRunTheReport]", "CCAP3")) = True Then
End Sub
I put this under the on click on my command button.
But I still don't understand the [ShouldIRunTheReport]
What do I put there?
CCAP3 is my report name should that be the query name
Oh one more. Do I still need open report code?
Thanks agian.

 

[Chey]

okay now we have to slow down.
I need to takle this one

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

I have done something wrong
tblGrant Completion-March
Holds fields------Zero Enrollement, Zero Attendance
tblmonth
holds field-ID
tblGrant however has all the months spelled out ex January 05 all the way
to December 07 for each provider.
When I run the code I get a blank page an error mesg. comes up can not find
tblmonth.ID
I do have a table called tblmonth and a field called ID.
Please work with me through this I have come to far to quite.
Lets work with this one first.

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]

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];
This is what I pasted
It is telling me that I am missing ],)or item. Do you know what I could be
missing?
I belive I have all my file names correct.
Could it mean it can not find a file name of sort.
Oh ID is in another table. Do I have to change that?
The table is tblmonth.
Oh I hope this doesn't screw anything up.
Thanks

[A.Zero Enrollement, Zero Attendance] needs to be A.[Zero Enrollement,
Zero Attendance]

Completrion should be Completion

[tblGrant Completion-March.ID - 5)) = - 6) needs a ']' after March

[tblGrant Completion-March]
The number of spaces after tblGrant has to be exactly the same as your
table name otherwise Access will not recognize it.

If you don't have an ID field in the table that orders the month dates
then use the other version I posted that doesn't use the ID field.

Also, don't include the characters at the beginning of the line that
indicate a quote from a previous post.

Keep at it. I can almost see what your code is supposed to look like.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Private Sub Zero_Enrollment__Zero_Attendece_Click()
If Nz(DLookup("[ShouldIRunTheReport]", "CCAP3")) = True Then
End Sub

I think I posted about five lines in the Click event.

I put this under the on click on my command button.
But I still don't understand the [ShouldIRunTheReport]
What do I put there?

Whatever field is used by CCAP3 is the one you want.

CCAP3 is my report name should that be the query name

It should be the query name.

Oh one more. Do I still need open report code?
Thanks agian.

Yes.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

okay now we have to slow down.

I need to takle this one

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

You never fixed the syntax here. See the other answer.

I have done something wrong
tblGrant Completion-March
Holds fields------Zero Enrollement, Zero Attendance
tblmonth
holds field-ID
tblGrant however has all the months spelled out ex January 05 all the way
to December 07 for each provider.
When I run the code I get a blank page an error mesg. comes up can not find
tblmonth.ID
I do have a table called tblmonth and a field called ID.
Please work with me through this I have come to far to quite.
Lets work with this one first.

When you replace tblMonth in what I posted you need to change the name
everywhere to [tblGrant Completion-March]. Actually, you don't need to
use [tblGrant Completion-March] at all if you already have tblMonths
with the fields I've shown. Don't the fields that look like "January
05" have a year attached somehow? I repeat, the SQL for determining
whether to run the report or not is totally separate from the SQL used
to get the data on the report. Also, there's not a [tblGrant
Completion-April] anywhere, right? I'll need to see an example soon to
see what you're up against otherwise I'll just be making guesses.

James A. Fortune
(e-mail address removed)

 

[Chey]

Hello
I had to use the tblGrant Completion-March to try to get everything to flow.
You had mentioned about making two queries. I had done that. Now on my
report I did the SQL like you told me. and on the on click I did what you
told me. so now when I click on my command button an error mesg pops up
saying it can not find A.Zero Enrollement, Zero Attendance. I may have
mispelled that though. Now when my report opens how do I put the field on my
report that I want to view. When I made the SQL it made me name it, now it
shows up in the record source in my report. Do I need to change that back to
my old query name?
Thanks again for all your help. I think we may be almost there.

okay now we have to slow down.
I need to takle this one

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

You never fixed the syntax here. See the other answer.

I have done something wrong
tblGrant Completion-March
Holds fields------Zero Enrollement, Zero Attendance
tblmonth
holds field-ID
tblGrant however has all the months spelled out ex January 05 all the way
to December 07 for each provider.
When I run the code I get a blank page an error mesg. comes up can not find
tblmonth.ID
I do have a table called tblmonth and a field called ID.
Please work with me through this I have come to far to quite.
Lets work with this one first.

When you replace tblMonth in what I posted you need to change the name
everywhere to [tblGrant Completion-March]. Actually, you don't need to
use [tblGrant Completion-March] at all if you already have tblMonths
with the fields I've shown. Don't the fields that look like "January
05" have a year attached somehow? I repeat, the SQL for determining
whether to run the report or not is totally separate from the SQL used
to get the data on the report. Also, there's not a [tblGrant
Completion-April] anywhere, right? I'll need to see an example soon to
see what you're up against otherwise I'll just be making guesses.

James A. Fortune
(e-mail address removed)

 

[Chey]

Okay disregard last post.
I got it to work, however, not what I was looking for, close though. Just
need help polishing it up.
It takes for ever to process.
and it came back saying that there were not 6 of them. I know why though
you said that I had to have them all in one table. I don't. Can I alter
the code for this. They do have a relationship. If not can you show me an
easy way of transfering it into my other table. I have over 15,0000 records.
Can I do an if statement?

okay now we have to slow down.
I need to takle this one

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

You never fixed the syntax here. See the other answer.

I have done something wrong
tblGrant Completion-March
Holds fields------Zero Enrollement, Zero Attendance
tblmonth
holds field-ID
tblGrant however has all the months spelled out ex January 05 all the way
to December 07 for each provider.
When I run the code I get a blank page an error mesg. comes up can not find
tblmonth.ID
I do have a table called tblmonth and a field called ID.
Please work with me through this I have come to far to quite.
Lets work with this one first.

When you replace tblMonth in what I posted you need to change the name
everywhere to [tblGrant Completion-March]. Actually, you don't need to
use [tblGrant Completion-March] at all if you already have tblMonths
with the fields I've shown. Don't the fields that look like "January
05" have a year attached somehow? I repeat, the SQL for determining
whether to run the report or not is totally separate from the SQL used
to get the data on the report. Also, there's not a [tblGrant
Completion-April] anywhere, right? I'll need to see an example soon to
see what you're up against otherwise I'll just be making guesses.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Okay disregard last post.
I got it to work, however, not what I was looking for, close though. Just
need help polishing it up.
It takes for ever to process.
and it came back saying that there were not 6 of them. I know why though
you said that I had to have them all in one table. I don't. Can I alter
the code for this. They do have a relationship. If not can you show me an
easy way of transfering it into my other table. I have over 15,0000 records.
Can I do an if statement?

I'd say we're on to a different problem now. With 15,000 records you're
likely to need to use VBA. I can help you do that. Start a new thread.
Show a few sample records from each table. Explain what you are
trying to do. Explain the six checkboxes in a row criterion again.
Once I understand the problem I usually do a decent job of coming up
with a plan. I still don't understand. Perhaps some code can fill in
the values of the checkboxes for the 24 records in tblMonths. Help us
understand. Be verbose if necessary.

James A. Fortune
(e-mail address removed)