Hi,

I have 3 checkboxes on a form and I want a query to bring back different results for each possibility of checks. The singles are easy, but if more than one gets checked I'm having problems can someone tell me how to handle this? The part thats tripping me up is in bold. Thanks!

SELECT tbl_downtime.Employee_Number, tbl_downtime.Machine_Stop, tbl_downtime.Machine_Start, tbl_downtime.DownTime, tbl_downtime.Shift
FROM tbl_downtime
WHERE (((tbl_downtime.Machine_Start) Between [Forms]![F_Reports2].[txtStop] And [Forms]![F_Reports2].[txtStart]) AND ((tbl_downtime.Shift)=IIf([Forms]![F_Reports2]![chk1]=0 And [Forms]![F_Reports2]![chk2]=0 And [Forms]![F_Reports2]![chk3]=-1,"3",IIf([Forms]![F_Reports2]![chk1]=0 And [Forms]![F_Reports2]![chk2]=-1 And [Forms]![F_Reports2]![chk3]=0,"2",IIf([Forms]![F_Reports2]![chk1]=-1 And [Forms]![F_Reports2]![chk2]=0 And [Forms]![F_Reports2]![chk3]=0,"1",IIf([Forms]![F_Reports2]![chk1]=-1 And [Forms]![F_Reports2]![chk2]=-1 And [Forms]![F_Reports2]![chk3]=0,"1" AND "2","0"))))));

Dude, bad post. Try word wrap. =)

SELECT tbl_downtime.Employee_Number, tbl_downtime.Machine_Stop, tbl_downtime.Machine_Start,
tbl_downtime.DownTime, tbl_downtime.Shift
FROM tbl_downtime
WHERE (((tbl_downtime.Machine_Start) Between [Forms]![F_Reports2].[txtStop] And
[Forms]![F_Reports2].[txtStart]) AND ((tbl_downtime.Shift)=IIf([Forms]![F_Reports2]![chk1]=0 And
[Forms]![F_Reports2]![chk2]=0 And [Forms]![F_Reports2]![chk3]=-1,"3",IIf([Forms]![F_Reports2]![chk1]=0 And
[Forms]![F_Reports2]![chk2]=-1 And [Forms]![F_Reports2]![chk3]=0,"2",IIf([Forms]![F_Reports2]![chk1]=-1 And
[Forms]![F_Reports2]![chk2]=0 And [Forms]![F_Reports2]![chk3]=0,"1",IIf([Forms]![F_Reports2]![chk1]=-1 And
[Forms]![F_Reports2]![chk2]=-1 And [Forms]![F_Reports2]![chk3]=0,"1" AND "2","0"))))));

It seems from looking at your combination of check boxes, that Check3 is never used to figure the state of the Shift. So this logic should do it for you:

Dim c1 As Boolean
Dim c2 As Boolean
Dim c3 As Boolean
Dim iShift As Integer
Dim sShift As String
Dim sSQL As String

c1 = Me.chk1
c2 = Me.chk2
c3 = Me.chk3

If c1 = False Then
    If c2 = False Then
        iShift = 3
    Else
        iShift = 2
    End If
Else
    If c2 = False Then
        iShift = 1
    Else
        sShift = " 1 And 2"
    End If
End If

If sShift <> "" Then
sSQL = "SELECT tbl_downtime.Employee_Number, tbl_downtime.Machine_Stop, tbl_downtime.Machine_Start, " & _
    "tbl_downtime.DownTime, tbl_downtime.Shift " & _
    "FROM tbl_downtime " & _
    "WHERE (((tbl_downtime.Machine_Start) Between [Forms]![F_Reports2].[txtStop] And " & _
    "[Forms]![F_Reports2].[txtStart]) AND [tbl_downtime.Shift] = 1 AND [tbl_downtime.Shift] = 2"
'I really think you mean 1 OR 2 here, not 1 AND 2

Else
sSQL = "SELECT tbl_downtime.Employee_Number, tbl_downtime.Machine_Stop, tbl_downtime.Machine_Start, " & _
    "tbl_downtime.DownTime, tbl_downtime.Shift " & _
    "FROM tbl_downtime " & _
    "WHERE (((tbl_downtime.Machine_Start) Between [Forms]![F_Reports2].[txtStop] And " & _
    "[Forms]![F_Reports2].[txtStart]) AND [tbl_downtime.Shift] = " & iShift
End If

Did that help?



mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks for the reply mmcdonal!
Sorry about the wide post, I tried wrapping it as code but apparently it didn't work very well.
Your right about the last line needing to be Or instead of And. I had tried it so many different ways I guess that's what got posted.

3rd shift can be ticked, so by my count that's 7 possibilities.
Do you think it would be better to put the code in a module and run from there instead of the query w/ the IIF? I think that's what your getting at.
I'll give this a shot like you laid it out and I'll take a look at that link also.

Thanks,
Dave

From what I had, even though the third check box was checked, it didn't make any difference in your logic.

