Custom Paramter Query....not getting the right data back

[Travis818]

I have created a database for a small gym where they are capturing data on members for personal training purposes. I have created a custom parameter query that grabs its information from a search form, then shows the data in a separate results form. The search form is called ‘frmMemberSearc h’; the query is called ‘qryMemberSearc h’ and the results form is called ‘frmMemberResul ts’. In the query, I have the following criteria expression for the 5 searchable fields in the search form…[Forms]![frmMemberSearch]![Height] Or Like [Forms]![frmMemberSearch]![Height] Is Null


If I populate the ‘Height’ combo box, I want the results to come back showing me only the records with that specific ‘Height’ I selected. However, if someone failed to put a person’s height into the system, leaving the field blank; I end up getting that piece of data too. On the other hand, I want the null values captured, but only if I leave the value null in my search form. I don’t want null values in the table to be retrieved when I have selected a specific value from my search combo box. The easy solution would be to make the fields required, but I can’t necessarily do that.

[Steven Kogan]

If I'm understanding correctly, just looking at height, the SQL would be something like:

SELECT Test.Height
FROM Test
WHERE (((Test.Height)=[Forms]![frmMemberSearch]![Height])) OR (((Test.Height) Is Null) AND (([Forms]![frmMemberSearch]![Height]) Is Null));

Does that do what you want?