Generating a Filtered Report

[brsawvel]

Hello Guys,

I attempting to develop a code that generates a report via a form with text, numeric, and yes/no fields where the user can enter desired parameters in the form and click the "btnReport" button. It works for the text fields, but I don't know how to alter the code to work for numeric, and yes/no fields. Can someone help?

Here is what I have for the text fields

Private Sub btnReport_Click()
On Error GoTo Err_btnReport_Click

'Button filters out records depending on the criteria entered in the search fields
Dim strWhere As String
Dim lngLen As Long
Dim stDocName As String
 
'Stores the search criteria and enters it into the Where Condition
If Not IsNull([Forms]![frmIncident]![tbo1]) Then
strWhere = "([tbo1] = [Forms]![frmIncident]![tbo1]) And "
End If
 
'This is used to cut off the " AND " at the end of the Where Condition
lngLen = Len(strWhere) - 5
 
'If there was no information included in the criteria fields an error pops up, otherwise the search processes the Where Condition
If lngLen <= 0 Then
MsgBox "Please enter search criteria!"
Else
strWhere = Left$(strWhere, lngLen)
stDocName = "rptReport"
DoCmd.OpenReport stDocName, acPreview, , strWhere
End If
 
'this prevents the form from developing a new record
Form.Undo
 
Exit_btnReport_Click:
    Exit Sub
 
Err_btnReport_Click:
    MsgBox Err.Description
    Resume Exit_btnReport_Click

End Sub

I'm pretty sure that the only part of this code that needs to be edited is the following:

If Not IsNull([Forms]![frmIncident]![tbo1]) Then
strWhere = "([tbo1] = [Forms]![frmIncident]![tbo1]) And "

But how do I edit it to deal with numeric fields and yes/no fields?

 

[pbaldy]

Does this help?

http://www.baldyweb.com/wherecondition.htm

 

[brsawvel]

Apparantly the code works for all types of fields (I should have checked first); however, my new problem is this...

I receive an error that says "The filter operation was cancelled, The filter would be too long".

I figure it's because I'm trying to create a where condition for each field (approximately 150), there are only something like 2000 characters allowed in this operation.

If Not IsNull([Forms]![frmIncident]![tbo1]) Then
strWhere = "([tbo1] = [Forms]![frmIncident]![tbo1]) And "

I read this article, but I'm not sure how to implement the work around -

http://support.microsoft.com/kb/207615

Anyone know a work around and how to implement it in my case?

 

[pbaldy]

That's a lot of fields; what do they represent?

One way around the limitation is

Field In(1, 2, 3)

rather than

Field = 1 OR Field = 2 OR Field = 3.

You might also resolve the form references as done in the link I posted earlier, as that would replace

[tbo1] = [Forms]![frmIncident]![tbo1]

with

[tbo1] = 123

 

[brsawvel]

I'm confused by what you mean by field in (1,2,3)

attached is an example of what I'm trying to do if it helps...

(although it works in this one because there aren't as many yes/no tabs as my actual db).

 

[pbaldy]

I only have A2k here, but I can look at the sample tonight. The "In" clause would only help if you have more than one criteria value per field. It sounds like you just have a lot of fields with one criteria on each. Try my second suggestion, as that should shorten the string considerably.

 

[brsawvel]

I'm sorry, but I'm somewhat new to this still and not sure how to implement the code in the link you provided. I get the basic of the page is saying, but I'm not sure how the code would look as a whole.

 

[pbaldy]

strWhere = "([tbo1] = " & [Forms]![frmIncident]![tbo1] & ") And "

 

[brsawvel]

Thanks for the string Paul. I assume what you recommended was that I replaced all the....

strWhere = "([tbo1] = [Forms]![frmIncident]![tbo1]) And "

with

strWhere = "([tbo1] = " & [Forms]![frmIncident]![tbo1] & ") And "

I did this and reattempted the filter and received a "enter parameter" for each box that I entered a filter request in. Any ideas?

I really appreciate the help.

 

[brsawvel]

Actually,

Sorry, I saw a mistake in my code. I actually had this...

strWhere = "([tbo1] = " & [Forms]![frmIncident]![tbo1] & ") And " & strWhere

I took of all the "& strWhere" and now when I click the report button, all records appear on the report as if I never filtered.

 

[pbaldy]

Well, you would have to add to the string, so this appears to work:

strWhere = strWhere & "([Number] = " & [Forms]![frmIncident]![Number] & ") And "

Also, you've bound this search form to the table, so as you change search criteria you're actually changing the displayed record. I doubt that was your intent.

 

[pbaldy]

I just noticed you got around the my last comment with the Undo. I wouldn't use a bound form myself, but your call. Also, as noted in my link, for text fields you need the single quote around the value:

strWhere = strWhere & "([Breed] = '" & [Forms]![frmIncident]![Breed] & "') And "

 

[brsawvel]

I'll try the first part.

I thought of the second part and set the form -when filtering- to open in "new record" and added "form.undo" near the end of the thread so that the record is not changed when filtering.

 

[brsawvel]

ah. I didn't see the single quote. Let me try that.

 

[brsawvel]

I tried this

strWhere = strWhere & "([Classification] = '" & [Forms]![frmIncident]![Classification] & "') And "

and got a "data type mismatch in criteria expression.

 

[pbaldy]

The sample does not include that field. If it is numeric, you don't want the single quotes.

 

[brsawvel]

I removed the single quotes from the numeric fields. I still receive the same error as last. Is there something I need to do for the yes/no fields as well?

 

[pbaldy]

Shouldn't be. My brief test in your sample worked as expected:

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

  'Stores the search criteria and enters it into the Where Condition
  If Not IsNull([Forms]![frmIncident]![Color]) Then
    strWhere = strWhere & "([Color] = '" & [Forms]![frmIncident]![Color] & "') And "
  End If

  'Stores the search criteria and enters it into the Where Condition
  If Not IsNull([Forms]![frmIncident]![Number]) Then
    strWhere = strWhere & "([Number] = " & [Forms]![frmIncident]![Number] & ") And "
  End If

  'Stores the search criteria and enters it into the Where Condition
  If Not IsNull([Forms]![frmIncident]![Med a]) Then
    strWhere = strWhere & "([Med a] = " & [Forms]![frmIncident]![Med a] & ") And "
  End If

  'Stores the search criteria and enters it into the Where Condition
  If Not IsNull([Forms]![frmIncident]![Med b]) Then
    strWhere = strWhere & "([Med b] = " & [Forms]![frmIncident]![Med b] & ") And "
  End If

 

[brsawvel]

Do you mind looking at my actual db and determining where I'm going wrong? I'd like to send it by personal message, though, and not post it for all to see.

 

[pbaldy]

You can email it to

addy removed to prevent spam