Table Linking

[MJM2244]

I'll start out the question with what I'm trying to accomplish. Basically,
I've created a form that populates data into a table (lets call it table 1).
I want to be able to reference this table against a master table (call it
table 2) and return the matching results. The form has a command button that
runs a query that links these two tables and returns the resulting matches.
The problem I am having is that if a user wanted to leave one of the fields
blank on the populating form the query will not return anything. So lets say
the form asks for State, City, County and Zip Code all of these then populate
the corresponding column in Table 1. If the user only wanted to enter the
County and Zip Code fields how can I get the query to still return these
matches. I know the query is then saying the State field in Table 1 is blank
there are no blank State fields in Table 2 so return nothing. Is there some
kind of wildcard character or join property that can still link the two
fields from the tables if one of the fields in Table 1 is blank.

Thank You

 

[Michel Walsh]

You may either add an OR FieldName IS NULL to make something like:

(State = FORMS!FormName!Sate OR State IS NULL) AND (City =
FORMS!FormName!city OR City IS NULL ) AND ( ... )


in SQL view of your query of your query,


either produces the SQL statement with the not empty criteria:

Dim str AS String
str= " true "
if( 0 <> len( Me.State & vbNullString) ) then str = str & " AND
(State = FORMS!FormName!Sate OR State IS NULL) "
if( 0 <> len( Me.City & vbNullString)) then str= str & " AND (City =
FORMS!FormName!city OR City IS NULL ) "
...


and use the string as criteria either directly, either appending the other
SQL parts to it to make a complete SQL statement.




Hoping it may help,
Vanderghast, Access MVP