Using a form to request multiple user parameters - needs help to be more robust

[whr4th]

Using Access 2010

My database is about 800,000 lines of my city's appraisal district property records. I created a form following this template (Use a parameter to make a query ask for input - Access - Office.com) and it works pretty well. The link only uses one parameter as an example, but I just added the multiple parameters myself. Pretty simple stuff.

Here's where I am stuck (disclosure: I'm not so hot with Access, but pretty good with Excel):
1) The created form has two actionable buttons - Ok, and Cancel. The Ok button (via a macro) takes you to the Query data sheet, but does NOT refresh the data to recognize the new/updated parameters. I have tried adding Refresh and RefreshRecord as a New Action both before and after the macro opens the query datapage. The only way I can get the query to refresh is to manually click the Refresh All button on the home tab. It needs to refresh upon clicking the Ok button.

2) Some of the values in the fields are null/blank values. Examples a things like year the home was built or date of a deed transfer. I'd like to be able to do two things: A) leave a field in my form blank and B) return records with null values (at my option). Example: I set the date parameter range from blank (or from the beginning of time) to end of 2012. If I enter 1/1/1900 to capture early records, it still misses records with null values in the date field.

3) Finally, and seemingly simplest, is that I'd like to clear/reset all the parameters and view the whole dataset if I want to use simple filtering for some reason. Sure, I could create a separate query, but that seems a little redundant. How can I do this?

Think that covers it. Any and all help would be appreciated.

 

[alansidman]

I think the issues you refer to are covered in this tutorial.

Macromedia Flash (SWF) Movie Created by Camtasia Studio 2

Alan

 

[whr4th]

Sort of. Since that's the old version of Access, I'm having a hard time following it all.

#1: Thoughts on refresh?
#2: The video doesn't solve my issue I don't think. He uses "Like" in the criteria, I am using "Between...And". Here is my criteria for one of the fields: "Between [Forms]![Main Form]![Min Beds] And [Forms]![Main Form]![Max Beds]" How can I change this to allow me to search for anything including nulls up to 4 bedrooms? Zero to four ignores null bedroom values. Not all records show the # of bedrooms.
#3: Tried his solution, but couldn't get that to work either. I can't figure out the syntax with the "me.".... Using the above field as an example - what should it look like? I have set the button up to go to an "Event Procedure" when it's clicked. I have also tried creating a macro, but can't figure that out either.

Thanks in advance.

 

[xenou]

#2: The video doesn't solve my issue I don't think. He uses "Like" in the criteria, I am using "Between...And". Here is my criteria for one of the fields: "Between [Forms]![Main Form]![Min Beds] And [Forms]![Main Form]![Max Beds]" How can I change this to allow me to search for anything including nulls up to 4 bedrooms? Zero to four ignores null bedroom values. Not all records show the # of bedrooms.

Set your database table to have a default value of zero. Or, use the Nz() function to coerce Nulls to zero so they can be matched against zero.


It's hard to answer a lot of questions at once so this may take some unpacking in future posts (feel free to "fork" your questions into discrete topics in their own threads):

1) The created form has two actionable buttons - Ok, and Cancel. The Ok button (via a macro) takes you to the Query data sheet, but does NOT refresh the data to recognize the new/updated parameters. I have tried adding Refresh and RefreshRecord as a New Action both before and after the macro opens the query datapage. The only way I can get the query to refresh is to manually click the Refresh All button on the home tab. It needs to refresh upon clicking the Ok button.

I have found that using Me.Requery in the button code is the most reliable way to really requery (fully refresh) a dataset. To be honest, I don't know what the refresh command really does - but it doesn't seem to fully live up to its promise.

2) Some of the values in the fields are null/blank values. Examples a things like year the home was built or date of a deed transfer. I'd like to be able to do two things: A) leave a field in my form blank and B) return records with null values (at my option). Example: I set the date parameter range from blank (or from the beginning of time) to end of 2012. If I enter 1/1/1900 to capture early records, it still misses records with null values in the date field.

See above - Alan's video, my response. Etc. Etc. Nulls are tricky. If you still are having trouble start a new thread on this topic by itself.

3) Finally, and seemingly simplest, is that I'd like to clear/reset all the parameters and view the whole dataset if I want to use simple filtering for some reason. Sure, I could create a separate query, but that seems a little redundant. How can I do this?

You probably have to be creative with use of Like and/or Nz(). Like has the advantage of being able to match "nothing" (so to speak -- Like "*"). Using LIKE criteria is probably the way to do it "dynamically" - that is, being able to get all data or filtered data using a single parameterized query. I don't have a lot of experience with this as I do it sparingly. Otherwise, you do use a separate query (one where you remove the WHERE criteria) - or at least use string replacement to take it out at runtime. No one solution.