Using Textbox as filter to open report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear All:
I succesfully use multiple combo boxes as filter to open report. But I have
trouble if using txtbox.

I create a command button to triggle filter.

Here is the code for textbox name txtRequest.
If IsNull(Me.txtRequest.Value) Then
strRequest = "Like '*'"
else
strRequest = "='" & Me.txtRequest.Value & "'"
End If

and then

strFilter = "[Requestor] " & strRequest
Requestor is the field in report.
But after report is open, I see no matching record.

Also since I must open the report first while using this form filter. Is
any way I can temporary hide this report (like minimize) and then after
filter applies, then the report can be maximize? Need your suggestion
Anything wrong with it??? Need help in this part
 
How about just

If IsNull(Me.txtRequest.Value) = FalseThen
strFilter = "[Requestor] = '" & Me.txtRequest.Value & "'"
End If
 
Doug thank for your reply.
Still get the same EMPTY report.

I use strFilter to combine all multiple query fields together. Combo boxes
are working fine. But txt still not working.....

strFilter = "[Requestor] " & strRequest

Any other suggestion?


Douglas J. Steele said:
How about just

If IsNull(Me.txtRequest.Value) = FalseThen
strFilter = "[Requestor] = '" & Me.txtRequest.Value & "'"
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Vivi said:
Dear All:
I succesfully use multiple combo boxes as filter to open report. But I
have
trouble if using txtbox.

I create a command button to triggle filter.

Here is the code for textbox name txtRequest.
If IsNull(Me.txtRequest.Value) Then
strRequest = "Like '*'"
else
strRequest = "='" & Me.txtRequest.Value & "'"
End If

and then

strFilter = "[Requestor] " & strRequest
Requestor is the field in report.
But after report is open, I see no matching record.

Also since I must open the report first while using this form filter. Is
any way I can temporary hide this report (like minimize) and then after
filter applies, then the report can be maximize? Need your suggestion
Anything wrong with it??? Need help in this part
 
What's the final result for strFilter that's not working?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Vivi said:
Doug thank for your reply.
Still get the same EMPTY report.

I use strFilter to combine all multiple query fields together. Combo
boxes
are working fine. But txt still not working.....

strFilter = "[Requestor] " & strRequest

Any other suggestion?


Douglas J. Steele said:
How about just

If IsNull(Me.txtRequest.Value) = FalseThen
strFilter = "[Requestor] = '" & Me.txtRequest.Value & "'"
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Vivi said:
Dear All:
I succesfully use multiple combo boxes as filter to open report. But I
have
trouble if using txtbox.

I create a command button to triggle filter.

Here is the code for textbox name txtRequest.
If IsNull(Me.txtRequest.Value) Then
strRequest = "Like '*'"
else
strRequest = "='" & Me.txtRequest.Value & "'"
End If

and then

strFilter = "[Requestor] " & strRequest
Requestor is the field in report.
But after report is open, I see no matching record.

Also since I must open the report first while using this form filter.
Is
any way I can temporary hide this report (like minimize) and then after
filter applies, then the report can be maximize? Need your suggestion
Anything wrong with it??? Need help in this part
 
Dear Doug:

The report can be opened with all blank values.
Then I use combo box as filter to open the same report, it is working......


Douglas J. Steele said:
What's the final result for strFilter that's not working?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Vivi said:
Doug thank for your reply.
Still get the same EMPTY report.

I use strFilter to combine all multiple query fields together. Combo
boxes
are working fine. But txt still not working.....

strFilter = "[Requestor] " & strRequest

Any other suggestion?


Douglas J. Steele said:
How about just

If IsNull(Me.txtRequest.Value) = FalseThen
strFilter = "[Requestor] = '" & Me.txtRequest.Value & "'"
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear All:
I succesfully use multiple combo boxes as filter to open report. But I
have
trouble if using txtbox.

I create a command button to triggle filter.

Here is the code for textbox name txtRequest.
If IsNull(Me.txtRequest.Value) Then
strRequest = "Like '*'"
else
strRequest = "='" & Me.txtRequest.Value & "'"
End If

