Multiple Field Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My query prompts for two inputs from the user :

[What date do you want to select?] & [What tech do you want to select?]

the first is for the DateofEntry field and the second for technician name
field.

What do I put in the fields to make sure that I get data all the dates in
the database when [What date do you want to select?] is blank? or all the
technicians on the 1st prompt date when [What tech do you want to select?] is
left blank? Thanks in advance!!
 
Try using Like with NZ

SELECT Table1.DateofEntry , Table1.technician
FROM Table1
WHERE (((Table1.DateofEntry) Like nz([What date do you want to
select?],"*")) AND ((Table1.technician) Like nz([What tech do you want to
select?],"*")))
 
If you are doing this in the query grid.

Field: YourDateField
Criteria: [What date do you want to select?] or [What date do you want to
select?] is Null

Field: Technician
Criteria: [What tech do you want to select?] or [What tech do you want to
select?] Is Null


You should be using the same criteria line for both YourDateField and
Technician. Access will rearrange this query criteria after you save it to at
least four lines. Also, this technique rapidly becomes to complex for Access to execute.
 
Thanks for the replies!!!

When Date has a value and technician is null I don't get any records
when date is null and technician has a value I also don't get any records
When both are null I get all records.


John Spencer (MVP) said:
If you are doing this in the query grid.

Field: YourDateField
Criteria: [What date do you want to select?] or [What date do you want to
select?] is Null

Field: Technician
Criteria: [What tech do you want to select?] or [What tech do you want to
select?] Is Null


You should be using the same criteria line for both YourDateField and
Technician. Access will rearrange this query criteria after you save it to at
least four lines. Also, this technique rapidly becomes to complex for Access to execute.
My query prompts for two inputs from the user :

[What date do you want to select?] & [What tech do you want to select?]

the first is for the DateofEntry field and the second for technician name
field.

What do I put in the fields to make sure that I get data all the dates in
the database when [What date do you want to select?] is blank? or all the
technicians on the 1st prompt date when [What tech do you want to select?] is
left blank? Thanks in advance!!
Click to expand...
Click to expand...
 
Where do I put the code you sent? I am a newbie!

Ofer said:
Try using Like with NZ

SELECT Table1.DateofEntry , Table1.technician
FROM Table1
WHERE (((Table1.DateofEntry) Like nz([What date do you want to
select?],"*")) AND ((Table1.technician) Like nz([What tech do you want to
select?],"*")))


truepantera said:
My query prompts for two inputs from the user :

[What date do you want to select?] & [What tech do you want to select?]

the first is for the DateofEntry field and the second for technician name
field.

What do I put in the fields to make sure that I get data all the dates in
the database when [What date do you want to select?] is blank? or all the
technicians on the 1st prompt date when [What tech do you want to select?] is
left blank? Thanks in advance!!
Click to expand...
Click to expand...
 
Did you put the entire phrase "[What date do you want to select?] or [What date
do you want to select?] is Null" in ONE criteria cell under the your date field
and on the same criteria line did you put the other phrase all in one criteria cell?

If so, it should have worked. If you are still having problems, post your SQL statement.

Open the query in design view
Select SQL from the View menu
Copy the text from the window
Paste the text into the message


Thanks for the replies!!!

When Date has a value and technician is null I don't get any records
when date is null and technician has a value I also don't get any records
When both are null I get all records.

John Spencer (MVP) said:
If you are doing this in the query grid.

Field: YourDateField
Criteria: [What date do you want to select?] or [What date do you want to
select?] is Null

Field: Technician
Criteria: [What tech do you want to select?] or [What tech do you want to
select?] Is Null


You should be using the same criteria line for both YourDateField and
Technician. Access will rearrange this query criteria after you save it to at
least four lines. Also, this technique rapidly becomes to complex for Access to execute.
My query prompts for two inputs from the user :

[What date do you want to select?] & [What tech do you want to select?]

the first is for the DateofEntry field and the second for technician name
field.

What do I put in the fields to make sure that I get data all the dates in
the database when [What date do you want to select?] is blank? or all the
technicians on the 1st prompt date when [What tech do you want to select?] is
left blank? Thanks in advance!!
Click to expand...
Click to expand...
Click to expand...
 
I didn't have it on the one line. Thanks! That works great. I was wondering
about your opinion on the following criteria. It returns all records, even
when both dates are NOT null...... I based it on your prior fix for me.
=[What beginning date do you want?] or [What beginning date do you want?] is Null AND <=[What ending date do you want?] or >=[What ending date do you want?] is Null
Click to expand...

Thanks a million!

P


John Spencer (MVP) said:
Did you put the entire phrase "[What date do you want to select?] or [What date
do you want to select?] is Null" in ONE criteria cell under the your date field
and on the same criteria line did you put the other phrase all in one criteria cell?

If so, it should have worked. If you are still having problems, post your SQL statement.

Open the query in design view
Select SQL from the View menu
Copy the text from the window
Paste the text into the message


