Use unbound form to run query

[dizzydangler]

Hi all,

I am a new Access user and just starting to get my head around some of the basic concepts, so please take it easy on me :) My company has been managing client records on excel, and I’m in the process of migrating the data over to Access 2007 (Windows XP), kind of learning as I go. I’ve managed to import the client records into a single table, and set up a “single view” form that streamlines how we input new client data. Now I’m trying to set up a “single view” form that would allow anyone with access to the database to run a query that returns whatever information they want using as few steps as possible.

So far, I’ve been able to set up this form to receive input via unbound text/date selection/check boxes, but I can’t get the criteria row of the query to reference any of the data entered in the controls. I’ve tried entering Forms!NameOfForm!NameOfUnboundField in the criteria cell of the query, but when I run the query to test it, it just puts brackets around the form name and unbound field name and prompts for parameter input.

Here’s what I’m trying to do, and I’m hoping there’s some simple hint that can get me started on the right track:

-I want to make the criteria row of the query reference the values (such as client name, date of visit, client’s region, etc) that users input into the unbound form.

-I want users to be able to select multiple values for certain fields (such as “client’s region,” that have a finite range, because they’re entered to the database from the input form using a list box) using check box controls in the form and get the query to return all associated records (as long as they meet the criteria specified in the other controls on the form).

-I want to allow users to use check boxes to specify which fields (within the returned records) the query displays, regardless of the filtering criteria set.

-Finally, I want to ensure that users can leave a control on the form blank and still get records back (for instance, if the “date” box is left blank, the query should return ALL records that meet the other filtering criteria). I’ve seen this is a problem in some related threads. Or, if it’s easier, they could “activate” a filtering criteria by clicking a check box. (“If you want to filter results by date, click here:”)

I realize I may be asking a lot, particularly with my lack of experience with Access, but I’m pretty much at the end of my rope with the help files and reading threads posted by other people. Any help would be SO appreciated!

Thanks!

Joe

[MMcCarthy]

There are a couple of things you need to check.

First of all is the form being left open while the query is running. If the form is closed the criteria will not work.

Secondly try entering the criteria as follows:
Forms![NameOfForm]![NameOfUnboundTextbox]
The brackets are required particularly if there are any spaces in the form name or the textbox name. Also you will notice that I changed the last part to NameOfUnboundTextbox rather than NameOfUnboundField. They are not necessarily the same thing. Check that the property name of the textbox is what you are using and not the field name.

Lastly make sure the unbound control is not on a subform as this is a different syntax.

Mary

[dizzydangler]

Thanks for the post, Mary! I'm getting the form to work with the query now, after a lot of trial and error.
I'm trying to write the criteria in the query in such a way that they will filter records when only when a user enters text, and allow all records to be returned when left blank. An example of one of my criteria is:

Like nz("*"&([Forms]![NameOfForm]![NameOfUnboundTextbox])&"*", "*"), which lets the user enter a part of a field (like a client's last name, for instance), and get back all records that have a match in that field.
The problem now is that there are a lot of "null value" fields in the records, where data was never entered, and this criteria won't return any records with null values in the fields it's querying. Is there a way to use the unbound controls to filter records without excluding records with null values?

Thanks!

Joe

[dizzydangler]

OK, I got it to work!!!

I am working straight out of the query now, it was simpler for me, but I was able to put together a QSL code that gives me what I want--if all parameters are left blank, the query returns everything, but as soon as text is entered in a parameter, it begins to filter by that field by whatever text is entered. I'm attaching the code for anyone else who might be trying to do a similar operation:

It's a little clumsy, I think, and probably has a few lines that are not required, but I've spent so much time with this I don't want to change it now that I've finally got it working.

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT [Client Database].Date, [Client Database].[Department], [Client Database].Office Building, [Client Database].[Client Name], [Client Database].Meeting Type, [Client Database].Region, [Client Database].City, [Client Database].[# of Meetings]
 
FROM [Client Database]
 
WHERE 
 
(IIf([Enter Office Building Name:] Is Null,(([Client Database].Office Building) Like "*" & [Enter Office Building Name:] & "*" Or ([Client Database].Office Building) Is Null),(([Client Database].Office Building) Like ("*" & [Enter Office Building Name:] & "*"))) 
 
And (IIf([Enter Client Name:] Is Null,(([Client Database].[Client Name]) Like "*" & [Enter Client Name:] & "*" Or ([Client Database].[Client Name]) Is Null),(([Client Database].[Client Name]) Like ("*" & [Enter Client Name:] & "*")))) 
 
And (IIf([Enter Meeting Type:] Is Null,(([Client Database].Meeting Type) Like "*" & [Enter Meeting Type:] & "*" Or ([Client Database].Meeting Type) Is Null),(([Client Database].Meeting Type) Like ("*" & [Enter Meeting Type:] & "*")))) 
 
And (IIf([Enter Region:] Is Null,(([Client Database].Region) Like "*" & [Enter Region:] & "*" Or ([Client Database].Region) Is Null),(([Client Database].Region) Like ("*" & [Enter Region:] & "*")))) 
 
And (IIf([Enter City:] Is Null,(([Client Database].City) Like "*" & [Enter City:] & "*" Or ([Client Database].City) Is Null),(([Client Database].City) Like ("*" & [Enter City:] & "*")))) 
 
And ((IIf(([Enter Start Date:] And [Enter End Date:]) Is Null,(([Client Database].Date) Like nz((([Client Database].Date)>=[Enter Start Date:] And ([Client Database].Date)<=[Enter End Date:]),"*")),([Client Database].Date)>=[Enter Start Date:] And ([Client Database].Date)<=[Enter End Date:]))<>False) 
 
And ((IIf(([Enter Department: (EX: HR)]) Is Null,(([Client Database].[Department]) Like nz(([Client Database].[Department]),"*")),(([Client Database].[Department]) Like nz([Enter Department: (EX: HR)],"*"))))<>False));

Mary, thanks to your tips, I can now link the query to the form, so my next step is to go back in and build the form to drive this query.

Thanks again!

Joe

[MMcCarthy]

@dizzydangler
You're welcome anytime Joe.

Without knowing your database better I can't say if the criteria is correct for sure but if it ain't broke don't fix it :D