and then

strFilter = "[Requestor] " & strRequest
Requestor is the field in report.
But after report is open, I see no matching record.

Also since I must open the report first while using this form filter.
Is
any way I can temporary hide this report (like minimize) and then after
filter applies, then the report can be maximize? Need your suggestion
Anything wrong with it??? Need help in this part
 
I'm asking you to print out what strFilter contains when it doesn't work.
(Seeing what it contains when it works might be a good idea as well)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Vivi said:
Dear Doug:

The report can be opened with all blank values.
Then I use combo box as filter to open the same report, it is
working......


Douglas J. Steele said:
What's the final result for strFilter that's not working?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Vivi said:
Doug thank for your reply.
Still get the same EMPTY report.

I use strFilter to combine all multiple query fields together. Combo
boxes
are working fine. But txt still not working.....

strFilter = "[Requestor] " & strRequest

Any other suggestion?


:

How about just

If IsNull(Me.txtRequest.Value) = FalseThen
strFilter = "[Requestor] = '" & Me.txtRequest.Value & "'"
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear All:
I succesfully use multiple combo boxes as filter to open report.
But I
have
trouble if using txtbox.

I create a command button to triggle filter.

Here is the code for textbox name txtRequest.
If IsNull(Me.txtRequest.Value) Then
strRequest = "Like '*'"
else
strRequest = "='" & Me.txtRequest.Value & "'"
End If

and then

strFilter = "[Requestor] " & strRequest
Requestor is the field in report.
But after report is open, I see no matching record.

Also since I must open the report first while using this form
filter.
Is
any way I can temporary hide this report (like minimize) and then
after
filter applies, then the report can be maximize? Need your
suggestion
Anything wrong with it??? Need help in this part
 
Dear Doug:

Here is the code I have:

Private Sub cmdApplyFilter_Click()
Dim strStaff As String
Dim strBU As String
Dim strRequestor As String
Dim strCompany As String
Dim strFilter As String
' four multiple filters
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "By Staff") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Staff field
If IsNull(Me.cbostaff.Value) Then
strStaff = "Like '*'"
Else
strStaff = "='" & Me.cbostaff.Value & "'"
End If
' Build criteria string for BU field
If IsNull(Me.cboBU.Value) Then
strBU = "Like '*'"
Else
strBU = "='" & Me.cboBU.Value & "'"
End If

'Build criteria string for Requestor field
'If Not IsNull(Me.txtRequestor) Then
'strFilter = strFilter & "([Requestor] Like ""*" & Me.txtRequestor &
"*"") AND "
'End If

'Build criteria string for Company field
If IsNull(Me.txtCompany.Value) Then
strCompany = "Like '*'"
Else
strCompany = "='" & Me.txtCompany.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Company Name] " & strCompany
'strFilter = "[Assigned Staff] " & strStaff & " AND [Requested Unit] " &
strBU & " AND [Requestor] " & strRequestor
( I marked it off to test one filter only, when I use "combo box" filters
it works fine.)


' Apply the filter and switch it on
With Reports![By Staff]

.Filter = strFilter
.FilterOn = True
End With
End Sub

Douglas J. Steele said:
I'm asking you to print out what strFilter contains when it doesn't work.
(Seeing what it contains when it works might be a good idea as well)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Vivi said:
Dear Doug:

The report can be opened with all blank values.
Then I use combo box as filter to open the same report, it is
working......


Douglas J. Steele said:
What's the final result for strFilter that's not working?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug thank for your reply.
Still get the same EMPTY report.

I use strFilter to combine all multiple query fields together. Combo
boxes
are working fine. But txt still not working.....

strFilter = "[Requestor] " & strRequest

Any other suggestion?


:

How about just

If IsNull(Me.txtRequest.Value) = FalseThen
strFilter = "[Requestor] = '" & Me.txtRequest.Value & "'"
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear All:
I succesfully use multiple combo boxes as filter to open report.
But I
have
trouble if using txtbox.

