if statement for a check box in report?

[Gustavson]

I'm using access 97. My report is printed from a query. This query contains a field named Hazardous which is a check box next to another field named chemical. I have about 25 records of chemicals and about 4 of them are Hazardous so the check box next to the 4 chemicals is checked. Okay, so on my report I have a cover page which states building information and then behind that I have the 25 chemical records listed. Anyways, on the cover page I have a check box which I want to be checked if any of the 25 chemicals are checked. In this case there are the 4 chemicals, so is there a way to write a code maybe that determines if any of the 25 records are checked and if so place a check mark on my report...

Another thing - My chemicals are listed in the detail section and the cover page is in the building header of my report. Could I some how link the check box of the building header to the check box on the detail section? For example, if me.detail.[hazardous]= true then etc....

Hope this is enough info...
Gustavson

 

[GingerR]

try this. change table1 to the name of your table.

in your cover sheet check box control source, put:

=IIf(DSum(&quot;[Hazardous]&quot;,&quot;[table1]&quot<0,Yes,No)

since YES = -1 and a NO = 0, if there are any YES's the sum of them will be <0.

g

 

[Gustavson]

Ginger - this worked great thanx for your help and sorry for the duplicate post. If anyone knows how to remove the other please do so or let me know how...
Thanx
Gustavson

Here's the code I used:
=IIf(DSum(&quot;[Hazardous Substance]&quot;,&quot;[EHS Reports]&quot<0,CBool(True),False)

 

[Gustavson]

ginger - okay, I came accross a little glitche. Here's what I did: I entered another building and entered some chemicals. That is fine, but none of them are hazardous. My reports prints the building cover page and the chemicals behind it but the hazardous box is checked as well. I believe this happens because it looks at the query and sees the check marks from the other building. Is there a way I can add a check box to the detail section of the report which contains the chemicals and link my building cover page check box to that instead, so that if any of the chemicals behind the building page is checked hazardous, check the cover page as well??

Bye the way, here's the run down on how my reports are printed:

Building
Chemicals in that building
Next Building
Chemicals in that building
And so on... until there are no more buildings

Hope this makes sense..
Thanx
Gustavson

 

[GingerR]

do this:

in the same place as before (check box in header) put

=DSum(&quot;[Hazardous]&quot;,&quot;Table1&quot;,&quot;[BuildingID] = &quot; & [BuildingID])

change Table1 to your table name as before

this adds the where clause &quot;where the buildingID in the table = the buildingID that I'm viewing in the report right now.

I'm assuming BuildingID is in the Header. change it to whatever you call it.
is your building ID a number? if so leave it like this.

if not make it say this - you have to put single quotes around the building name or whatever if it's text:

[BuildingID] = '&quot; & [BuildingID] & &quot;'&quot;

that's:

.... = single quote + double quote & ..... & double quote + single quote + double quote

let me know how it goes

g

 

[Gustavson]

Ginger - this is what I used and its not doing anything:

=IIf(DSum(&quot;[Hazardous Substance]&quot;,&quot;[EHS Reports]&quot;,&quot;[Building Name] = ' &quot; & [Building Name] & &quot; ' &quot<0,CBool(True),False)

I can say that it's not generating any errors. I messed around with the code to try to get it to work and this is the closest I got it to work. Basically, the check box remains blank which is not good. But, it doesn't turn grey so there's probably no errors. Bye the way, [EHS Reports] is the query used to generate the report. I tried putting a the building table [buildingTable] here and still no difference. I think my problem here is I don't understand what the code is doing in English.

Anyways, do you have any ideas....
I've run out of them...
Gustavson

 

[Gustavson]

ginger - just checking to see if you're still around...
Thanx
Gustavson

 

[Gustavson]

Ginger - okay, if figured out what I did wrong. I put spaces between these &quot;'&quot; so that I could see them. Anyways, I deleted the spaces and it works good now, Thanx... Bye the way, the time it takes to open my reports have increased a lot. Probably just have to deal with it...
Gustavson

 

[GingerR]

hi sorry i'm back. yes the spaces are the problem i saw that right away.

dlookup, dsum, etc (domain aggregate) functions take longer.
someone else probably has a slicker way.

what is cbool? convert to boolean? you dont have to. True = -1 = YES = checked

i will mess around to see if i can find something quicker.
send me zip of your db if you want so i can see how many records, etc and play with it maybe i can figure something out. it will help me too i am lazy in that i get something to work and leave it, sometimes i need to push myself to find faster better things.

datachick10@hotmail.com

g

 

[GingerR]

try this instead:

=IIf(Sum([Hazardous])<0,Yes,No)

leave first check box, put this on one next to it. browse thru and make sure they look the same. then get rid of first one and see if faster.

i got this by making a footer for the group (cover page header/footer in your case) and in that you can put summaries of fields in the detail section. i didnt think that would work in the header section, i thought the 'sum' had to be after the details. i only have 11records in my table and 3 'buildings' and it looks good to me but you'll have to verify.

g