Combobox filter on a query not working while Null (in the form only)

0
votes

I am currently working on a project on MS-Access 2010 and I am struggling with a issue for hours. I would like to apply a filter on a SELECT/PIVOT statement. The result of that query is displayed in a ListBox.

The issue is that no results are displayed when the Combobox is set to Null. But when I select specific values in that Combobox it works perfectly.

  • The Cbbox filter is declared as a Parameter
  • My query WHERE clause looks like this :
WHERE (Jobs.fk_group=[Formulaires]![frm_MAIN]![lst_filterGroup] AND (fk_otherCritera='XXX')) 
   OR ((([Formulaires]![frm_MAIN]![lst_filterGroup]) Is Null)   AND (fk_otherCritera='XXX'))
  • The query WORKS while I enter manually the value of the parameter (=when I enter an empty string, it displays all the records = what I want)
  • Idk if it is important, but also the listview that I use swap dynamically its recordsource (=it runs 2 differents queries), depending from another Cbbox
  • I checked the parameters values into my VBA code just before MyListview.Requery calls and IsNull(myCbboxReference) returns True and my other criteria is also OK.

I have no clue of what I did wrong, I need help :-(

Best regards, LR

1
sqlms-accessms-access-2010

1 Answers

0
votes

I would recommend to use special value in combobox for displaying all records in main table and don't rely on comparing with Null values. Your query, probably, doesn't work because empty combobox returns "" , not Null.

Also be careful with queries based on references to controls/parameters. Access has a bug: if you apply filter on data in the form, based on query with such kind references, it stops read new values from controls/parameter during Requery. It appears at least for subforms in Datasheet mode. Workaround for this bug - using function instead of reference.