Need help with a search query

[Buns1976]

Hi Everyone,



I am very new to Access and need some help with some queries. I have one Table that contains a Date, PLUNumber, Description, PriceEach, ItemsSold, and TotalSales fields.

I have a date range query already set up but I am looking for a way to enter a scan or type a description in a search box with a date range picker
and show that item for the dates selected.

Any help would be greatly appreciated.

Thank you!

[June7]

Review http://allenbrowne.com/ser-62.html

[theviking]

As Allen Browne states, dynamically generating a where clause through code is the most efficient way to filter your records. However if you are not yet comfortable with coding you can get around the issues he describes with using a Like statement with the Nz function. Build a query which mirrors your base table. Use a Like statement as indicated to filter the query based upon an unbound form field. But instead of your search criteria executing directly against the table column, create a new column with an Nz function against your search field to ensure the table column is always populated. Otherwise as Allen indicates, any nulls will be excluded from your search results.

Something like this (no pun intended


To search on a date column you would typically use a Between statement with the same type of logic.


Again, I'm not recommending this as the best way for an experienced developer to filter form records. Functions and Like statements with wildcards in queries are generally inefficient so in a very large table you will definitely see a performance hit. But in a smaller table against an Access db it's not noticeable and the level of effort (and expertise) is greatly reduced over coding your where clause. You can literally write these in minutes.