• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1196
  • Last Modified:

Using a Form to Set Criteria on a Query in Access

I need to know how to have a form that I use as a criteria for a query.  I have fields on a form that I want the user to select data from a drop down.  I then want to have the query use the drop down controls in the criteria.  My issue is this.  I know how to point the query at the control on the form (Forms!FormName!ControlName) but there are time when one control will have data and the other will be blank.  How do I make the criteria return all records if the control is blank?
0
dvelasquez
Asked:
dvelasquez
  • 5
  • 4
1 Solution
 
Patrick MatthewsCommented:
Please post the SQL as you have it now.
0
 
dvelasquezAuthor Commented:
SELECT tblNonCompHdr.IDNo, tblNonCompHdr.IDDate, tblNonCompHdr.NCDate, tblNonCompHdr.Facility, tblNonCompHdr.Dept, tblNonCompHdr.Type, tblNonCompHdr.Customer, tblNonCompHdr.Desc, tblNonCompHdr.Closed, tblNonCompHdr.EnteredBy, tblNonCompHdr.SO, tblNonCompHdr.PO, tblNonCompHdr.[Carrier/FF], tblNonCompHdr.[BOL/HAWB], tblNonCompHdr.Mode, tblNonCompHdr.FreightTerms, tblNonCompHdr.CAR, tblNonCompHdr.Fault, tblNonCompHdr.CBNotes
FROM tblNonCompHdr
WHERE (((tblNonCompHdr.Facility)=[forms]![frmNonCompSearch]![Facility]) AND ((tblNonCompHdr.Customer)=[forms]![frmNonCompSearch]![Customer]));
0
 
Patrick MatthewsCommented:
WHERE tblNonCompHdr.Facility Like Nz([forms]![frmNonCompSearch]![Facility], "*") AND
    tblNonCompHdr.Customer Like Nz([forms]![frmNonCompSearch]![Customer], "*");
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cquinnCommented:
WHERE (tblNonCompHdr.Facility Like [forms]![frmNonCompSearch]![Facility]) OR ([forms]![frmNonCompSearch]![Facility] IS NULL)
0
 
dvelasquezAuthor Commented:
WHERE tblNonCompHdr.Facility Like Nz([forms]![frmNonCompSearch]![Facility], "*") AND
    tblNonCompHdr.Customer Like Nz([forms]![frmNonCompSearch]![Customer], "*");

The above SQL work with one exception.  I have another control on the form that I wish to apply the same method to (use a form to feed the query).  This field will contain the "#" character.  When I use the "#" character on the form, the query will return no results.  What do I need to do to account for this?
0
 
Patrick MatthewsCommented:
dvelasquez,

Please explain further.  Also, for the future, please try to state all the requirements/conditions
*up front*, as that tends to reduce the amount of back and forth required to solve the problem.

:)

Regards,

Patrick
0
 
dvelasquezAuthor Commented:
I was not aware that what I was asking for data type specific - sorry for the confusion or lack of information.

The SQL that you sent me work fine, unless the data in one of the controls on the form "Facility" for example contains a "#".  The data values in "Facility" look like:

A#343A
CC#23A
These values cause the query to return null.
0
 
Patrick MatthewsCommented:
If you want to do partial matches:

WHERE tblNonCompHdr.Facility Like "*" & Nz([forms]![frmNonCompSearch]![Facility], "") & "*" AND
    tblNonCompHdr.Customer Like "*" & Nz([forms]![frmNonCompSearch]![Customer], "") & "*";
0
 
dvelasquezAuthor Commented:
My issue is that I have a field in the query called "SO" - it stands for Sales Order.  The actial sales order number looks like (SO#0876787).  For some reason when I enter the sales order number in the form (in the [SO] control, the query that is feed by the form comes up null.  I played around a bit and found out that if I enter a dummy sales order number without the "#" in it,. everything works fine.  Something I am doing is resulting in the "#" in the sales order to not return the results that I am looking for.

I hope I have made this clear and thank you very much for your help.

Here is the query

SELECT tblNonCompHdr.IDNo, tblNonCompHdr.IDDate, tblNonCompHdr.NCDate, tblNonCompHdr.Facility, tblNonCompHdr.Dept, tblNonCompHdr.Type, tblNonCompHdr.Customer, tblNonCompHdr.Desc, tblNonCompHdr.Closed, tblNonCompHdr.EnteredBy, tblNonCompHdr.SO, tblNonCompHdr.PO, tblNonCompHdr.[Carrier/FF], tblNonCompHdr.[BOL/HAWB], tblNonCompHdr.Mode, tblNonCompHdr.FreightTerms, tblNonCompHdr.CAR, tblNonCompHdr.Fault, tblNonCompHdr.CBNotes
FROM tblNonCompHdr
WHERE (((tblNonCompHdr.Facility) Like "*" & Nz([forms]![frmNonCompSearch]![Facility],"") & "*") AND ((tblNonCompHdr.Type) Like Nz([forms]![frmNonCompSearch]![Type],"") & "*") AND ((tblNonCompHdr.Customer) Like Nz([forms]![frmNonCompSearch]![Customer],"")) AND ((tblNonCompHdr.SO) Like Nz([forms]![frmNonCompSearch]![SO],"")) AND ((tblNonCompHdr.PO) Like Nz([forms]![frmNonCompSearch]![PO],"*")));

0
 
Patrick MatthewsCommented:
dvelasquez,

Only you know what combination of full and/or partial matches you need, so I will just explain
the Like operator further and let you finish it off.

The Like operator allows you to compare two strings, and evaluate them for full or partial matches.
For partial matches, you need to use wild cards.  Like the =, >, <, or <> operators, it returns
True or False.

This web page offers a very good overview on how to use Like in parameter queries:
http://office.microsoft.com/en-us/access/HP010322531033.aspx

The only wrinkle is that I was using the Nz() function to return a value when the referenced form
control was blank.

Regards,

Patrick
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now