Generating a Filtered Report

[pbaldy]

Yes, but is that in the form module or a standard module?

 

[brsawvel]

I believe it's in a standard module. I created it in 2007 by going to create>>module rather than entering a module into the form's script.

 

[pbaldy]

Can you email me an updated version of the program?

 

[brsawvel]

sure. just one second.

 

[pbaldy]

I'm sorry, I probably wasn't clear enough. You need to leave all the code behind the form button that built strWhere. The only changes to that code were to move the declaration of strWhere out to a standard module and drop the last part of the OpenReport line that included strWhere. Do you still have all that working code in a backup? Basically, we're still going to build strWhere, we're just going to use it differently.

 

[brsawvel]

sorry it took a bit to respond. I've been watching the elections. I have the original still. I just created a 3rd back up and edited the back up.

Should I leave everything except:

Dim strWhere As String

and remove:

strWhere = Left$(strWhere, lngLen)

or remove something else?

 

[pbaldy]

Take this line out and move it to a standard module:

Dim strWhere As String

And change this line:

DoCmd.OpenReport stDocName, acPreview, , strWhere

to this:

DoCmd.OpenReport stDocName, acPreview

Leave everything else as is in that procedure. Then add this to the report open event:

Me.Recordsource = "SELECT * FROM TblReport WHERE " & strWhere

 

[brsawvel]

I received a "Syntax Error in WHERE Clause". The debug says -

SELECT * FROM TblReport WHERE

 

[pbaldy]

Can you send me an updated version in it that has the code to generate strWhere? The debug indicates that either that code isn't running or the string isn't getting passed correctly to the report.

 

[brsawvel]

I sent it.

 

[pbaldy]

Okay, if I change the declaration to

Public strWhere As String

which was my mistake and change these to be handled as text:

MGRS EW
MGRS NS

I can get it to work. However, we run into a big problem. According to Help, the maximum:

Number of AND operators in a WHERE or HAVING clause 99

You are way over that limit with all your fields. I have to run to a meeting, but some thought needs to be given to the design.

 

[brsawvel]

Is there a way to tell the code to ignore act as if a section of the code isn't even there if the yes/no box is unchecked and move on to the next section?

I tried doing it once as this --

IF isnull THEN
GOTO a_btnreport_click
ELSE
IF isnotnull THEN
store Where clause for report

but it still came back as being too long.

Otherwise I might have to redesign the thing drastically I guess.

 

[pbaldy]

Well, you're already doing that with:

If Not IsNull([Forms]![FrmIncident]![Classification]) Then

but part of the problem is you're basing on a bound form, so the Yes/no fields are all either true or false, but never Null, so they're all included. I won't pretend to understand the overall picture here, but perhaps changing the test on all the Yes/No fields to

If Me.ControlName = True Then

that way strWhere will only get updated if the checkbox is checked. In other words, the field would only be included in the criteria if it was true. Would that fit what you wanted? It would not if you might want to look for fields that weren't checked.

 

[brsawvel]

if that does work, that would be perfect. I wouldn't be looking for anything not checked.

 

[pbaldy]

Try that plus the 2 changes I mentioned in post 51 and see where we're at.

 

[brsawvel]

I believe that did it! I'm going to run a series of tests to verify, and I'll let you know.

 

[brsawvel]

I don't see any errors as of yet. I really appreciate you helping me on this. I'll be sure to give you due credit if this goes anywhere. (Now, of course, I have one last issue. I have a Keyword thread if you are interested).

 

[pbaldy]

No problem my friend; let me know how it goes.