Please help!!! I've been struggling with this for a LONG time and my head about to explode!
I'm trying to run this dynamic query by form (the form consists of combo boxes) but it will only work under certain conditions. The function of the form: when a freezer is selected, then display all contents of the freezer. When no freezer is selected, show all contents in all freezers. When freezer and Shelf is selected, show contents in that particular shelf. - That's to give you an idea of the tree hierarchy.
Ok, so when I select something from ALL the combo boxes, the query works. When I clear the combo box that is at the bottom of the hierarchy (i.e., the most specific one) and run the query - it also works (but only sometimes).
BUT when I load the form and select a value in all combo box, except for the last combo box - I get an error!
I don't get the error when I leave all combo boxes blank. But if I select a freezer only, then I also get the error.
This is the error message:
This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
For the Criteria, I have something similar to this for each ID field:
IIF(IsNull([Forms]![frmSelector]![cboCategorySelect]), [CategoryID],[Forms]![frmSelector]![cboCategorySelect])
(I got it from this link, courtesy of Jack. http://support.microsoft.com/default.aspx?scid=kb;EN-US;q209576)
Please help!!! Thank you!
I'm trying to run this dynamic query by form (the form consists of combo boxes) but it will only work under certain conditions. The function of the form: when a freezer is selected, then display all contents of the freezer. When no freezer is selected, show all contents in all freezers. When freezer and Shelf is selected, show contents in that particular shelf. - That's to give you an idea of the tree hierarchy.
Ok, so when I select something from ALL the combo boxes, the query works. When I clear the combo box that is at the bottom of the hierarchy (i.e., the most specific one) and run the query - it also works (but only sometimes).
BUT when I load the form and select a value in all combo box, except for the last combo box - I get an error!
I don't get the error when I leave all combo boxes blank. But if I select a freezer only, then I also get the error.
This is the error message:
This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
For the Criteria, I have something similar to this for each ID field:
IIF(IsNull([Forms]![frmSelector]![cboCategorySelect]), [CategoryID],[Forms]![frmSelector]![cboCategorySelect])
(I got it from this link, courtesy of Jack. http://support.microsoft.com/default.aspx?scid=kb;EN-US;q209576)
Please help!!! Thank you!
