0

Sorry if this is an overly easy question, I don't use access often but was asked to make a search form for a table our company uses to track remake parts. I have a form created with a series of unbound fields where users can input their criteria to search the table.

The table sometimes has null or blank values because not all fields are relevant for every entry. Each entry is manually entered by an employee when they discover a part needs to be remade so I am not sure if fields they don't fill out are left as blank or are null.

The search form is partially working, but it's not pulling any records that have null or blank values values in any of my search criteria (even if that specific field was left blank on the form) and I can't figure out how to get them to populate in the results when the form query is run. I though adding the Or criteria of is null would get these results, but it doesn't seem to be working like I thought it would.

There are 14 possible search criteria and the query should pull in about 30 columns of info for each returned record.

Any ideas on what I am doing wrong here? See pictures for examples.

Examples of two columns in the tables where not all records have an order number or line item associated. These are the records I am unclear if they are just blank or are null values.

Design View of the form

Example of the query criteria I am using for the form

1 Answer 1

0

I figured it out.

This link had the answers I needed.

https://answers.microsoft.com/en-us/msoffice/forum/all/is-null-not-working-in-access-query-criteria/7274dcca-2798-43aa-8578-97304119d3d5

The fix was to add "[Forms]![SearchForm]![UnboundFieldNameFromForm] Is Null" as the Or criteria.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.