I had it worked out but trashed it already. Only checks 1 and 2 determied the result, and check 3 was never a determining factor.

See what happens.

mmcdonal

Look it up at: http://wrox.books24x7.com

Okay I have the logic that works now and I made queries for different circumstances. The logic seems to work, but I'm having an issue w/ the checkboxes. When the form opens all 3 boxes are grey. The unclicked ones stay grey and I think this is messing something up when the code runs, because it is going into the wrong IF. If I check and clear all 3 boxes, then check only the one(s) I want, the code runs fine. What could be causing this? Thanks, Dave

'get selected shift(s) from checkboxes.
If [Forms]![F_Reports2].chk1.Value = -1 And [Forms]![F_Reports2].chk2.Value = -1 And [Forms]![F_Reports2].chk3.Value = 0 Then
    DoCmd.OpenQuery "Q_check12"
ElseIf [Forms]![F_Reports2].chk1.Value = -1 And [Forms]![F_Reports2].chk2.Value = 0 And [Forms]![F_Reports2].chk3.Value = -1 Then
    DoCmd.OpenQuery "Q_check13"
ElseIf [Forms]![F_Reports2].chk1.Value = 0 And [Forms]![F_Reports2].chk2.Value = -1 And [Forms]![F_Reports2].chk3.Value = -1 Then
    DoCmd.OpenQuery "Q_check23"
ElseIf [Forms]![F_Reports2].chk1.Value = -1 And [Forms]![F_Reports2].chk2.Value = -1 And [Forms]![F_Reports2].chk3.Value = -1 Then
    DoCmd.OpenQuery "Q_check123"
ElseIf ([Forms]![F_Reports2].chk1.Value = -1 Or [Forms]![F_Reports2].chk2.Value = -1 Or [Forms]![F_Reports2].chk3.Value = -1) Then
    DoCmd.OpenQuery "Q_Util_Down"
End If

I am not sure what is causing this. I think you may have Triple State set to Yes.??

You can always set the value on the On Current event of the form. Set each check box to 0 or False.

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com

Like MMcDonal says, check to make sure their triple state is turned OFF. Also make sure their default value is TRUE/YES or FALSE/NO instead of not specified.

ElseIfs are usually not very clear. Try something like this, based on your ElseIfs, but just using If/Else/End If:

Dim strSQL as String, fRunUtil_Down as Boolean

strSQL = ""
fRunUtil_Down = False

If Me.chk1 Then
    If Me.chk2 Then
        If Me.chk3 Then
            strSQL = "Q_check123"
        Else
            strSQL = "Q_check12"
        End If
    Else
        If Me.chk3 Then
            strSQL = "Q_check13"
        End If
    End If

    fRunUtil_Down = True
Else
    If Me.chk2 Then
        If Me.chk3 Then
            strSQL = "Q_check23"
        End If

        fRunUtil_Down = True
    Else
        If Me.chk3 Then
            fRunUtil_Down = True
        End If
    End If
End If

If Len(strSQL) > 0 Then
    DoCmd.OpenQuery strSQL
End If

If fRunUtil_Down Then
    DoCmd.OpenQuery "Q_Util_Down"
End If


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks for the replies guys.

I checked the Triple state and all 3 were set to No. I also checked the default value and it wasn't specified on any of the 3 boxes, so I set all 3 to false. I was still getting the same problem though.
I went into the load event of the form and set all 3 boxes to 0. Now when the form opened the boxes aren't shadowed in and everything seems to work fine.
Should I still change the ElseIfs like you showed Serrano? I haven't heard about them not being clear before. Why is it that they aren't usually clear?

Thanks,
Dave

In general, it's a good idea to stay away from ELSEIFs. The reason is that with IF/ELSE/ENDIF you know what's going on

IF x = 0 Then
    'blah if x = 0
ELSE
    'blah if x <> 0
End IF

When you start nesting them, you can still know what's going on

IF x = 0 Then
    'blah if x = 0
    IF y = 0 Then
       'blah if x = 0 & y = 0
    ELSE
       'blah if x = 0 & y <> 0
    End IF
ELSE
   'blah if x <> 0
    IF y = 0 Then
       'blah if x <> 0 & y = 0
    ELSE
       'blah if x <> 0 & y <> 0
    End IF
End IF

The problem with ELSEIFs is that people tend to not have any rhyme or reason on how they order them

If x = 0 And y = 0 Then
    'blah x = 0 & y = 0
ElseIf x <> and y = 0 Then
    'blah x <> 0 & y = 0
ElseIf ...

When you start getting a bunch of these in the random order that the person thought of them, it gets very hard to follow what conditions trigger the code you want; whereas in the other way, you have to follow true/false logic and keep things in order a certain way. Logically, it's better coding sense.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I agree there. Also, don't forget SELECT CASE.

The problem with the ElseIf, as Greg stated, is the order, and once a condition is met, the conditional stops all further processing.



mmcdonal

Look it up at: http://wrox.books24x7.com