Using the "Forms Object" on more than field in a query table

[Kodawari]

I am using a form to collect criteria for a query. The form contains 3
elements for the query: 1) an ID number (the sequential number assigned to
the record by Access when created), 2) a beginning and ending date range, and
3) a name field. In the query, I have used the “forms object†to pull the
data from the form. However, if I place criteria (using the forms object) on
more than one field in the query table, it does not pull the data from the
form (no data at all). If I put the criteria on only one field in the query
table, the query pulls the data from the form just fine (for just that one
element).
Is there some special means to tell Access to accept data from multiple
inputs on a form for a query (using the “forms objectâ€)? Can access accept
the fields that are populated on the form and ignore the fields that are left
blank? Is this the ideal method to do this or is there a better way?
Examples of two of the criteria (in separate fields) being used on the query
table are as follows:
=[Forms]![Q&A data query]![Q&AID]
Between[Forms]![Q&A data query]![date_from] and[forms]![Q&A data
query]![date_to]
Any help would be so much appreciated.
Thanks!!

 

[Dirk Goldgar]

I am using a form to collect criteria for a query. The form contains 3
elements for the query: 1) an ID number (the sequential number assigned to
the record by Access when created), 2) a beginning and ending date range,
and
3) a name field. In the query, I have used the “forms object†to pull the
data from the form. However, if I place criteria (using the forms object)
on
more than one field in the query table, it does not pull the data from the
form (no data at all). If I put the criteria on only one field in the
query
table, the query pulls the data from the form just fine (for just that one
element).
Is there some special means to tell Access to accept data from multiple
inputs on a form for a query (using the “forms objectâ€)? Can access
accept
the fields that are populated on the form and ignore the fields that are
left
blank? Is this the ideal method to do this or is there a better way?
Examples of two of the criteria (in separate fields) being used on the
query
table are as follows:
=[Forms]![Q&A data query]![Q&AID]
Between[Forms]![Q&A data query]![date_from] and[forms]![Q&A data
query]![date_to]
Any help would be so much appreciated.

You probably need to add an "Or [form field] Is Null" alternative in each
criterion:

=[Forms]![Q&A data query]![Q&AID] Or [Forms]![Q&A data query]![Q&AID] Is
Null

=[Forms]![Q&A data query]![date_from] Or [Forms]![Q&A data

query]![date_from] Is Null

<=[Forms]![Q&A data query]![date_to] Or [Forms]![Q&A data
query]![date_to] Is Null

Because you'd be replacing the "Between" criterion with two separate
criteria, you'd need to include your date field again in the query (not
checked on be Show line) in order to apply the second criterion on it.

 

[elbyc]

I am using a form to collect criteria for a query.  The form contains 3
elements for the query: 1) an ID number (the sequential number assigned to
the record by Access when created), 2) a beginning and ending date range,and
3) a name field.  In the query, I have used the “forms object” to pull the
data from the form.  However, if I place criteria (using the forms object) on
more than one field in the query table, it does not pull the data from the
form (no data at all).  If I put the criteria on only one field in the query
table, the query pulls the data from the form just fine (for just that one
element).
Is there some special means to tell Access to accept data from multiple
inputs on a form for a query (using the “forms object”)?  Can access accept
the fields that are populated on the form and ignore the fields that are left
blank?  Is this the ideal method to do this or is there a better way?
Examples of two of the criteria (in separate fields) being used on the query
table are as follows:
=[Forms]![Q&A data query]![Q&AID]
Between[Forms]![Q&A data query]![date_from] and[forms]![Q&A data
query]![date_to]
Any help would be so much appreciated.
Thanks!!

I'm unfamiliar with the forms object, but I have a similar set up
where I need a form to set criteria. I used my form to enter a single
record in a separate one-record data table (Start-Date, End-date,
Region). My Query pulls from the small table and also from a larger,
separate table that has a Begindate field and a region field included.
There is a one-to-all link on the Region field. In the query, I set
the criteria on the Begindate field to be between start-date and end-
date. I can use start-date elsewhere in the query with no problem.