Check Box Help

[Demona]

Hi Guys and Gals,

I have a quick little problem ... I have a form that calls out to a dynamic query which works fine. My problem is with the code for the check boxes. This is the code I am using.

    If Me.[GVL A] = -1 Then
        where = where & " AND [Shift ID] = " & "'A'"
    End If
        
    If Me.[GVL A] = 0 Then
        where = where & " AND [Shift ID] = " & "'A'"
    End If

Whats happening is that there are three check boxes and I can't select box 1 and 2 to do a query (my results from the search return a blank form) or box 1 and 3 or 2 and 3 or what ever. And to top it all off, if I do select one it works fine and again if I check a different box the results return blank. Do I need to do a .requery on this form and how do I do multiple selection for a search.

Thanks

Love and Kisses

Demona

 

[Demona]

And Or Statement

I can do the query with a Or but how do I place that inside the code?

 

[dcx693]

You should place a
Debug.Print where
just before the SQL statement gets executed so you get to see exactly what the SQL string produced is. Or use the MsgBox statement to print the message to the screen. Post the results here so we can help you with it.

 

[dcx693]

I can do the query with a Or but how do I place that inside the code?

Where you see AND in your code, replace it with OR.

 

[Demona]

Thanks for repling

I have attached a skimpy version of the database. The Supplied zip file only has check boxes in the Report / Query Form under GVL. All of these chck boxes will be selectable by the user eventually, but first I was trying the check box scenerio with just the Shift ID area under GVL. I tried the OR statement with worse results. Is there a AND/OR statement?

Anyways Thanks


Love and Kisses

Demona

 

[dcx693]

There are several problems here. The most important of which has nothing to do with programming syntax. Your syntax looks fine, but the logic is wrong.

I can guess at what you're trying to achieve with these statements, but if you read through them, it doesn't seem logical:

If [GVL Shift A] = -1 Then
where = where & " AND [Shift ID] = " & "'A'"
End If

If [GVL Shift A] = 0 Then
where = where & " AND [Shift ID] = " & "'A'"
End If

If [GVL Shift B] = -1 Then
where = where & " AND [Shift ID] = " & "'B'"
End If

If [GVL Shift B] = 0 Then
where = where & " AND [Shift ID] = " & "'B'"
End If

In the first statement, you're saying "do this if the checkbox is checked". Then in the second statement, you're saying "do the same thing if the checkbox is not checked". Your checkboxes are not "triple-state" checkboxes, so they have to be either true or false (not null).

The reason you don't get any results when you select more than one checkbox is because your logic produces statements like this:
"[Shift ID]='A' AND [Shift ID]='B' AND [Shift ID]='C'"
Since Shift ID can only be one of those values, no records can meet those criteria.

You're on the right track when you say you want to change the AND to OR. All you need to do is to change them in the code.

 

[Demona]

Code problem

Hi Guys,

First of all, I would like to apologize to everyone about cross posting / double posting. I figured that I may have posted in the wrong forum. Sorry.

Second, dcx693 has been a great help, but I found that the only way to really (sort of) make it work was to start with a re-write of the code. I found that putting a double space after the AND / OR statements made a slight change in my code.

Now for my new problem. Because I re-wrote the code a above by adding strings to store the selections, my code for building the query is adding extra AND to the build of the query and I was hoping that someone could just look at the code and point out what was wrong. I keep getting a 3075 error.

Thanks

 

[MrTibbs]

where is the confusion?

Is 'where' a reserved word in SQL?

Try something like
cwhere = ""

If [GVL Shift A] = -1 Then
cwhere = "[Shift ID] = " & "'A'"
End If

If [GVL Shift B] = -1 Then
cwhere = cwhere & " OR [Shift ID] = " & "'B'"
End If

If [GVL Shift C] = -1 Then
cwhere = cwhere & " OR [Shift ID] = " & "'C'"
End If

This will need tidying so the 'OR' is only added to an existing string after the first element is assigned. Also check -1 is 'true'

Once your cwhere is defined as [shiftid] = "a" or [shiftID] = "B" or whtever you dump the whole condition into your where criteria as one lump so it does not try to AND the separate parts.