Using Variable in Query Criteria?

[RSIboy]

Hi

I have a form with two checkboxes; depending on whether one/both/none are ticked, I need the criteria of a query to be one of:

4
Is Not 4
*

I understand that that I can store the chosen option above in a hidden textbox (say, txtbox1) on the form, but then how do I pass the contents to my query? I can't simply put

txtbox1

in the criteria field...

I've read about using variables in the critera row of a QBE grid by using a Function ( http://support.microsoft.com/kb/114511 ), but this appears to be just to define the string, not the entire query criteria.

Any comments or suggestions gratefully received

Cheers

 

[MStef]

Question:
What is different between "Is not 4" and "*" (premise, Like "*")??

 

[RSIboy]

I have a table with a primary key of 'statusID' that the checkboxes relates to.

If checkbox 1 is selected (but not checkbox 2) then I need the criteria

statusID 4

If checkbox 2 is selected (but not checkbox 1) then I need the criteria

statusID <> 4

If both are selected, then I have no critera, and thought that would be

status ID *

 

[vbaInet]

You would need to handle everything in code and build your sql string in code too. Here's an idea to get you started:

Dim strSQL as string

strSQL = "SELECT * FROM table"

If Check1.value = True and Check2.Value = True then
    strSQL = strSQL & ";"
    .... Do what you want with strSQL
ElseIf Check1.Value = True Then
    strSQL = strSQL & " WHERE [fieldName] = 4;"    
    .... Do what you want with strSQL
ElseIf Check2.Value = True Then
    strSQL = strSQL & " WHERE [fieldName] <> 4;"  
    .... Do what you want with strSQL
Else 
    .... Any other criteria
End if

 

[Brianwarnock]

I think that this is resolved in a simple where clause

Where (checkbox1=-1 and checkbox2 =0 and StatusID=4) or (checkbox1=0 and checkbox2=-1 and StatusID<>4) or (checkbox1=-1 and checkbox2 =-1)

you will need to add forms!formname to the syntax for the checkboxes.

Brian

 

[MStef]

Look at "Demo4Not4AllA2000.mdb" (attachment, zip).
Open form and see.

 

[Brianwarnock]

I don't want to be a party pooper but that does not answer his question, and in my opinion 3 queries and VBA is a bit heavy for such a simple task.

Brian

 

[Brianwarnock]

Hi


I've read about using variables in the critera row of a QBE grid by using a Function ( http://support.microsoft.com/kb/114511 ), but this appears to be just to define the string, not the entire query criteria.

Any comments or suggestions gratefully received

Cheers

BTW you cannot pass a function to the criteria row you can only pass strings.

Brian

 

[vbaInet]

You can however, "get" the return value from a function in the Criteria row.

WHERE [FieldName] = myFunc()

The function must be have the brackets for the Jet Engine to recognise it as a function.

 

[MStef]

Hello RSIboy!
Look at "Demo4Not4AllNewA2000.mdb" (attachment, zip).
Open Form and try.

 

[RSIboy]

Wow! Thank you all for your prompt replies.

MStef - nice one! That is exactly the result I am trying to achieve. I'll see if I can transpose your solution to my database...

Brian, apologies if I havn't explained myself fully - it would be ideal if the query could be written in SQL as a WHERE clause, but it would need to say 'in english':

Where (IF checkbox1=-1 and checkbox2 =0 THEN StatusID=4, ELSE IF checkbox1=0 and checkbox2=-1 THEN StatusID<>4)

Can you have IF/THEN/ELSE in brackets in a Where clause?

Anyhow, thanks again for all your input

Cheers

 

[Brianwarnock]

Did you try my where clause? it says "in English"
Select IF (checkbox1=-1 and checkbox2 =0 and StatusID=4) ElseIF(checkbox1=0 and checkbox2=-1 and StatusID<>4) ElseIF (checkbox1=-1 and checkbox2 =-1)

Brian

 

[vbaInet]

Did you try my where clause? it says "in English"
Select IF (checkbox1=-1 and checkbox2 =0 and StatusID=4) ElseIF(checkbox1=0 and checkbox2=-1 and StatusID<>4) ElseIF (checkbox1=-1 and checkbox2 =-1)

Brian

I bet he didn't Brian

 

[Brianwarnock]

He couldn't have done, as I'm 4 years into retirement I don't always trust my solutions if others, like yourself, have proposed a different solution, so I test them, ie I try them, apparently a novel approach for some people.

Brian

 

[vbaInet]

Maybe he didn't understand the concept because he was still proposing the IF function. When you posted I thought that was plausible because a checkbox returns one of three values. If it were a textbox then it would have been a different case.

I think with your recent explanation he would get it.

 

[RSIboy]

Hehe... Guilty as charged!!

My sincere apologies Brian - I hadn't tried your solution. I didn't have the time to try all the suggestions when I first glanced at the replies, and didn't understand that the WHERE clause could be phrased like that (I'm still very much a copy&paste/learn-by-example Access user), so fired off another question... bad form!

Now that I've made a blank database, and tried your solution, it has worked like a charm - and I see by reviewing the 'Design view' rather than the SQL view, that I could have built the query in the familiar grid by using 3 criteria rows, and the three columns of StatusID, checkbox1, and checkbox2.

Once again thanks to all of you - I never ceased to be amazed (as I gradually try to improve the capability of my database), that when I get stuck there are people able and willing to promptly point me in the right direction.

Cheers!

 

[Brianwarnock]

Thanks for the response, glad you are sorted.

I actually find it easier to construct that type of Where condition in SQL but the downside is the risk of Typos.

Brian