I create a command button to triggle filter.

Here is the code for textbox name txtRequest.
If IsNull(Me.txtRequest.Value) Then
strRequest = "Like '*'"
else
strRequest = "='" & Me.txtRequest.Value & "'"
End If

and then

strFilter = "[Requestor] " & strRequest
Requestor is the field in report.
But after report is open, I see no matching record.

Also since I must open the report first while using this form
filter.
Is
any way I can temporary hide this report (like minimize) and then
after
filter applies, then the report can be maximize? Need your
suggestion
Anything wrong with it??? Need help in this part
 
Change

With Reports![By Staff]
.Filter = strFilter
.FilterOn = True
End With

to

Debug.Print "strFilter = " & strFilter
With Reports![By Staff]
.Filter = strFilter
.FilterOn = True
End With

Once the code's executed, go to the Immediate WIndow (Ctrl-G) and let me
know what's been printed there.

Does your code work under certain conditions, but not under others? If so,
post what strFilter is when it works, what it is when it doesn't work, and
what's different about what's selected or filled in on your form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Vivi said:
Dear Doug:

Here is the code I have:

Private Sub cmdApplyFilter_Click()
Dim strStaff As String
Dim strBU As String
Dim strRequestor As String
Dim strCompany As String
Dim strFilter As String
' four multiple filters
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "By Staff") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Staff field
If IsNull(Me.cbostaff.Value) Then
strStaff = "Like '*'"
Else
strStaff = "='" & Me.cbostaff.Value & "'"
End If
' Build criteria string for BU field
If IsNull(Me.cboBU.Value) Then
strBU = "Like '*'"
Else
strBU = "='" & Me.cboBU.Value & "'"
End If

'Build criteria string for Requestor field
'If Not IsNull(Me.txtRequestor) Then
'strFilter = strFilter & "([Requestor] Like ""*" & Me.txtRequestor &
"*"") AND "
'End If

'Build criteria string for Company field
If IsNull(Me.txtCompany.Value) Then
strCompany = "Like '*'"
Else
strCompany = "='" & Me.txtCompany.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Company Name] " & strCompany
'strFilter = "[Assigned Staff] " & strStaff & " AND [Requested Unit] "
&
strBU & " AND [Requestor] " & strRequestor
( I marked it off to test one filter only, when I use "combo box" filters
it works fine.)


' Apply the filter and switch it on
With Reports![By Staff]

.Filter = strFilter
.FilterOn = True
End With
End Sub

Douglas J. Steele said:
I'm asking you to print out what strFilter contains when it doesn't work.
(Seeing what it contains when it works might be a good idea as well)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Vivi said:
Dear Doug:

The report can be opened with all blank values.
Then I use combo box as filter to open the same report, it is
working......


:

What's the final result for strFilter that's not working?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug thank for your reply.
Still get the same EMPTY report.

I use strFilter to combine all multiple query fields together.
Combo
boxes
are working fine. But txt still not working.....

strFilter = "[Requestor] " & strRequest

Any other suggestion?


:

How about just

If IsNull(Me.txtRequest.Value) = FalseThen
strFilter = "[Requestor] = '" & Me.txtRequest.Value & "'"
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear All:
I succesfully use multiple combo boxes as filter to open report.
But I
have
trouble if using txtbox.

I create a command button to triggle filter.

Here is the code for textbox name txtRequest.
If IsNull(Me.txtRequest.Value) Then
strRequest = "Like '*'"
else
strRequest = "='" & Me.txtRequest.Value & "'"
End If

and then

strFilter = "[Requestor] " & strRequest
Requestor is the field in report.
But after report is open, I see no matching record.

Also since I must open the report first while using this form
filter.
Is
any way I can temporary hide this report (like minimize) and then
after
filter applies, then the report can be maximize? Need your
suggestion
Anything wrong with it??? Need help in this part
 
Hi. Doug:

