Multiple parameter query and empty parameter

[thewrightdesign]

I'm doing a fairly simple database, one table with 20 or so fields. Want to do a simple parameter query that when run asks you to enter Institution, Start Date, End Date, and a Pass/Fail value. We want to enter all or some of these values and get the results. For example, might need to search all the records from one institution during a certain date range regardless of pass/fail status. Also might need to see all the pass/fails within a date range regardless of institution. Also might need to see all pass or fail from a certain institution no matter the date. So we need to be able to enter criteria in the pop up parameter box or not enter it, and if the criteria is not entered then all in that column need to be returned. Make sense? If not I'll be happy to clarify.

I can get it to work with two of the columns, not all three no matter how I try this. I've had them as OR and as AND (all on different lines in the query design or on same line) but nothing seems to work. We've tried Like statements for wildcards, between for the dates, and no matter what I can't get it to add in the pass/fail and work correctly. I have it working correctly for the first two columns, but not when I add that third. Here is the code that works for the first two:

SELECT

.[ID #],

.Last,

.First,

.Institution,

.[Result Date],

.Pass, ****other table fields deleted****
FROM

WHERE (((

.Institution)=[Instutition:])) OR (((

.[Result Date]) Between [Start Date:] And [End Date:]));


Here is one of the things I tried to add in the pass/fail field:

SELECT

.[ID #],

.Last,

.First,

.Institution, [Dead Records].[Result Date],

.Pass,
FROM

WHERE (((

.Institution)=[Instutition:])) OR (((

.[Result Date]) Between [Start Date:] And [End Date:])) OR (((

.Pass) Like [Yes or No]));

I'm using Access 2003.

Any ideas into what I'm not doing right to return the results I want it to being able to enter or not enter criteria in any of the three (four with end date) parameter input fields?

Thanks in advance.

 

[jdraw]

Why not start by creating a query for each question?

And then work on the logic to put those queries or parameters together.

You might even consider a form with some comboboxes to select options and then build the proper SQL for a "customized" query, then run the query.

There are some tutorials at
http://www.fontstuff.com/access/acctut18.htm

 

[thewrightdesign]

I can certainly make it work with more than one query, boss wants just one, I have no idea why she's set on that but she is.

I can do comboboxes for the instutitions and the pass/fail I guess, but the date range seems like it would be easier to do as input. What you're talking about requires building a form which we really weren't doing since it's all in datasheet view. She just wanted to run the query, input text and go. Doesn't make sense that it won't do it on three criteria.

I'll probably end up building her a search form to enter her data on, was just hoping there was an easy explanation why this seemingly simple query won't work on three criteria but only two at a time. Clearly there is something I'm not doing right, I didn't think Access was limited like that.

Edited to add: I talked to boss and she'll let me do it however it works, yay. Just need to make sure that we can pull a report off whatever we get going so if I do link queries, need to still be able to output the data into the report I created. I'm assuming that will be possible by using the linked queries as the data source for the report. Please someone correct me if I'm wrong.

 

[jdraw]

It has nothing to do with datasheet view. The reason people don't want users typing values into a parameter query is because they make mistakes.

You could build a form as in the link I gave. That form could include one or 2 calendar controls to let user pick a date right off a calendar. The less typing, the less chance of error.

You can build what you're asking, but you have several conditions, and it seems that's the real issue. I don't think it's a limitation in Access.

Layout all of the conditions and build the query, but make sure you have your conditions defined.
What version of Access are you using?

 

[Brianwarnock]

What you are saking is simple for
most situations
field=parameter or parameter is null

and far better done via a form with the query then run via a command button.

It is the date thing that causes the difficulty or complexity. but again can be done as
(Result Date Between [Start Date:] And [End Date:] or [Start Date] is null)

This link shows you how to do it in the design grid if you prefer that

Brian

 

[jdraw]

Here's a recent Parameter query that was given in response to a request. You may get the general syntax/approach info from this:

PARAMETERS EnterMin Currency, EnterMax Currency;
SELECT tblOwl.id, tblOwl.Person, tblOwl.Previous, tblOwl.Current, tblOwl.Paid, [Previous]+[Current]-[Paid] AS BalanceOwing
FROM tblOwl
WHERE (((IIf(IsNull([Entermin]) And IsNull([Entermax]),([Previous]+[Current]-[Paid]),([Previous]+[Current]-[Paid]) Between [EnterMin] And [EnterMax]))<>False));

I'd still recommend a form, but if you're heading for a report you might want to look at this tutorial, again from Martin Green
http://www.fontstuff.com/access/acctut19.htm

Good luck with whatever you decide.

 

[thewrightdesign]

I'm using Access 2003. There will only ever be two users, myself and my boss, using this so we are not concerned about typing errors and both of us prefer to type as opposed to mouse clicking multiple things. Most of what I do is geared toward only a couple of users, we don't have multiple people getting into our databases so I don't really have the concerns those building databases used by many do. Maybe that's faulty logic on my part but I have to work with what my boss finds most comfortable. That is one reason we try to stay away from objects like calendars to select the date. If possible, I'd like to type in date rather than use the calendars, just goes so much quicker for both of us. If that can't be done then it can't be done.

I'm not a programmer by any means, learning as I go for the past year on doing these databases. Coding is not at all my strong point, although I can usually get it right when someone helps walk me through it. I know it didn't have to do with datasheet view, I was merely explaining we weren't doing a form for this one... although now I probably will build one in order to do this search rather than relying just on the parameter boxes to pop up from the query since that wasn't working.

I'm all for trying whatever works here, I'm still curious why the query builder in Access isn't taking that third parameter though, I'm sure it's because I'm not putting something in correctly just not sure what.

 

[jdraw]

Just looked at your query again -- words like First, Last are reserved words in Access.

The general advice is to NOT use spaces or special characters in field or object names.

see http://allenbrowne.com/AppIssueBadWord.html


Another look:

You have 2 tables in your query

Table (which is a poor name choice) and
[Dead Records] (which includes a space)

You'll have to identify how these tables are related in your query.

 

[thewrightdesign]

I'm aware of that it's something I always change, I should have done that here just was focused on the other issue. Those aren't really critical to the question at hand so wasn't concerned with them just now. Thanks for the reminder though.

 

[thewrightdesign]

What you are saking is simple for
most situations
field=parameter or parameter is null

and far better done via a form with the query then run via a command button.

It is the date thing that causes the difficulty or complexity. but again can be done as
(Result Date Between [Start Date:] And [End Date:] or [Start Date] is null)

This link shows you how to do it in the design grid if you prefer that

Brian

Thanks Brian,

I looked at that link, and it's one I've used before when running into query issues. I have tried that and it's still not working in the design grid. As Jon states there, setting it up like that makes Access split the Is Nulls off into their own column and on my bigger database that led to a stack overflow everytime I ran the query. Since this one will eventually contain thousands of records, I want to make sure that doesn't happen this time as well.

I've also tried doing the Like statement on one criteria row and the Is Null as an OR on the row directly under it, but as he also says here Like won't return a null even when the null is set up as an OR. I'm really at wit's end here trying to make this work since we need to be able to leave a criteria field blank sometimes and still have the query work.

 

[jdraw]

Can you show us the full query?

 

[thewrightdesign]

I showed you two in the first message, what else is needed? I can show you every one I've tried so far, but there are about 12.

Here are a few more I've tried that do not work:

SELECT DeadRecords.[DOC #], DeadRecords.Lastname, DeadRecords.Firstname, DeadRecords.Institution, DeadRecords.ResultDate, DeadRecords.Pass, DeadRecords.Score, DeadRecords.LAW, DeadRecords.SS, DeadRecords.SC, DeadRecords.LAR, DeadRecords.MAT, DeadRecords.R, DeadRecords.M, DeadRecords.L, DeadRecords.PLAW, DeadRecords.PSS, DeadRecords.PSC, DeadRecords.PLAR, DeadRecords.PMAT, DeadRecords.PTOTAL
FROM DeadRecords
WHERE (((DeadRecords.Institution)=[Instutition:])) OR (((DeadRecords.ResultDate) Between [Start Date:] And [End Date:])) OR (((DeadRecords.Pass)=[Yes or No:]));

SELECT DeadRecords.[DOC #], DeadRecords.Lastname, DeadRecords.Firstname, DeadRecords.Institution, DeadRecords.ResultDate, DeadRecords.Pass, DeadRecords.Score, DeadRecords.LAW, DeadRecords.SS, DeadRecords.SC, DeadRecords.LAR, DeadRecords.MAT, DeadRecords.R, DeadRecords.M, DeadRecords.L, DeadRecords.PLAW, DeadRecords.PSS, DeadRecords.PSC, DeadRecords.PLAR, DeadRecords.PMAT, DeadRecords.PTOTAL
FROM DeadRecords
WHERE (((DeadRecords.Institution)=[Instutition:])) OR (((DeadRecords.ResultDate) Between [Start Date:] And [End Date:])) OR (((DeadRecords.Pass)=[Yes or No]));

SELECT DeadRecords.[DOC #], DeadRecords.Lastname, DeadRecords.Firstname, DeadRecords.Institution, DeadRecords.ResultDate, DeadRecords.Pass, DeadRecords.Score, DeadRecords.LAW, DeadRecords.SS, DeadRecords.SC, DeadRecords.LAR, DeadRecords.MAT, DeadRecords.R, DeadRecords.M, DeadRecords.L, DeadRecords.PLAW, DeadRecords.PSS, DeadRecords.PSC, DeadRecords.PLAR, DeadRecords.PMAT, DeadRecords.PTOTAL
FROM DeadRecords
WHERE (((DeadRecords.Institution) Like "*" & [Search Institution] & "*" & "") AND ((DeadRecords.ResultDate) Between [Start Date:] And [End Date:]) AND ((DeadRecords.Pass) Like [Yes or No])) OR (((DeadRecords.Institution) Is Null) AND ((DeadRecords.ResultDate) Is Null) AND ((DeadRecords.Pass) Is Null));

This last code works... can enter or not on all three fields (2 for dates) but doesn't have pass/fail added in:

SELECT DeadRecords.[DOC #], DeadRecords.Lastname, DeadRecords.Firstname, DeadRecords.Institution, DeadRecords.ResultDate, DeadRecords.Pass, DeadRecords.Score, DeadRecords.LAW, DeadRecords.SS, DeadRecords.SC, DeadRecords.LAR, DeadRecords.MAT, DeadRecords.R, DeadRecords.M, DeadRecords.L, DeadRecords.PLAW, DeadRecords.PSS, DeadRecords.PSC, DeadRecords.PLAR, DeadRecords.PMAT, DeadRecords.PTOTAL
FROM DeadRecords
WHERE (((DeadRecords.Institution)=[Instutition:])) OR (((DeadRecords.ResultDate) Between [Start Date:] And [End Date:]));

 

[jdraw]

In this query

SELECT DeadRecords.[DOC #], DeadRecords.Lastname, DeadRecords.Firstname, DeadRecords.Institution, DeadRecords.ResultDate, DeadRecords.Pass, DeadRecords.Score, DeadRecords.LAW, DeadRecords.SS, DeadRecords.SC, DeadRecords.LAR, DeadRecords.MAT, DeadRecords.R, DeadRecords.M, DeadRecords.L, DeadRecords.PLAW, DeadRecords.PSS, DeadRecords.PSC, DeadRecords.PLAR, DeadRecords.PMAT, DeadRecords.PTOTAL
FROM DeadRecords
WHERE (((DeadRecords.Institution)=[Instutition:])) OR (((DeadRecords.ResultDate) Between [Start Date:] And [End Date:])) OR (((DeadRecords.Pass)=[Yes or No:]));

what values can be in DeadRecords.Pass?

This seems to be an error

((DeadRecords.Pass)=[Yes or No:])

If pass is a boolean datatype, and it can be either Yes or No, then you really don't care what value it has, so you could drop it from your query... I think.

 

[thewrightdesign]

Hm. That might be my problem. We want to be able to answer either yes or no, it's a pass/fail checkbox so values are 0,-1. -1 = yes or true (I think the table has true) and 0 = no or false. When I do a query using just that field... [Yes or No] works to filter it properly according to what you type into the parameter input box when it comes up. So I don't see why it doesn't work when added in that same way to the query design grid with the other two criteria filled out.

 

[jdraw]

You could try
(
(DeadRecords.Pass)=Yes or
(DeadRecords.Pass)=No
)

 

[thewrightdesign]

You could try
(
(DeadRecords.Pass)=Yes or
(DeadRecords.Pass)=No
)

So the line of code would look like this:?

WHERE (((DeadRecords.Institution)=[Instutition:])) OR (((DeadRecords.ResultDate) Between [Start Date:] And [End Date:])) OR (((DeadRecords.Pass)=Yes OR (DeadRecords.Pass)=NO));

 

[thewrightdesign]

That just did what leaving the pass/fail field out entirely did... it returned all records whether passed or failed and I need to be able to return passes or fails in that search.

 

[jdraw]

That's what I was asking in previous post. If you can only have Yes or No in that field, and you're asking for either value, then you don't have to use it in the criteria because you'll get all the records.

This

WHERE (((DeadRecords.Institution)=[Instutition:])) OR (((DeadRecords.ResultDate) Between [Start Date:] And [End Date:])) OR (((DeadRecords.Pass)=Yes OR (DeadRecords.Pass)=NO));

will give those records where the Institution matches the parameter, or those records where the resultDate is between your Start and End, and it says you don't care if it's Pass or Fail.

If you want the Passes only then you'd need

WHERE (((DeadRecords.Institution)=[Instutition:])) OR (((DeadRecords.ResultDate) Between [Start Date:] And [End Date:])) OR ((DeadRecords.Pass)=Yes );

NOTE: This does not give you

records for InstitutionX where the resultDate is Between your Start and End and Pass = Yes

You would need to change the OR to AND

 

[vbaInet]

Like Brian mentioned -->

and far better done via a formwith the query then run via a command button.

It is the date thing that causes the difficulty or complexity.

You really should be considering using a form for input instead.

In any case, in the following thread, post #14 explains the idea and post #24 contains the actual db. You can always move the DMin() and DMax() functions directly in the query and then use the Is Null code example Brian gave for other non date fields.

http://www.access-programmers.co.uk/forums/showthread.php?t=216127&page=2

Plus, if you want a combination of results, your OR should be AND and you need correct parentheses too.

 

[thewrightdesign]

That's what I was asking in previous post. If you can only have Yes or No in that field, and you're asking for either value, then you don't have to use it in the criteria because you'll get all the records.

This


will give those records where the Institution matches the parameter, and those records where the resultDate is between your Start and End, and it says you don't care if it's Pass or Fail.

If you want the Passes only then you'd need

Right, but what we need is to be able to enter 0 or -1 into the parameter input box when it comes up. That's how the other two criteria are set up and how they work, and how this one works when I have it in a query on it's own. If I do just the pass as you suggest, then I have to write a whole other query to get the fails when I should be able to just enter 0 or -1 and it filters to return the results I want each time.