Here are some common mistakes newbies make with Nulls.If you are unclear about Nulls, first read Nulls: Do I need them? If you enter criteria under a field in a query, it returns only matching records. For example, say you have a table of company names and addresses.My plan is to have the user filter though the data using the controls on the form.As the user filters out components, the report should update to display what components pass the new query. Refresh on the button click event, but haven't gotten anywhere.You want two queries: one that gives you the local companies, and the other that gives you all the rest. Unless you set the Required property of the Invoice ID field to Yes in the Invoice Detail table, Access permits Nulls.
The errors that result from improperly handled null entries aren't always obvious and can show up later as erroneous data.It's easy to think of null and blank entries as the same thing, but they aren't equal.The term “null” simply means the data is missing or unknown.Desc, tbl Components.type, tbl Components.vend, tbl Components.vendor PN " search SQLstr Builder = search SQLstr Builder & "FROM tbl Components INNER JOIN tbl Docs ON tbl Comp = tbl Comp " Dim str Comp ID, str Desc, str Type, str Vend, str VPN As String str Comp ID = tbo Comp ID. Desc) Like '*" & str Desc & "*') AND ((tbl Components.type) Like '*" & str Type & "*') AND ((tbl Components.vend) Like '*" & str Vend & "*') AND ((tbl Components.vendor PN) Like '*" & str VPN & "*'));" End If End Function Then you can use str My Value as a parameter in your Record Source.It is also possible to pass multiple values via Open Args, but it's a little more tricky.That's not the same as saying the data doesn't exist, although it may not.Often, null entries are an indication that the value will be forthcoming or that someone is still searching for the data.This query will contain the components that made it past the filter, and some details about those components.Also on the search form, I have a sub-report that is bound to the query.In most cases, you should explicitly block this possibility to prevent orphaned records. Since these records don't match any record in the main form, these orphaned records are never displayed again.For a typical Invoice table, the line items of the invoice are stored in an Invoice Detail table, joined to the Invoice table by an Invoice ID. The user is convinced your program lost them, though they are still there in the table.