Multiple parameter query and empty parameter

[thewrightdesign]

I spoke too soon LOL. Have put the code into the real database and it's not working like it did in the test database. not sure why, it's done the same. Here is the code that is now not working:

SELECT GEDdata.[DOC#], GEDdata.Lastname, GEDdata.Firstname, GEDdata.Institution, GEDdata.ResultDate, GEDdata.PASS, GEDdata.Score, GEDdata.LAW, GEDdata.SS, GEDdata.SC, GEDdata.LAR, GEDdata.MAT, GEDdata.R, GEDdata.M, GEDdata.L, GEDdata.PLAW, GEDdata.PSS, GEDdata.PSC, GEDdata.PLAR, GEDdata.PMAT, GEDdata.PTOTAL, [Pass:] AS Expr1
FROM GEDdata
WHERE (((GEDdata.Institution)=[Institution:]) AND ((GEDdata.ResultDate) Between Nz([Start:],DMin("ResultDate","GEDdata")) And Nz([End:],DMax("ResultDate","GEDdata"))) AND ((GEDdata.PASS)=[Pass:])) OR (((GEDdata.ResultDate) Between Nz([Start:],DMin("ResultDate","GEDdata")) And Nz([End:],DMax("ResultDate","GEDdata"))) AND (([Pass:]) Is Null) AND (([Institution:]) Is Null));

If I leave anything blank, it will pop up another parameter box to ask for it again rather than seeing it as null and allowing it to be so.

 

[vbaInet]

Show me the SQL of the one that was working.

 

[thewrightdesign]

SELECT testdata.ID, testdata.LastName, testdata.FirstName, testdata.Institution, testdata.ResultDate, testdata.PASS, testdata.Score, testdata.LAW, testdata.SS, testdata.SC, testdata.LAR, testdata.MAT, [Pass:] AS Expr1
FROM testdata
WHERE (((testdata.Institution)=[Institution:]) AND ((testdata.ResultDate) Between Nz([Start:],DMin("ResultDate","testdata")) And Nz([End:],DMax("ResultDate","testdata"))) AND ((testdata.PASS)=[Pass:])) OR (((testdata.ResultDate) Between Nz([Start:],DMin("ResultDate","testdata")) And Nz([End:],DMax("ResultDate","testdata"))) AND (([Pass:]) Is Null) AND (([Institution:]) Is Null));


This works the best of any that have been tried, but even it will not let me leave pass blank and enter only institution leaving dates blank and return all records from that institution

 

[thewrightdesign]

I just noticed when I run this one, the resulting datasheet has two extra columns with the Pass and Institutions as expressions in there. Access keeps adding that "As Expression" and won't let me take it out, but in the code you provided it wasn't there. How do I fix that?

 

[vbaInet]

It's all over the place. Here's what it should look like:

WHERE (
       (Institution = [Institution:] OR [Institution:] Is Null) AND 
       (PASS = [Pass:] OR [Pass:] Is Null) AND 
       (ResultDate BETWEEN Nz([Start:], DMin("ResultDate","TableName")) AND Nz([End:], DMax("ResultDate","TableName")))
      )

Note how they are grouped.

 

[thewrightdesign]

That makes a LOT more sense! The code I posted just came from the sql view of the design grid for the query. The way you have it written clearly puts it all together as it should run. I just ran this several times with every combination I could think of and it works exactly right!! I can't tell you how excited I am to see this working!

Is it possible to make this work off of a form? Where I have unbound text boxes for the values rather than having pop up query parameter boxes? Does that make sense? I had seen a QBF done this way and was working on building one, and I know it means coding a lot on the command button to make it run. Will this query work for that and me be able to have those input boxes for start and end dates?

 

[vbaInet]

It would even be much better running it off a form. All you need to do is replace the criteria with a full reference to the control. Here's an example using Pass

(PASS = [Forms]![[COLOR=Red]FormName[/COLOR]]![[COLOR=Red]TextboxName[/COLOR]] OR [Forms]![[COLOR=Red]FormName[/COLOR]]![[COLOR=Red]TextboxName[/COLOR]] Is Null)

You only change the red bits.

For the DMin and DMax parts, put the entire DMin() and DMax() code lines in separate textboxes and call them txtAltStart and txtAltEnd respective. Then you can use the reference to those textboxes in the query.

Remember that the code needs to be open before the query is run and it needs to remain open for the entire session the query is run.

If you have a button to confirm the input, you will need to put Me.Requery in the button's Click event.

 

[thewrightdesign]

Just making sure I understand on the DMin/DMax parts... make a text box for start and one for end, then in the properties of each text do the full DMin/DMax code in the control source property? And do I need to add anything other than:

Private Sub cmdOk_Click()
DoCmd.OpenQuery "Query1", acViewNormal, acEdit
DoCmd.Close acForm, "Form1"
End Sub

to the OK command button?

 

[vbaInet]

Just making sure I understand on the DMin/DMax parts... make a text box for start and one for end, then in the properties of each text do the full DMin/DMax code in the control source property?

Bingo!

And do I need to add anything other than:

Private Sub cmdOk_Click()
DoCmd.OpenQuery "Query1", acViewNormal, acEdit
DoCmd.Close acForm, "Form1"
End Sub

to the OK command button?

Keep the form open. So take out the close line.

If you're going to keep closing and opening the query then you don't need what I mentioned below.

If you have a button to confirm the input, you will need to put Me.Requery in the button's Click event.

 

[thewrightdesign]

Bingo!

Keep the form open. So take out the close line.

If you're going to keep closing and opening the query then you don't need what I mentioned below.

Ok so if I take out the close, which is fine, then in its place I'd have Me.Requery?

 

[vbaInet]

When the query is re-opened the criteria is run. But if you keep the query open and change the filters on the form, you will need a way of telling the query to fetch the records again based on new criteria. This is what the Me.Requery code does.

 

[thewrightdesign]

WHERE ((Institution= [Forms]![Form1]![cboInstitution] OR [Forms]![Form1]![cboInstitution] Is Null) And (PASS=[Forms]![Form1]![cboPass] OR [Forms]![Form1]![cboPass] Is Null) And (ResultDate Between Nz([Start:],DMin("ResultDate","DeadRecords")) And Nz([End:],DMax("ResultDate","DeadRecords"))));

Here is what I'm going with for the form... does the ResultDate code stay the same on this as well as having it in the control source for the txtAltStart txtAltEnd buttons?

 

[vbaInet]

Ok, I think I didn't fully explain this bit.

You will have four textboxes for the date field, namely txtStart, txtEnd, txtAltStart and txtAltEnd, where Alt represents "Alternative". A full reference to txtAltStart and txtAltEnd will replace DMin(...) and DMax(...) respectively and you can set their Visible property to No.

txtStart and txtEnd will replace [Start:] and [End:] respectively. The Nz() remains.

 

[thewrightdesign]

Like this?

WHERE ((Institution= [Forms]![Form1]![cboInstitution] OR [Forms]![Form1]![cboInstitution] Is Null) And (PASS=[Forms]![Form1]![cboPass] OR [Forms]![Form1]![cboPass] Is Null) And (ResultDate Between Nz([Forms]![Form1]![txtStart], [Forms]![Form1]![txtAltStart])) And Nz([Forms]![Form1]![txtEnd], [Forms]![Form1]![txtAltEnd]))));

 

