Search code examples
sqlformsms-access

MS Access Continuous Form: How to search a record where one field is empty?

Here is my stocks table:

product_group dimension
TI 8x4
WN
YS 7x9
ST 10x15

I made a query stocksListQ and created a form findStocks based on that, adding a GroupSearch and DimensionSearch textboxes for the user to input value and query the table.

My current criteria for product_group: Like "*" & [Forms]![findStocks]![GroupSearch] & "*" Or Is Null

My current criteria for dimension: Like "*" & [Forms]![findStocks]![DimensionSearch] & "*" And Not Is Null OR [Forms]![findStocks]![DimensionSearch] Is Null

Currently, DimensionSearch is working properly but GroupSearch isn't. No matter what I type into GroupSearch, EVERY record will be displayed. How can I modify my GroupSearch criteria so that they will display the correct results?

For example:

  • If I type "T", table should only show TI and ST
  • If i type "WN", table should only show WN

Note: my product_group criteria seems to work when I remove the criteria for dimension. Thank you in advance.

Solution

  • This reduced expression works:

    Like "*" & [Forms]![findStocks]![DimensionSearch] & "*" OR [Forms]![findStocks]![DimensionSearch] Is Null

    But notice, that the unbound textbox DimensionSearch must be exited (move to another control) to update.