Thank for teaching me another way to "catch" the value in the textbox.
I only key in string: Master in the textbox. After Ctrl G I got:
strFilter = [Company Name] ='Master' (which is partial of the company)
The report shows blank. But if I use the full name: Master Shipping then the
record can be found.
Actually, I only have Master Shipping as Company Name in my record.

I know what's going wrong now. Only need to work on either wildcard like *
or partial of the string as filter.

Thanks.

Douglas J. Steele said:
Change

With Reports![By Staff]
.Filter = strFilter
.FilterOn = True
End With

to

Debug.Print "strFilter = " & strFilter
With Reports![By Staff]
.Filter = strFilter
.FilterOn = True
End With

Once the code's executed, go to the Immediate WIndow (Ctrl-G) and let me
know what's been printed there.

Does your code work under certain conditions, but not under others? If so,
post what strFilter is when it works, what it is when it doesn't work, and
what's different about what's selected or filled in on your form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Vivi said:
Dear Doug:

Here is the code I have:

Private Sub cmdApplyFilter_Click()
Dim strStaff As String
Dim strBU As String
Dim strRequestor As String
Dim strCompany As String
Dim strFilter As String
' four multiple filters
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "By Staff") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Staff field
If IsNull(Me.cbostaff.Value) Then
strStaff = "Like '*'"
Else
strStaff = "='" & Me.cbostaff.Value & "'"
End If
' Build criteria string for BU field
If IsNull(Me.cboBU.Value) Then
strBU = "Like '*'"
Else
strBU = "='" & Me.cboBU.Value & "'"
End If

'Build criteria string for Requestor field
'If Not IsNull(Me.txtRequestor) Then
'strFilter = strFilter & "([Requestor] Like ""*" & Me.txtRequestor &
"*"") AND "
'End If

'Build criteria string for Company field
If IsNull(Me.txtCompany.Value) Then
strCompany = "Like '*'"
Else
strCompany = "='" & Me.txtCompany.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Company Name] " & strCompany
'strFilter = "[Assigned Staff] " & strStaff & " AND [Requested Unit] "
&
strBU & " AND [Requestor] " & strRequestor
( I marked it off to test one filter only, when I use "combo box" filters
it works fine.)


' Apply the filter and switch it on
With Reports![By Staff]

.Filter = strFilter
.FilterOn = True
End With
End Sub

Douglas J. Steele said:
I'm asking you to print out what strFilter contains when it doesn't work.
(Seeing what it contains when it works might be a good idea as well)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dear Doug:

The report can be opened with all blank values.
Then I use combo box as filter to open the same report, it is
working......


:

What's the final result for strFilter that's not working?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug thank for your reply.
Still get the same EMPTY report.

I use strFilter to combine all multiple query fields together.
Combo
boxes
are working fine. But txt still not working.....

strFilter = "[Requestor] " & strRequest

Any other suggestion?


:

How about just

If IsNull(Me.txtRequest.Value) = FalseThen
strFilter = "[Requestor] = '" & Me.txtRequest.Value & "'"
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear All:
I succesfully use multiple combo boxes as filter to open report.
But I
have
trouble if using txtbox.

I create a command button to triggle filter.

Here is the code for textbox name txtRequest.
If IsNull(Me.txtRequest.Value) Then
strRequest = "Like '*'"
else
strRequest = "='" & Me.txtRequest.Value & "'"
End If

and then

strFilter = "[Requestor] " & strRequest
Requestor is the field in report.
But after report is open, I see no matching record.

Also since I must open the report first while using this form
filter.
Is
any way I can temporary hide this report (like minimize) and then
after
filter applies, then the report can be maximize? Need your
suggestion
Anything wrong with it??? Need help in this part
 
If IsNull(Me.txtCompany) Then
strFilter = "[Company Name] Like '*'"
Else
strFilter = "[Company Name] Like " & Me.txtCompany & "*'"
End If

although, personally, I'd not both filtering if txtCompany is null:

If IsNull(Me.txtCompany) = FalseThen
strFilter = "[Company Name] Like " & Me.txtCompany & "*'"
End If