Thanks for the replies!!!

When Date has a value and technician is null I don't get any records
when date is null and technician has a value I also don't get any records
When both are null I get all records.

John Spencer (MVP) said:
If you are doing this in the query grid.

Field: YourDateField
Criteria: [What date do you want to select?] or [What date do you want to
select?] is Null

Field: Technician
Criteria: [What tech do you want to select?] or [What tech do you want to
select?] Is Null


You should be using the same criteria line for both YourDateField and
Technician. Access will rearrange this query criteria after you save it to at
least four lines. Also, this technique rapidly becomes to complex for Access to execute.

truepantera wrote:

My query prompts for two inputs from the user :

[What date do you want to select?] & [What tech do you want to select?]

the first is for the DateofEntry field and the second for technician name
field.

What do I put in the fields to make sure that I get data all the dates in
the database when [What date do you want to select?] is blank? or all the
technicians on the 1st prompt date when [What tech do you want to select?] is
left blank? Thanks in advance!!
Click to expand...
Click to expand...
Click to expand...
 
I guess I should just require re-rentry if someone wants a range and enters
null instead. If they want a single date, just make them type it for the
beginning date and ending date?

P
 
In the case you've posted you are probably going to need to include the field
you are querying in the grid TWO TIMES. Then you put one set of criteria under
the first copy of the field and the other set of criteria under the second copy
of the criteria.


Field: BeginDate: YourDateField
Criteria: >=[What beginning date do you want?] or [What beginning date do you
want?] is Null

Field: EndDate: YourDateField
Criteria: <=[What ending date do you want?] or >=[What ending date do you want?]
is Null

I didn't have it on the one line. Thanks! That works great. I was wondering
about your opinion on the following criteria. It returns all records, even
when both dates are NOT null...... I based it on your prior fix for me.
=[What beginning date do you want?] or [What beginning date do you want?] is Null AND <=[What ending date do you want?] or >=[What ending date do you want?] is Null
Click to expand...

Thanks a million!

P

John Spencer (MVP) said:
Did you put the entire phrase "[What date do you want to select?] or [What date
do you want to select?] is Null" in ONE criteria cell under the your date field
and on the same criteria line did you put the other phrase all in one criteria cell?

If so, it should have worked. If you are still having problems, post your SQL statement.

Open the query in design view
Select SQL from the View menu
Copy the text from the window
Paste the text into the message


Thanks for the replies!!!

When Date has a value and technician is null I don't get any records
when date is null and technician has a value I also don't get any records
When both are null I get all records.

:

If you are doing this in the query grid.

Field: YourDateField
Criteria: [What date do you want to select?] or [What date do you want to
select?] is Null

Field: Technician
Criteria: [What tech do you want to select?] or [What tech do you want to
select?] Is Null


You should be using the same criteria line for both YourDateField and
Technician. Access will rearrange this query criteria after you save it to at
least four lines. Also, this technique rapidly becomes to complex for Access to execute.

truepantera wrote:

My query prompts for two inputs from the user :

[What date do you want to select?] & [What tech do you want to select?]

the first is for the DateofEntry field and the second for technician name
field.

What do I put in the fields to make sure that I get data all the dates in
the database when [What date do you want to select?] is blank? or all the
technicians on the 1st prompt date when [What tech do you want to select?] is
left blank? Thanks in advance!!
Click to expand...
Click to expand...
Click to expand...
 
I have added the following coding to each of my search fields on my query.

Technician Name field:
Forms![Search Selection Dialog]![findtech] or Forms![Search Selection
Dialog]![findtech] is Null

where Technician Name field is the table field name and findtech is the
search form name.

I have seven of these seach criteria on the search form and it will not
restrict the records. Access made additional entries for me (creating
additional colums in the query). It worked when I only used two fields for
filtering!!! There has to be a better way!
 
There is - but it involves building your where clause (or the entire query)
using VBA behind a form.

Check out the following site for some ideas
Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm


You might want to consider the Query By Form applet from Duane Hookom at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Form'
or an earlier version at
http://www.invisibleinc.com/download.cfm?filFilID=7

***FEATURES***

The DH QBF is a complete query by form applet that can be easily integrated into
any existing Access application. Typically, the functionality provided by DH QBF
can replace many "canned" reports. The developer imports several forms, tables,
a query, and a report from the DH_QBF.mdb, creates some master queries, and deploys.

The developer creates one or more master queries that join tables, alias
field names, create calculated columns, etc. The users can then select a master
query (datasource) from a drop-down and then select up to 30 fields from the
master query. Users can define sorting and criteria as well as grouping and
totaling. All of this "design" information is stored in two tables for re-use.

The results of the queries are displayed in a datasheet subform contained in a
main form. The main form has options to send/export the records to print, Word
table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most formats
allow he user to automatically open the target application. The Word merge
process will open a new Word document and link to the merge fields.
 
Back
Top