Option to leave field blank in form

[kmhoyt14]

It's been a few years (longer than I care to admit) since I've done VBA or access and I'm trying to relearn what little I knew so bare with me. I have a query that is based on unbound boxes in a form. I want the user to have the option to leave some of those boxes blank and the query just use whatever given information the user puts in the boxes. What's the best/ easiest functions to do this?

 

[namliam]

Easiest solution is to bind the form to the table directly and have access to the "heavy lifting" for you.

 

[Deleted member 147267]

If this is a search form, it is perfectly normal for the controls to not be bound, yes?

 

[Deleted member 147267]

Pat, isn't that the opposite of what was asked for?

and the query just use whatever given information the user puts in the boxes.

I take that to mean if the control wasn't filled in, don't use it in the criteria at all.

 

[Deleted member 147267]

I did look at the clause and this is how I interpreted it:
- if field has a value, return records that match that value
OR
- if field is null, return records that contain null values in that field
Is that not correct?

 

[kmhoyt14]

I want the query to return results that are selected from the form and if there are blank criteria then I want it to not use the criteria in that box. I was getting an error when I tried the Where function when I extended it to more than 1 criteria. When I had the Where (fld1 = Forms!yourform!fld1 OR Forms!yourform!fld1 Is Null) under one of the fields I tried to use it with my other two fields that are required and it returned all results for all three criteria individually rather than finding results that contained only the selected criteria.

 

[Deleted member 147267]

Pat, you're not getting it and I don't wish to debate this further. My point is along this line: one interpretation of the op is that IF the form control is blank, don't include it in the results. This would require looping through all the controls and has nothing to do with including it regardless of whether or not it is null. Your version includes it regardless of that fact. I'm not saying your interpretation is incorrect, but you and I might do less bantering if you tried harder to understand my points rather than arguing yours. I will leave you guys to this one.

 

[kmhoyt14]

Below is the SQL of what I had before using the is null. This worked when I tested it on 3 criteria but then I extended it to all 6 criteria the search results would show nothing. Any and all help is appreciated.

SELECT [FM Data].Zone, [FM Data].Mixer, [FM Data].Line, [FM Data].Equipment, [FM Data].Type, [FM Data].Color, [FM Data].[Item#], [FM Data].Source, [FM Data].[FM Library#], [FM Data].[Part#], [FM Data].Attachments
FROM [FM Data]
WHERE ((([FM Data].Zone) Like "*" & [Forms]![SearchForm]![Zonetxt] & "*") AND (([FM Data].Mixer) Like "*" & [Forms]![SearchForm]![Mixertxt] & "*") AND (([FM Data].Line) Like "*" & [Forms]![SearchForm]![Linetxt] & "*") AND (([FM Data].Equipment) Like "*" & [Forms]![SearchForm]![Equiptxt] & "*") AND (([FM Data].Type) Like "*" & [Forms]![SearchForm]![Typetxt] & "*") AND (([FM Data].Color) Like "*" & [Forms]![SearchForm]![Colortxt] & "*"))
ORDER BY [FM Data].Zone, [FM Data].Mixer, [FM Data].Line, [FM Data].[Item#], [FM Data].[FM Library#], [FM Data].[Part#];