Hi rayape,
before this year is over let's go the whole hog.
I think I got it. There are two reasons for the undesired/missing function of your query: 1. the operator 'AND' and 2. the value of the false part of your IIf-statements.
If a checkbox isn't checked, the statement returns the value "", i.e. an empty string. But this value obviously cannot be evaluated by Access-SQL. So either you hand over a parameter or you don't. But you cannot hand over an empty one (do not confound with 'null').
I've asked you if you need the checkboxes at all costs. Because without them you can get the same functionality with less effort. I changed your query to a similar one to mine posted last time. This changed query ('qryExpenses') has no IIf-statements anymore. It evaluates only the two comboboxes. Easy to read, easy to understand, easy to handle!
From a logic point of view it should also be possible to return all the records of the table. We can achieve this by leaving both comboboxes empty and by adding another "Or" line in the query handling the case that both parameters are null.
If you open the form 'Expenses' and press the 'Run Query' button you will see all records of the table shown in the query. Then you may select a value in one or both combobox(es) and press 'Run Query' again (without having closed the query window before. VBA code takes care of this).
Besides... the strange error message I saw the last time was caused by a data type incompatibility. As you noticed, one of the criteria columns in 'tblDemo' has a numeric data type which does not go with the false-part of the IIf-statement. -
Little I know about the data structure of your application but if you save 'Stores' and 'Categories' as text values you will have to change the whole column of your table in case the term should be renamed or in case of a spelling change. I recommend you to use separate tables ('tblCategories', 'tblStores') and replace the values by the corresponding numeric values ('ID'). After this you should change the field data type of the two fields in 'Expenses' and create some relationships between the tables.
One more thing: if I say "Expenses", do you know what I mean? Maybe. Maybe, because you have a table and a form in your application with the
same name. - And if I tell you something about 'Inventory' (e.g. in my database), do you know what is meant? No, you don't. 'Inventory' can be a table, a query, a form, a textbox, a listbox, a recordset or whatever. Therefore Access developers normally use prefixes, like 'txt' for a textbox, 'tbl' for a table, "chk" for a checkbox and so on. Well known is the so called 'Reddick VBA Naming Convention' (search the web for further details). But you don't have to use this one. You can create your own convention if you want to. Important is only you use one at all. Your application will be so big and complex some day that you will get lost reading code without a naming system. Or maybe it will not grow but you start developing another application and half a year later you open the first database again. Would you remember everything? - And last but not least, if you share code with others - like you did here in this forum - using prefixes it's a lot easier for them to understand. Sorry, but control names like 'Check17' or 'Combo13' are really bad. This time it was me who had "a very hard time understanding the code".
Object names in general and control names in particular should be descriptive, like 'tblExpenses', 'qryExpenses', 'cboStores', 'chkIncludeCategory' and so on.
Have a good start into a new Access year!
StarGrabber
