Search form with multiple tick boxes wont ignore unticked boxes

[sweeneye]

Hi,

I'm basing a query on the variables used in a form. The database contains lots of problems, say with a computer and a tick box for the apropriate component like monitor, keyboard, mouse etc. and each has several eg problem 1 has a monitor and mouse problem

My form then has what to look for tick boxes (tri-state) for each of these. I want my query to include all monitor problems when that is ticked and all monitor and mouse problems when both ticked, no monitor problems when unticked and ignore a parameter when the tick box is null (greyed out)

I have come up with:

WHERE ((((Table1.[Monitor])=Forms![Query Form]![Monitor tick]) Or Is Null) And (((Table1.[Keyboard])=Forms![Query Form]!Keyboard tick) Or Null) And (((Table1.Mouse)=Forms![Query Form]!Mouse tick) Or Null)

but I cant get it to work, it either gives my all the entries in the database or sometimes a parameter query box with "Forms![Query Form]![Monitor tick]"

any help or advice would be great, thanks

[Stewart Ross]

Hi. What data type are you using for the fields bound to your tick boxes? If they are Boolean (Yes/No) types an Access quirk is that there is no null state for Boolean values, so any test for Is Null on a boolean will fail. Booleans will evaluate as False when not set. You need to rethink the logic of what you wish to achieve...

On the other issue, Access sometimes has difficulties in interpreting direct references to form fields in queries, treating the form field as a parameter. If this becomes a problem for you it is possible to resolve it by replacing the form field references with a custom function which will return the field value from the form for you. the Jet database engine has no problems with function calls, but can fail in mysterious ways when faced with form field references.

If you need to use it, the function below should be placed in any public code module or a new module if you do not have any existing modules.

Public Function FormFieldValue(FormName As String, FieldName As String)
    FormFieldValue = Forms(FormName).Controls(FieldName)
End Function

In place of the direct form field references in your query criteria you then use calls to the function as follows:

Formfieldvalue("Query Form", "Monitor Tick")

and so on.

Good luck with resolving the problems.

-Stewart

[sweeneye]

Thanks for the advice on using form fields as query values, that was something that I refused to belive wouldn't work but I guess sometimes even in programmes like Access there can be bugs like that!

Anyway, I didn't give any thought to the data type of the fields. I just inserted check boxes, named them appropriately and in the properties of the check box I changed the vale 'triple state' to yes, assuming this gave me a variable that had three staes, Yes, No and Is Null. Was I wrong to also assume this?

My workaround which will result in a bit of a clunky for is to create a drop down for each problem with the values Yes, No and Is Null (displayed as Ignore) and use this parameter for my query but I am not sure this will work either. If the SQL search through the database uses these values will it do what I want as I had written it?

[Stewart Ross]

Hi. What matters most is what the field types of the fields in Table1 are. Your SQL WHERE in post 1 clearly shows that you are testing your form tickboxes against fields such as Table1.[Mouse] and Table1.[Monitor]. If these are boolean then the tri-state form boxes will not help you because the fields in the table can't represent the null state. Integer fields can, however, as null is an allowed state for a Long or Integer field. You would need to make sure that there is no value set in the Default Value property, however.

I would suggest that you revise the field types to Integer for the tick fields and do some tests with your form at table and query level to see what happens when you add test records to the table without setting these fields. In particular, check for null values actually being recorded. I would try to avoid using drop-downs with things like Null on them as text, because it won't mean much for users.

-Stewart

[sweeneye]

OK, thanks, I'm smacking my head in disgust!!! I wrote the SQL ages ago and came back to it recently and realised I had been asking it to do something stupid by basically saying where field 'x' (which is a yes or no) is equal to null

what I thought i was saying was include this field if the tick box is equal to null, i'll have another look at it

[sweeneye]

So with the new Public Function:

WHERE (IF Formfieldvalue("Query Form", "Monitor tick") <> IS Null THEN (Table1.[Monitor])=Formfieldvalue("Query Form", "Monitor tick"));
 

Not being a big SQL user am I right in using logic here inside a WHERE statement or am I again going down the wrong track!

[Stewart Ross]

Ahh, not quite there with the SQL yet. If what you want to do is to check whether the form field matches the tick box if it is not null, and include it anyway if the tick box is null, there is a simpler way using the Nz function to return True (-1) if the form field is null:

WHERE (Table1.Monitor = CBool(Nz(Formfieldvalue("Query Form", "Monitor tick"), -1))) AND (Table1.Mouse = ...

The CBool is a convert to boolean function which will ensure that the value is correctly interpreted as a true/false value (as Nz is likely to return a string).

-Stewart

[NeoPa]

My main rcecommendation would be to create the SQL on the fly in your form code. This is much more straightforward. However, if this is not possible or practicable for any reason, please read on.

For this you will need to change the FormFieldValue() procedure slightly to return a Variant value. Otherwise Null values will cause a crash. This also assumes that, whatever the type of the fields in your table, they only ever contain TRUE (all 1 bits = -1) or FALSE (all 0 bits = 0) values.

WHERE (((FormFieldValue("Query Form", "Monitor tick") IS NULL)
   OR (FormFieldValue("Query Form", "Monitor tick") = Table1.Monitor))
  AND ((FormFieldValue("Query Form", "Mouse tick") IS NULL)
   OR (FormFieldValue("Query Form", "Mouse tick") = Table1.Mouse))
  AND (...))

Clearly, this can end up as quite a convoluted set of SQL, but it should nevertheless work.

[NeoPa]

My experience of accessing form controls from SQL is probably not as extensive as many, so take what I say with caution. I haven't experienced any problems referencing controls myself. Here is a link on how it should be done in case it's any help (Referring to Items on a Sub-Form). It covers more than simple subform referencing btw.

Hope this helps (& I'm not trying to tread on toes here Stewart). Appologies if it seems that way.

[sweeneye]

I hate asking more because you have provided me with so much help today so if you want to leave this to someone else you can!

I have two problems:

1:
despite using the function as you gave me exactly in Access Class Objects, I get the following error message:

Undefined function <name> in expression. (Error 3085)
You entered an SQL expression that includes a Function procedure name that cannot be recognized. Make sure the function exists, that it can be used in SQL expressions, or check the expression to make sure you entered the name correctly.

This is a small ish problem and at the moment I am not getting the annoying parameter pop-up box that I sometimes get so its not essential to sort out

2:
The expersion in the last post would be perfect for treating the Null state of the check box as 'Yes' but I want the SQL query to ignore this if it is Null (including all entries in the table that have either 'Yes' or 'No') instead of treating it as a 'Yes'

I have about 20 check boxes (its a database of problems with our automation systems to be looked up to diagnose the root cause of future issues) and it is neccessary when looking up previous problems to have a blank form with all these check boxes in Null state. and a button to run the Query

For example, to look up all previous issues with 'conveyor B' and 'feeder B' but not the 'controler module', I want to check conveyor B box, check feeder B box and change the 'controler module' box to unticked. And all the other 17 boxes will be left in their null state so the query will return instances where these fields are both 'yes' and 'no' in other words ignoring them when the are Null



I thought it would be a minor issue to get over but i've been stuck on it for so long now its killing me!!!

[sweeneye]

wow it took me so long to write that and other distractions there are two more replys, it looks promissing and I will give it a try, thanks

[sweeneye]

Tried that and its great to make some progress but its a killed when it all falls apart!!! (excuse me for using the actual code and not the simpler examples i had been using)

WHERE (((Forms![Query Form]![IMP/IMPexquery] Is Null)
 Or (Forms![Query Form]![IMP/IMPexquery]= Table1.[IMP/IMPex])))

worked great, picked only yes when ticked, only no when unticked and both when null so i tried to add an and statement to it:

WHERE (((Forms![Query Form]![IMP/IMPexquery] Is Null)
 Or (Forms![Query Form]![IMP/IMPexquery]= Table1.[IMP/IMPex]))
AND ((Forms![Query Form]![LSM/OCR] Is Null)
 Or (Forms![Query Form]![LSM/OCR]=Table1.[LSM/OCR]))
 

but it completely ignores the second part!! it does the exact same for the first bit and doesnt take the second bit into account

[sweeneye]

I think i found the problem, when i looked at the SQL in design view it showed this:

it doesent seem to be linking the parameters properly, instead it creates new ones that dont work as proper AND statements!!

oh well, it looked so nice in SQL

[Stewart Ross]

Hi NeoPa. The problems with form field references typically arise in queries which do extensive grouping and totalling, rather than those involving query criteria in general. It becomes a certainty that a failure will arise if such a query is converted to a crosstab - Jet generates a failure saying that it does not recognise the query field name as a valid name.

For this particular application the use of the indirect function is probably not necessary - but in itself it should not cause any issues, any more than the use of any other function in a query expression would do.

I take no offence at your input at all - I really appreciate your very constructive input and experience on this and other topics you answer and contribute to.

Reading the last posts in this thread there is more to do to resolve the issues arising, and I will check back later when I have more time to look at these in detail.

Cheers

Stewart

My experience of accessing form controls from SQL is probably not as extensive as many, so take what I say with caution. I haven't experienced any problems referencing controls myself. Here is a link on how it should be done in case it's any help (Referring to Items on a Sub-Form). It covers more than simple subform referencing btw.

Hope this helps (& I'm not trying to tread on toes here Stewart). Appologies if it seems that way.

[NeoPa]

...
worked great, picked only yes when ticked, only no when unticked and both when null so i tried to add an and statement to it:

WHERE (((Forms![Query Form]![IMP/IMPexquery] Is Null)
 Or (Forms![Query Form]![IMP/IMPexquery]= Table1.[IMP/IMPex]))
AND ((Forms![Query Form]![LSM/OCR] Is Null)
 Or (Forms![Query Form]![LSM/OCR]=Table1.[LSM/OCR]))

but it completely ignores the second part!! it does the exact same for the first bit and doesnt take the second bit into account

There's a lot in here so I'll try to get to all eventually.

Your WHERE clause looks fine except that it's missing a final closing parenthesis. I don't know if this is Copy/Pasted (it should be) as I would expect Access to show a message in this situatioon rather than allowing the query to run in that state.

[NeoPa]

I think i found the problem, ...
it doesent seem to be linking the parameters properly, instead it creates new ones that dont work as proper AND statements!!

oh well, it looked so nice in SQL

Actually, that is displaying it perfectly (and looks fine as far as I can see).

[NeoPa]

...
I take no offence at your input at all - I really appreciate your very constructive input and experience on this and other topics you answer and contribute to.
...

@Stewart - I'm pleased to hear :)
I'll have to defer to you on the form reference business. I tend not to play there very much (Almost never use Cross-Tabs).

@Sweeneye - To help us better understand your current position, can you post a sanitised version of your SQL. This would include tests for only two of the CheckBoxes and would be tested so you know exactly how it behaves. Please make sure to Cut/Paste the SQL across to avoid any possibility of errors.

[sweeneye]

This little bit of programming has caused me more stress!!! I finally found out my problem and its one of those stupid little things, the code should have looked like this:

SELECT Table1.[IMP/IMPex], Table1.[LSM/OCR]
 
FROM Table1
 
WHERE ((Forms![Query Form]![IMP/IMPexquery] Is Null) 
Or (Forms![Query Form]![IMP/IMPexquery]=Table1.[IMP/IMPex])) 
And ((Forms![Query Form]!LSMquery Is Null) 
Or (Forms![Query Form]!LSMquery=Table1.[LSM/OCR]));

If you cant spot the difference in the WHERE statement it's where I had been naming things slightly incorrectly and thats the only problem!!!

I could have gone on for hours with that, thanks for the help all, its been invaluable!!

[NeoPa]

No worries.

Sometimes, just the exercise of putting the question together enables you to see things you missed earlier.

Congratulations on solving the problem :)