Generating a Filtered Report

[brsawvel]

sending it now.. taking some time to attach.

 

[brsawvel]

it is sent

 

[pbaldy]

Dang, had a response typed up and something went haywire as I posted. Bottom line, there is no code behind the report button or the exit button in the 2003 version. As to the code, you're still not getting the single quotes correct. Text values need the single quotes, but numeric and Yes/No fields do not. Thus:

strWhere = strWhere & "([BSP] = " & [Forms]![FrmIncident]![BSP] & ") And "

because BSP is a Yes/No field.

 

[brsawvel]

I figured out what was wrong with the report button not working when transferring from 07 to 03. If a form has "No" for auto center in properties, it doesn't open in 03. I changed it and the buttons work now. Must be a flaw in the program.

I'm going to try to look through the code again and find all the mistakes.

Thanks!

 

[brsawvel]

Mister, You are a lifesaver!!! Thank you for the help. I guess I need to look more closely. (of course I've been at this for approximately 12 hours today though).

 

[brsawvel]

Oh. Sorry. After I added the final yes/no fields, the error "filter canceled" reared its ugly head again. I counted approximately yes/no 170 fields in total.

 

[pbaldy]

Add

Debug.Print Len(strWhere)

right before the OpenReport line. That should print the length of the wherecondition to the Immediate window. If you're close to the limit, you can do little things like getting rid of the parentheses around each test, plus the brackets around any field name that doesn't contain spaces or symbols (neither of which is a good idea by the way). In other words,

([Classification] = 'Confidential')

is fine as

Classification = 'Confidential'

 

[brsawvel]

I tried the debug.print but no window appeared. I even removed the open report and replaced it with the debug.print and received nothing.

with the second part, are you saying to replace...

If Not IsNull([Forms]![frmIncident]![Breed]) Then
    strWhere = strWhere & "([Breed] = '" & [Forms]![frmIncident]![Breed] & "') And "
  End If

with....

If Not IsNull([Forms]![frmIncident]![Breed]) Then
    strWhere = strWhere & "Breed = '" & [Forms]![frmIncident]![Breed] & "' And "
  End If

 

[pbaldy]

It won't just appear, you would have to look in the VBA editor at the Immediate window (Ctl-G if it isn't showing already).

Yup. You'll gain 170 * 2 = 340 characters for the parentheses, somewhat less for the brackets since you have to leave them on field names containing a space or symbol.

 

[brsawvel]

It reads 3086. Is that too much to make a difference?

 

[pbaldy]

I think so, yes. Let's go a different way, and be advised I've never tried it. I've never had a wherecondition that big (and I'm still not comfortable with the table design).

I don't have the db in front of me, and only have 2000 here anyway, so we'll have to feel our way through this. Move the

Dim strWhere As String

to a standard module so we can use it from anywhere. Take the wherecondition argument off your OpenReport line so it just opens the report in preview mode. In the open event of the report, add this code, changing the table name since I can't remember it:

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

and see if that works.

 

[brsawvel]

I received a:

Syntax Error in WHERE clause.

and the next box says:

Action Failed
Macro Name:
FrmIncident : btnReport : OnClick : Embedded Macro
Condition:
True
Action Name:
OpenReport
Arguments:
RptIncident, 5, , , Normal

 

[brsawvel]

I had the command to open the report as a macro. I changed it to a vb code and received this error on "docmd.openreport" -

Run Time Error 2497 - The action or method requires a report name argument

 

[pbaldy]

You'd just want to drop the wherecondition part, so it would look like:

DoCmd.OpenReport "ReportName", acViewPreview

 

[brsawvel]

without quotes around the rptIncident, I still receive the same debug error

with quotes, I get:

Run Time error 3145
Syntax error in where clause

 

[brsawvel]

I don't have a where clause in the vb code. It's in its own module.

 

[pbaldy]

You need the quotes. Add this in the report open code after you've set the recordsource and post the result.

Debug.Print Me.Recordsource

 

[brsawvel]

I'm a retard. I got this debug.print report -

SELECT * FROM TableName WHERE

I forgot to change TableName.

I changed it and got this from the debug.print

SELECT * FROM TblReport WHERE

 

[pbaldy]

It appears it's not picking up the strWhere from the form procedure. Did you make that a public variable in a standard module?

 

[brsawvel]

The module appears as this:

Option Compare Database
Dim strWhere As String

is that what you were asking?