If Len(strFilter) > 0 Then
With Reports![By Staff]
.Filter = strFilter
.FilterOn = True
End With
Else
.FilterOn = False
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Vivi said:
Hi. Doug:

Thank for teaching me another way to "catch" the value in the textbox.
I only key in string: Master in the textbox. After Ctrl G I got:
strFilter = [Company Name] ='Master' (which is partial of the company)
The report shows blank. But if I use the full name: Master Shipping then
the
record can be found.
Actually, I only have Master Shipping as Company Name in my record.

I know what's going wrong now. Only need to work on either wildcard like
*
or partial of the string as filter.

Thanks.

Douglas J. Steele said:
Change

With Reports![By Staff]
.Filter = strFilter
.FilterOn = True
End With

to

Debug.Print "strFilter = " & strFilter
With Reports![By Staff]
.Filter = strFilter
.FilterOn = True
End With

Once the code's executed, go to the Immediate WIndow (Ctrl-G) and let me
know what's been printed there.

Does your code work under certain conditions, but not under others? If
so,
post what strFilter is when it works, what it is when it doesn't work,
and
what's different about what's selected or filled in on your form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Vivi said:
Dear Doug:

Here is the code I have:

Private Sub cmdApplyFilter_Click()
Dim strStaff As String
Dim strBU As String
Dim strRequestor As String
Dim strCompany As String
Dim strFilter As String
' four multiple filters
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "By Staff") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Staff field
If IsNull(Me.cbostaff.Value) Then
strStaff = "Like '*'"
Else
strStaff = "='" & Me.cbostaff.Value & "'"
End If
' Build criteria string for BU field
If IsNull(Me.cboBU.Value) Then
strBU = "Like '*'"
Else
strBU = "='" & Me.cboBU.Value & "'"
End If

'Build criteria string for Requestor field
'If Not IsNull(Me.txtRequestor) Then
'strFilter = strFilter & "([Requestor] Like ""*" &
Me.txtRequestor &
"*"") AND "
'End If

'Build criteria string for Company field
If IsNull(Me.txtCompany.Value) Then
strCompany = "Like '*'"
Else
strCompany = "='" & Me.txtCompany.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Company Name] " & strCompany
'strFilter = "[Assigned Staff] " & strStaff & " AND [Requested Unit]
"
&
strBU & " AND [Requestor] " & strRequestor
( I marked it off to test one filter only, when I use "combo box"
filters
it works fine.)


' Apply the filter and switch it on
With Reports![By Staff]

.Filter = strFilter
.FilterOn = True
End With
End Sub

:

I'm asking you to print out what strFilter contains when it doesn't
work.
(Seeing what it contains when it works might be a good idea as well)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dear Doug:

The report can be opened with all blank values.
Then I use combo box as filter to open the same report, it is
working......


:

What's the final result for strFilter that's not working?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug thank for your reply.
Still get the same EMPTY report.

I use strFilter to combine all multiple query fields together.
Combo
boxes
are working fine. But txt still not working.....

strFilter = "[Requestor] " & strRequest

Any other suggestion?


:

How about just

If IsNull(Me.txtRequest.Value) = FalseThen
strFilter = "[Requestor] = '" & Me.txtRequest.Value & "'"
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear All:
I succesfully use multiple combo boxes as filter to open
report.
But I
have
trouble if using txtbox.

I create a command button to triggle filter.

Here is the code for textbox name txtRequest.
If IsNull(Me.txtRequest.Value) Then
strRequest = "Like '*'"
else
strRequest = "='" & Me.txtRequest.Value & "'"
End If

and then

strFilter = "[Requestor] " & strRequest
Requestor is the field in report.
But after report is open, I see no matching record.

Also since I must open the report first while using this form
filter.
Is
any way I can temporary hide this report (like minimize) and
then
after
filter applies, then the report can be maximize? Need your
suggestion
Anything wrong with it??? Need help in this part
 
Back
Top