Multiple parameter query and empty parameter

[thewrightdesign]

Like Brian mentioned -->
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.

Thanks I'll look into that as well, and I think after this discussion we've realized a form is what we need as long as I can have the results filter directly into a report.

In my first post I believe I posted the code that works with two criteria and are AND not OR statements. These are several examples (confusing I know) that I've tried. I'm building this query in the design grid, getting the sql code from the sql view after so Access is setting the parentheses in what you see.

I want to change the code of the yes/no criteria so it works as well as the other two are working, returning either passes or fails, or being left empty to return all passes and fails after being filtered by instutition and date range. Ideally, if all input parameter boxes are left blank all records should be returned.

 

[vbaInet]

I want to change the code of the yes/no criteria so it works as well as the other two are working, returning either passes or fails, or being left empty to return all passes and fails after being filtered by instutition and date range. Ideally, if all input parameter boxes are left blank all records should be returned.

Your BETWEEN code isn't working as you you think.

 

[thewrightdesign]

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

Right, and the code that I said works from my first post... are AND not OR. So why can't I have the pass/fail working in that same code?

 

[thewrightdesign]

Your BETWEEN code isn't working as you you think.

Ok great... what do I need to change it to to get it to work?

 

[jdraw]

Ok, I think I have misunderstood your [Yes or No]. That is a parameter. So it pops up and you input a 0 or -1. Confused with the naming.

I think your issue now is with the OR.

If you want Passes at InstitutionX where resultdate is between YourStart and YourEnd

You will need to use the AND operator.

Instiution = institutionX And ResultDate Between YourStart And YourEnd AND Pass = -1

 

[vbaInet]

Ok great... what do I need to change it to to get it to work?

All mentioned in my first post and the thread provided.

 

[thewrightdesign]

Ok, I think I have misunderstood your [Yes or No]. That is a parameter. So it pops up and you input a 0 or -1. Confused with the naming.

I think your issue now is with the OR.

If you want Passes at InstitutionX where resultdate is between YourStart and YourEnd

You will need to use the AND operator.

Instiution = institutionX And ResultDate Between YourStart And YourEnd AND Pass = -1

I think I never should have posted the codes that were not working, seems to have confused the issue greatly.

Let's just look at this code then:

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:]));


This code works. I can enter or not enter institution and both dates and return results correctly. What do I need to do to get it to add in the pass/fail and have it work as well?

 

[thewrightdesign]

Ok, I think I have misunderstood your [Yes or No]. That is a parameter. So it pops up and you input a 0 or -1. Confused with the naming.

I think your issue now is with the OR.

If you want Passes at InstitutionX where resultdate is between YourStart and YourEnd

You will need to use the AND operator.

Instiution = institutionX And ResultDate Between YourStart And YourEnd AND Pass = -1

Just looked again and the code with AND... doesn't work. the code with OR works the way I want it to

 

[thewrightdesign]

All mentioned in my first post and the thread provided.

That's what I'm trying now, and the link that had the report in it since filtering into a report is our main goal.

 

[vbaInet]

So let's clarify something. If I enter a value for Institution AND Start and End dates, should the query find those records that match all the criteria entered?

 

[thewrightdesign]

So let's clarify something. If I enter a value for Institution AND Start and End dates, should the query find those records that match all the criteria entered?

Thanks for sticking with me through this and clarifying things

Yes, I want to enter a value for all three of those and find the criteria matching those. I also want to be able to leave out either institution or the dates and still return values. (pass/fail as well)

The last code I posted works to allow me to return results for any specific institution between dates provided. It also allows me to return results from all institutions (leaving that field blank) between dates provided. It also lets me return results from a specific institution regardless of date (leaving both date fields blank)

What I want is for it to do that, and also return results in the same manner for pass/fail.

 

[vbaInet]

Start with your date field first before adding others unless you will get confused.

Because your date field is using a BETWEEN keyword, you will need to implement what I explained in that thread: post #14. Here's some aircode:

BETWEEN Nz([Start:], DMin(...)) AND Nz([Start:], DMax(...))

Once you've done that and it's working as expected you can move on to the other fields and do them one by one. You also mentioned that you would the query filtered by all criteria entered and I mentioned in my first post, the OR part you put is wrong. It should have been AND. But let's get the BETWEEN working first.

 

[thewrightdesign]

Attaching the db with sample data in case anyone has time to look through it and help me figure this out.

vbaInet, I looked through the links you provided and still haven't been able to get anything to return the results I need returned. Clearly I'm not code-literate enough to understand how to apply your suggestions properly.

As a reminder, what I'm wanting to do is return any combination of results for the criteria of Institution, Start/End date, and Pass (yes or no value). Need to be able to enter all three (four considering start date and end date are separate values), only one, only two or none of the vaules and have corresponding results returned. Hope that makes sense.

 

[vbaInet]

vbaInet, I looked through the links you provided and still haven't been able to get anything to return the results I need returned. Clearly I'm not code-literate enough to understand how to apply your suggestions properly.

I didn't see much attempt from your side.

Anyway, see attached.

 

[thewrightdesign]

I didn't see much attempt from your side.

Anyway, see attached.

I didn't post what I tried since I couldn't get it to work, plus I got pulled off to do another project and couldn't get back to this til yesterday.

I have meetings this morning so will take a look at what you uploaded this afternoon, thank you.

 

[vbaInet]

Alrighty, let me know!

 

[thewrightdesign]

Alrighty, let me know!

I tried to search on the one you sent back, the dates work perfectly and I can do any combination of the dates and institution and return what I want to see. Thank you for that. My question now is how do I add in that pass field to further filter results based on the pass/fail status? I'm gathering from reading this thread it has to be in top row of the query so it's an AND statement, and put the Is Null in a separate column and row?

 

[vbaInet]

All on the same row and yes that's makes it an AND condition.

Just use what has been giving as a guide replacing only the field names and leaving everything else as is.

 

[thewrightdesign]

That worked perfectly thank you so much for all your help!! I can now search on all three/four fields in any combination I want and get the correct results. This is going to help me with another database I was having trouble with before I think, can't wait to try it out on that one as well. Thanks so much for your help, you were very patient with me and I appreciate that greatly!

 

[vbaInet]

Happy to help!

Good luck with the rest of your project.