filter help

[awake2424]

Hello,

I am using Access 2003 to filter as report. I have a command button and a combo box on a form that I go into to filter the report (see attached). The command button uses this code:

Private Sub cmdPrint_Click()
Dim str_Filter As String
str_Filter = "[Case Number] = & Case"
DoCmd.OpenReport "unrelated SCT1", acViewNormal, str_Filter
End Sub

to print the selected case (which I set using the combo box drop-down menu)

The entire report is printing and I only need the selected record to print on the report. Any sugesstions? Thank you.

 

[maxmangion]

str_Filter = "[Case Number] = & Case"

should be

str_Filter = "[Case Number] =" & Case

 

[awake2424]

report filter

I tried the change and am still getting the entire report to print.

 

[maxmangion]

what is the datatype of your field? Because if it is "Text" and not "Number" you will need to add the quotes i.e.


str_Filter = "[Case Number] = '" & Me.Case & "'"

 

[awake2424]

report filter

the data type of the field is a number.

 

[maxmangion]

so it should be

str_Filter = "[Case Number] =" & Me.Case

note that in your original code you did not have the "Me."

 

[dkinley]

Also ... you have ...


DoCmd.OpenReport "unrelated SCT1", acViewNormal, str_Filter

If that doesn't work, try ...

DoCmd.OpenReport "unrelated SCT1", acViewNormal, ,str_Filter

-dK

 

[awake2424]

report filter

The code I am using is:

Private Sub cmdPrint_Click()
Dim str_Filter As String
str_Filter = "[Case Number] = & Case"
DoCmd.OpenReport "unrelated SCT1", acViewNormal, , str_Filter
End Sub

I am getting a runtime error 3075: Syntax error (missing operator) in query expression '([Case number] = & Case)'

 

[maxmangion]

Hi,

As I have already explained above that should read

"[Case Number] = " & Me.Case

 

[awake2424]

report filter

I am sorry. Here is my code:

Private Sub cmdPrint_Click()
Dim str_Filter As String
str_Filter = "[Case Number] =" & Me.Case
DoCmd.OpenReport "rptSCT", acViewNormal, , str_Filter
End Sub

I get a Compile error: method or data member not found

The .Case is highlighted

Thank you

 

[maxmangion]

try using a different name rather than Case because case is also a vba Keyword.

Try naming your field something like txtCase

 

[awake2424]

report filter

Code:

Private Sub cmdPrint_Click()
Dim str_Filter As String
str_Filter = "[Casenumber] =" & Me.txtCase
DoCmd.OpenReport "rptSCT", acViewNormal, str_Filter
End Sub

Error: Run-time 438 Object does not support this property or method

 

[maxmangion]

try

Private Sub cmdPrint_Click()
Dim str_Filter As String
str_Filter = "[Casenumber] =" & Me.txtCase
DoCmd.OpenReport "rptSCT", acViewNormal, ,str_Filter
End Sub

Note that a comma has been added near the str_Filter

 

[Alc]

I've seen the lack of a space between the "=" and a value cause problems e.g. Text = "1" works, Text ="1" errors. I don't know if that's what's causing your problem here, but fixing it anyway won't hurt.

If you remove str_Filter, does the report open with all records or still error?

if it works, try adding

Msgbox str_Filter

just before the Open Report line and see what that value is being set to.

 

[awake2424]

report filter

the same error occcurs.

 

[Alc]

Which line of code, if any, gets highlighted when the error occurs?

 

[awake2424]

report filter

This is what I have

Code:

Private Sub cmdPrint_Click()
Dim str_Filter As String
str_Filter = "[Casenumber]= " & Me.txtCase
DoCmd.OpenReport "rptSCT", acViewNormal, , str_Filter
End Sub

Highlighted line:
str_Filter = "[Casenumber]= " & Me.txtCase

Error: Object does not support this property or method

 

[Alc]

So the problem is with [CaseNumber] or Me.txtCase.

Is the field definitely called txtCase?
Is there definitely a value in this field?
How many columns are in txtCase (visible or otherwise)?

 

[awake2424]

report filter

The combo-box in which i select the case # is labeled txtCase

There is 1 column with numerous cases

 

[Alc]

Okay, we need to see if the problem is with the form object itself or where str_Filter is being created.

Try putting

Msgbox txtCase

directly before the problem line of code. This will tell you what is being read as the value of that field. Assuming that's displayed okay, try altering the next line to

str_Filter = "Casenumber = '" & Me.txtCase & "'"

in case the value is being read as a string, not a number