IIf Statements

[tinabee]

This database is for a machine shop. I have a table called Work Orders and a table called Work Order Details. Each WO Detail has a segment (there are 6 segments in numerical order to choose from) and a Work Done checkbox. I have a report called ToDoToday grouped by segment. I would like the report to show all the WODetails that the WorkDone is not checked but not the last segment (6). I would like this segment to show only if the other segments are done.

 

[vbaInet]

Do you have a key or field that indicates that which one is the last segment? Perhaps an AutoNumber or an auto date field? If you do you can simply exclude the Max record per segment in a query.

 

[tinabee]

The segment table has a autonumber field there are 6 of them. Each work order detail has a segment that is a combo box populated from the segment table. Every detail has segment 1 (dismantle) and 6 (assemble) and at least 1 sometime many other segments. The ToDo report shows the Work Order # and the WODetails description and it is grouped by segment. I want it show the WODetails for each segment but I only want the Dismantle (segment 1) and any other segment that is not checked WorkDone and not the Assemble (segment 6) which would show up only if the other WODetails have been done.
View attachment To Do Today.pdf

 

[tinabee]

This is a better sample of the report

 

[vbaInet]

So there are only a maximum of 6 segments right?

Have you managed to get only those records where WorkDone is Yes to show? You should have been able to do this in a query.

 

[tinabee]

Yes there are only 6 segments and I can get them to show when Work Done is no with a query. But it also shows the Assemble segment and I don't want this segment to show unless all the other segments are checked Work Done. The report is a main report (no record source) with a subreport (record source is a query using the WODetails table and WorkDone is no) and it is grouped/sorted by segment.

 

[vbaInet]

That's fine, we can do it in a query or in a report. I will do it in a report.

1. Drop a textbox in the Segment's group Header section.
2. Enter =1 in the Control Source of the textbox. Call this textbox txtCount.
3. Set the textbox's Running Sum property to Over All.
4. In the On Print event of the Segment group, select Code Builder and enter this code:

If Me.[COLOR=Blue]Segment [/COLOR]= "Assemble" Then
    Me.[COLOR=Blue]SegmentGroupHeader[/COLOR].Visible = (txtCount = 6)
Else
    Me.[COLOR=Blue]SegmentGroupHeader[/COLOR].Visible = True
End If

So Segment is the name of the field that displays segments like Assembly, Dismantle, etc... and SegmentGroupHeader is the name of the Segment Group Header. Amend those names accordingly.

 

[tinabee]

I am getting an error "compile error: Method or data member not found" and txtCount is highlighted

 

[vbaInet]

If Me.[COLOR=Blue]Segment [/COLOR]= "Assemble" Then
    Me.[COLOR=Blue]SegmentGroupHeader[/COLOR].Visible = ([COLOR=Red]Me.[/COLOR]txtCount = 6)
Else
    Me.[COLOR=Blue]SegmentGroupHeader[/COLOR].Visible = True
End If

So Segment is the name of the field that displays segments like Assembly, Dismantle, etc... and SegmentGroupHeader is the name of the Segment Group Header. Amend those names accordingly.

Remember what I told you there? Each section has a name. Make sure you use the right section name. I just added that bit in red too but that's not the problem.

 

[tinabee]

Adding the "Me" solved the error problem but the report is not what I need. On the report I have WO#28809 and it is listed under Machine, Chrome, Grinding and Assemble as none of those WODetails have been checked WorkDone. I want the WODetails for Machine, Chrome and Grinding to show but not Assemble. I only want Assemble to show when the other (3) WODetails have been checked WorkDone and will no longer show on the report.

 

[vbaInet]

So let's say there's a WO# 12345 in Dismantle alone and the WorkDone hasn't been checked. Should Assemble be visible or not?

i.e. are you saying that if ANY of the work orders in ANY of the segments have their Workdone unchecked, it should hide Assemble?

 

[tinabee]

Every Work Order will have a Work Details record for "Dismantle" and one for "Assemble" and will have 1 or more records for other segments. So if Dismantle is not checked Work Done or any of the other segments are not checked Work Done then it will not appear under the Assemble section. The only Work Orders listed under Assemble should be the ones where all the other Work Details (with other segments) for the Work Order have been checked Work Done.

 

[tinabee]

Assemble can be visible anytime - it's just only the Work Orders that meet the criteria should be listed there.

 

[tinabee]

Right now if there is not WODetail for a segment the heading does not show either so if there were no details for assemble it would not show.

 

[vbaInet]

There are missing details. Upload a sample database let me see what you're working with.

 

[tinabee]

I think I figured out something that will work. I added a field to the segment table called Segment# (so it would have 3 fields SegmentID, SegmentName & Segment#). I gave the segments 1-5 the number 1 and segment 6 the number 2. I then created a query (WIPQuery1) using the Work Order & WODetails tables and used "like 1" for the SegmentID criteria and "like no" for the Work Done criteria. I than created another query (WIPQuery2) the same only change the SegmentID criteria to "like 2". I then created a Find Unmatched Query using these 2 queries to find the records in query 2 with unmatched records in query 1. Then for the report - I created a main report (unbound) with 2 subreports. Subreport1 I used the WIPQuery1 and Subreport 2 I used the Unmatched Query. It seems like it's working. I may not have explained my issue very well as it is somewhat difficult to do. Thank you for trying to help me.