[vbaInet]

Lovely job!

 

[thewrightdesign]

LOL looked great but sooo didn't work Must have set up something wrong on the form. And when I try to just run the query I get the following error...

Ok I get the error when running the form... since it's based off the form of course it won't run on it's own... brain is turning to tapioca I think

 

[vbaInet]

If you encounter a syntax error next time copy and paste it into Notepad and lay it out like this:

WHERE (
       (Institution = [Forms]![Form1]![cboInstitution] OR [Forms]![Form1]![cboInstitution] Is Null) And 
       (PASS = [Forms]![Form1]![cboPass] OR [Forms]![Form1]![cboPass] Is Null) And 
       (ResultDate Between Nz([Forms]![Form1]![txtStart], [Forms]![Form1]![txtAltStart]) And Nz([Forms]![Form1]![txtEnd], [Forms]![Form1]![txtAltEnd]))
      );

That's the fixed version by the way.

 

[thewrightdesign]

Thanks again. It all works except that I can't leave date blank and return results.

 

[vbaInet]

What exactly did you put in the Control Source of txtAltStart and txtAltEnd?

 

[thewrightdesign]

(ResultDate Between Nz([Start:],DMin("ResultDate","DeadRecords")) And Nz([End:],DMax("ResultDate","DeadRecords")

that is exactly what is in each Alt and it's in the control source. If I change it out to full references like in the query code will that fix it?