Preview the filtered records

[Hiro]

Hello Forum Members:

I have a form which has a preview report button. If there is no filtered
recrod, it works, but when there are filtered records, an error message pops
up such as
"Q_Biopsy_Product.CompanyName" or "Q_Biopsy_Product.JawsSize"
I made sure that all the control names are matching with those in the
sCriteria statement. Can you see something that I am doing wrong?

And this is the code:
{starts}
Private Sub cmdPreview_Report_Click()
On Error Resume Next
Dim sCriteria As String
sCriteria = " 1 = 1 "
If FindCompany <> "" Then
sCriteria = sCriteria & " AND Q_Biopsy_Product.CompanyName =
""" & FindCompany & """"
End If
If FindSize <> "" Then
sCriteria = sCriteria & " AND Q_Biopsy_Product.JawsSize =
""" & FindSize & """"
End If
If cboFilterFenestrated <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.JawsFenestrated = """ & cboFilterFenestrated & """"
End If
If FindProduct <> "" Then
sCriteria = sCriteria & " AND Q_Biopsy_Product.ProductName =
""" & FindProduct & """"
End If
If FindVolume <> "" Then
sCriteria = sCriteria & " AND Q_Biopsy_Product.JawsVolume =
""" & FindVolume & """"
End If
If FindLength <> "" Then
sCriteria = sCriteria & " AND Q_Biopsy_Product.Length = """
& FindLength & """"
End If
If FindUsage <> "" Then
sCriteria = sCriteria & " AND Q_Biopsy_Product.UsageArea =
""" & FindUsage & """"
End If
If cboFilterPE <> "" Then
sCriteria = sCriteria & " AND Q_Biopsy_Product.PE = """ &
cboFilterPE & """"
End If
DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria
End Sub
{ends}

 

[NetworkTrade]

well what I see is that your code is filled with If/then statements that are
based on a value being there....i.e. If xyz <>"" then

so what happens if xyz has no data ?? i.e. xyz="" then there is no
value for this and perhaps the report in opening is always looking for a value

this would explain why it works with no filter, but does not work when there
is a filter...

so you need to consider throwing in a default value If xyz=""

 

[Hiro]

Hello NTC:

Thanks for your quick reply.
I figured it out and changed the very last part as follows:

1) This one does not work. - DoCmd.OpenReport "T_Biopsy_Product", acPreview,
, sCriteria
End Sub

2) This one works. - DoCmd.OpenReport "T_Biopsy_Product", acPreview, ,
Me.Filter
End Sub

Thanks again for your support.

With best regards,
Hiro

 

[Klatuu]

You don't need the Q_Biopsy_Product part.
What you need to use in your filtering is the field name of the report's
record set, not controls on the form or on the report.

Also, the 1 =1 part is not necessary. When you Dim a string variable as you
do with sCriteria, it is initialized as an empty string (same as "" or
vbNullString) so you can concatenate it without a problem.

Also, when you pass an empty string in the OpenReport method, it is exactly
the same as not even passing the argument.

 

[Hiro]

Hello Klatuu:

Thank you for your suggestions.
I thought I figured this out, and when I tried to make sure that every thing
was all right, but I was wrong.

DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria - This one works
only when there are no filtered records.
DoCmd.OpenReport "T_Biopsy_Product", acPreview, , Me.Filter - This one
works when there are filtered records, but when there are no filtered
records, the report “T_Biopsy_Product†produces only the partial records.

I’m wondering why it is not producing the full records when there are no
filtered records. It will be greatly appreciated if you could guide me
through on this.

With best regards,
Hiro

 

[Klatuu]

Note I have just cleaned up the code to make it more readable and removed
things that did not need to be there. One thing I find very suspicious is
that all fields in your table are text. You have the syntax for every
criteria as if it were text. The syntax has to be correct for the data type
of the field in the underlying table. For example, in this instance I doubt
Length is a text field:

sCriteria = sCriteria & "[Length] = """ & FindLength & """"

If Length is actually a numeric field, the correct syntax would be

sCriteria = sCriteria & "[Length] = " & FindLength

Also note the addtition of the AddAnd function. When you are stringing
together mutliple criteria, you need to know when and when not to include an
And in the string. The AddAnd function does that for you. Put it in a
standard module. You will find you will end up using it in other places.

Private Sub cmdPreview_Report_Click()
Dim sCriteria As String

On Error Resume Next
If FindCompany <> "" Then
"[CompanyName] = """ & FindCompany & """"
End If

If FindSize <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsSize] = """ & FindSize & """"
End If

If cboFilterFenestrated <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsFenestrated] = """ &
cboFilterFenestrated & """"
End If

If FindProduct <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[ProductName] = """ & FindProduct & """"
End If

If FindVolume <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsVolume] = """ & FindVolume & """"
End If

If FindLength <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[Length] = """ & FindLength & """"
End If

If FindUsage <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[UsageArea] = """ & FindUsage & """"
End If

If cboFilterPE <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[PE] = """ & cboFilterPE & """"
End If

DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria
End Sub

******************************

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities", vbExclamation,
conMsgTitle
GoTo AddAnd_Exit

End Function

 

[Hiro]

Hello Klatuu:

Thanks again for your reply and kindly re-arranging the code.
When you said the correct syntax for underlying table, I realized that I
would need to make some adjustments.

Would you please show me the correct syntax for a yes/no data type?
I am using a yes/no data type for:
* cboFilterFenestrated
* cboFilterPE

I also would like to add a currency data type. I can still use the same
numeric syntax. Am I correct?

I hope I am not asking too much on this and I sincerely appreciate your help.

With best regards,
Hiro

Note I have just cleaned up the code to make it more readable and removed
things that did not need to be there. One thing I find very suspicious is
that all fields in your table are text. You have the syntax for every
criteria as if it were text. The syntax has to be correct for the data type
of the field in the underlying table. For example, in this instance I doubt
Length is a text field:

sCriteria = sCriteria & "[Length] = """ & FindLength & """"

If Length is actually a numeric field, the correct syntax would be

sCriteria = sCriteria & "[Length] = " & FindLength

Also note the addtition of the AddAnd function. When you are stringing
together mutliple criteria, you need to know when and when not to include an
And in the string. The AddAnd function does that for you. Put it in a
standard module. You will find you will end up using it in other places.

Private Sub cmdPreview_Report_Click()
Dim sCriteria As String

On Error Resume Next
If FindCompany <> "" Then
"[CompanyName] = """ & FindCompany & """"
End If

If FindSize <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsSize] = """ & FindSize & """"
End If

If cboFilterFenestrated <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsFenestrated] = """ &
cboFilterFenestrated & """"
End If

If FindProduct <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[ProductName] = """ & FindProduct & """"
End If

If FindVolume <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsVolume] = """ & FindVolume & """"
End If

If FindLength <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[Length] = """ & FindLength & """"
End If

If FindUsage <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[UsageArea] = """ & FindUsage & """"
End If

If cboFilterPE <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[PE] = """ & cboFilterPE & """"
End If

DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria
End Sub

******************************

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities", vbExclamation,
conMsgTitle
GoTo AddAnd_Exit

End Function

Hello Klatuu:

Thank you for your suggestions.
I thought I figured this out, and when I tried to make sure that every
thing
was all right, but I was wrong.

DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria - This one
works
only when there are no filtered records.
DoCmd.OpenReport "T_Biopsy_Product", acPreview, , Me.Filter - This one
works when there are filtered records, but when there are no filtered
records, the report "T_Biopsy_Product" produces only the partial records.

I'm wondering why it is not producing the full records when there are no
filtered records. It will be greatly appreciated if you could guide me
through on this.

With best regards,
Hiro

 

[Klatuu]

A yes/no (boolean) data type is treated like a numeric field, so you would
use the syntax I posted previously.

Hello Klatuu:

Thanks again for your reply and kindly re-arranging the code.
When you said the correct syntax for underlying table, I realized that I
would need to make some adjustments.

Would you please show me the correct syntax for a yes/no data type?
I am using a yes/no data type for:
* cboFilterFenestrated
* cboFilterPE

I also would like to add a currency data type. I can still use the same
numeric syntax. Am I correct?

I hope I am not asking too much on this and I sincerely appreciate your
help.

With best regards,
Hiro

Note I have just cleaned up the code to make it more readable and removed
things that did not need to be there. One thing I find very suspicious
is
that all fields in your table are text. You have the syntax for every
criteria as if it were text. The syntax has to be correct for the data
type
of the field in the underlying table. For example, in this instance I
doubt
Length is a text field:

sCriteria = sCriteria & "[Length] = """ & FindLength & """"

If Length is actually a numeric field, the correct syntax would be

sCriteria = sCriteria & "[Length] = " & FindLength

Also note the addtition of the AddAnd function. When you are stringing
together mutliple criteria, you need to know when and when not to include
an
And in the string. The AddAnd function does that for you. Put it in a
standard module. You will find you will end up using it in other places.

Private Sub cmdPreview_Report_Click()
Dim sCriteria As String

On Error Resume Next
If FindCompany <> "" Then
"[CompanyName] = """ & FindCompany & """"
End If

If FindSize <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsSize] = """ & FindSize & """"
End If

If cboFilterFenestrated <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsFenestrated] = """ &
cboFilterFenestrated & """"
End If

If FindProduct <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[ProductName] = """ & FindProduct & """"
End If

If FindVolume <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsVolume] = """ & FindVolume & """"
End If

If FindLength <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[Length] = """ & FindLength & """"
End If

If FindUsage <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[UsageArea] = """ & FindUsage & """"
End If

If cboFilterPE <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[PE] = """ & cboFilterPE & """"
End If

DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria
End Sub

******************************

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities", vbExclamation,
conMsgTitle
GoTo AddAnd_Exit

End Function

Hello Klatuu:

Thank you for your suggestions.
I thought I figured this out, and when I tried to make sure that every
thing
was all right, but I was wrong.

DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria - This one
works
only when there are no filtered records.
DoCmd.OpenReport "T_Biopsy_Product", acPreview, , Me.Filter - This one
works when there are filtered records, but when there are no filtered
records, the report "T_Biopsy_Product" produces only the partial
records.

I'm wondering why it is not producing the full records when there are
no
filtered records. It will be greatly appreciated if you could guide me
through on this.

With best regards,
Hiro

:

You don't need the Q_Biopsy_Product part.
What you need to use in your filtering is the field name of the
report's
record set, not controls on the form or on the report.

Also, the 1 =1 part is not necessary. When you Dim a string variable
as
you
do with sCriteria, it is initialized as an empty string (same as "" or
vbNullString) so you can concatenate it without a problem.

Also, when you pass an empty string in the OpenReport method, it is
exactly
the same as not even passing the argument.


Hello Forum Members:

I have a form which has a preview report button. If there is no
filtered
recrod, it works, but when there are filtered records, an error
message
pops
up such as
"Q_Biopsy_Product.CompanyName" or "Q_Biopsy_Product.JawsSize"
I made sure that all the control names are matching with those in
the
sCriteria statement. Can you see something that I am doing wrong?

And this is the code:
{starts}
Private Sub cmdPreview_Report_Click()
On Error Resume Next
Dim sCriteria As String
sCriteria = " 1 = 1 "
If FindCompany <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.CompanyName
=
""" & FindCompany & """"
End If
If FindSize <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.JawsSize
=
""" & FindSize & """"
End If
If cboFilterFenestrated <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.JawsFenestrated = """ & cboFilterFenestrated & """"
End If
If FindProduct <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.ProductName
=
""" & FindProduct & """"
End If
If FindVolume <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.JawsVolume =
""" & FindVolume & """"
End If
If FindLength <> "" Then
sCriteria = sCriteria & " AND Q_Biopsy_Product.Length
=
"""
& FindLength & """"
End If
If FindUsage <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.UsageArea
=
""" & FindUsage & """"
End If
If cboFilterPE <> "" Then
sCriteria = sCriteria & " AND Q_Biopsy_Product.PE =
"""
&
cboFilterPE & """"
End If
DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria
End Sub
{ends}

 

[Hiro]

Hello Klatuu:

Thank you for your advice once again.
But I noticed the following behavior.
1) If I select multiple filters, it does not produce the report.
2) A yes/no data field (check box in the table and value list in the form)
does not work well with numeric syntax.

Is there anything that I am missing?

With best regards,
Hiro

A yes/no (boolean) data type is treated like a numeric field, so you would
use the syntax I posted previously.

Hello Klatuu:

Thanks again for your reply and kindly re-arranging the code.
When you said the correct syntax for underlying table, I realized that I
would need to make some adjustments.

Would you please show me the correct syntax for a yes/no data type?
I am using a yes/no data type for:
* cboFilterFenestrated
* cboFilterPE

I also would like to add a currency data type. I can still use the same
numeric syntax. Am I correct?

I hope I am not asking too much on this and I sincerely appreciate your
help.

With best regards,
Hiro

Note I have just cleaned up the code to make it more readable and removed
things that did not need to be there. One thing I find very suspicious
is
that all fields in your table are text. You have the syntax for every
criteria as if it were text. The syntax has to be correct for the data
type
of the field in the underlying table. For example, in this instance I
doubt
Length is a text field:

sCriteria = sCriteria & "[Length] = """ & FindLength & """"

If Length is actually a numeric field, the correct syntax would be

sCriteria = sCriteria & "[Length] = " & FindLength

Also note the addtition of the AddAnd function. When you are stringing
together mutliple criteria, you need to know when and when not to include
an
And in the string. The AddAnd function does that for you. Put it in a
standard module. You will find you will end up using it in other places.

Private Sub cmdPreview_Report_Click()
Dim sCriteria As String

On Error Resume Next
If FindCompany <> "" Then
"[CompanyName] = """ & FindCompany & """"
End If

If FindSize <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsSize] = """ & FindSize & """"
End If

If cboFilterFenestrated <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsFenestrated] = """ &
cboFilterFenestrated & """"
End If

If FindProduct <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[ProductName] = """ & FindProduct & """"
End If

If FindVolume <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsVolume] = """ & FindVolume & """"
End If

If FindLength <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[Length] = """ & FindLength & """"
End If

If FindUsage <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[UsageArea] = """ & FindUsage & """"
End If

If cboFilterPE <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[PE] = """ & cboFilterPE & """"
End If

DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria
End Sub

******************************

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities", vbExclamation,
conMsgTitle
GoTo AddAnd_Exit

End Function



Hello Klatuu:

Thank you for your suggestions.
I thought I figured this out, and when I tried to make sure that every
thing
was all right, but I was wrong.

DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria - This one
works
only when there are no filtered records.
DoCmd.OpenReport "T_Biopsy_Product", acPreview, , Me.Filter - This one
works when there are filtered records, but when there are no filtered
records, the report "T_Biopsy_Product" produces only the partial
records.

I'm wondering why it is not producing the full records when there are
no
filtered records. It will be greatly appreciated if you could guide me
through on this.

With best regards,
Hiro

:

You don't need the Q_Biopsy_Product part.
What you need to use in your filtering is the field name of the
report's
record set, not controls on the form or on the report.

Also, the 1 =1 part is not necessary. When you Dim a string variable
as
you
do with sCriteria, it is initialized as an empty string (same as "" or
vbNullString) so you can concatenate it without a problem.

Also, when you pass an empty string in the OpenReport method, it is
exactly
the same as not even passing the argument.


Hello Forum Members:

I have a form which has a preview report button. If there is no
filtered
recrod, it works, but when there are filtered records, an error
message
pops
up such as
"Q_Biopsy_Product.CompanyName" or "Q_Biopsy_Product.JawsSize"
I made sure that all the control names are matching with those in
the
sCriteria statement. Can you see something that I am doing wrong?

And this is the code:
{starts}
Private Sub cmdPreview_Report_Click()
On Error Resume Next
Dim sCriteria As String
sCriteria = " 1 = 1 "
If FindCompany <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.CompanyName
=
""" & FindCompany & """"
End If
If FindSize <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.JawsSize
=
""" & FindSize & """"
End If
If cboFilterFenestrated <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.JawsFenestrated = """ & cboFilterFenestrated & """"
End If
If FindProduct <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.ProductName
=
""" & FindProduct & """"
End If
If FindVolume <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.JawsVolume =
""" & FindVolume & """"
End If
If FindLength <> "" Then
sCriteria = sCriteria & " AND Q_Biopsy_Product.Length
=
"""
& FindLength & """"
End If
If FindUsage <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.UsageArea
=
""" & FindUsage & """"
End If
If cboFilterPE <> "" Then
sCriteria = sCriteria & " AND Q_Biopsy_Product.PE =
"""
&
cboFilterPE & """"
End If
DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria
End Sub
{ends}

 

[Klatuu]

Run your code in debug mode and put a breakpoint just after the criteria
variable is complete. Review it to see if it is correct.

Hello Klatuu:

Thank you for your advice once again.
But I noticed the following behavior.
1) If I select multiple filters, it does not produce the report.
2) A yes/no data field (check box in the table and value list in the form)
does not work well with numeric syntax.

Is there anything that I am missing?

With best regards,
Hiro

A yes/no (boolean) data type is treated like a numeric field, so you
would
use the syntax I posted previously.

Hello Klatuu:

Thanks again for your reply and kindly re-arranging the code.
When you said the correct syntax for underlying table, I realized that
I
would need to make some adjustments.

Would you please show me the correct syntax for a yes/no data type?
I am using a yes/no data type for:
* cboFilterFenestrated
* cboFilterPE

I also would like to add a currency data type. I can still use the same
numeric syntax. Am I correct?

I hope I am not asking too much on this and I sincerely appreciate your
help.

With best regards,
Hiro


:

Note I have just cleaned up the code to make it more readable and
removed
things that did not need to be there. One thing I find very
suspicious
is
that all fields in your table are text. You have the syntax for every
criteria as if it were text. The syntax has to be correct for the
data
type
of the field in the underlying table. For example, in this instance I
doubt
Length is a text field:

sCriteria = sCriteria & "[Length] = """ & FindLength & """"

If Length is actually a numeric field, the correct syntax would be

sCriteria = sCriteria & "[Length] = " & FindLength

Also note the addtition of the AddAnd function. When you are
stringing
together mutliple criteria, you need to know when and when not to
include
an
And in the string. The AddAnd function does that for you. Put it in
a
standard module. You will find you will end up using it in other
places.

Private Sub cmdPreview_Report_Click()
Dim sCriteria As String

On Error Resume Next
If FindCompany <> "" Then
"[CompanyName] = """ & FindCompany & """"
End If

If FindSize <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsSize] = """ & FindSize & """"
End If

If cboFilterFenestrated <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsFenestrated] = """ &
cboFilterFenestrated & """"
End If

If FindProduct <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[ProductName] = """ & FindProduct &
""""
End If

If FindVolume <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[JawsVolume] = """ & FindVolume &
""""
End If

If FindLength <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[Length] = """ & FindLength & """"
End If

If FindUsage <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[UsageArea] = """ & FindUsage & """"
End If

If cboFilterPE <> "" Then
sCriteria = AddAnd(sCriteria)
sCriteria = sCriteria & "[PE] = """ & cboFilterPE & """"
End If

DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria
End Sub

******************************

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities", vbExclamation,
conMsgTitle
GoTo AddAnd_Exit

End Function



Hello Klatuu:

Thank you for your suggestions.
I thought I figured this out, and when I tried to make sure that
every
thing
was all right, but I was wrong.

DoCmd.OpenReport "T_Biopsy_Product", acPreview, , sCriteria - This
one
works
only when there are no filtered records.
DoCmd.OpenReport "T_Biopsy_Product", acPreview, , Me.Filter - This
one
works when there are filtered records, but when there are no
filtered
records, the report "T_Biopsy_Product" produces only the partial
records.

I'm wondering why it is not producing the full records when there
are
no
filtered records. It will be greatly appreciated if you could guide
me
through on this.

With best regards,
Hiro

:

You don't need the Q_Biopsy_Product part.
What you need to use in your filtering is the field name of the
report's
record set, not controls on the form or on the report.

Also, the 1 =1 part is not necessary. When you Dim a string
variable
as
you
do with sCriteria, it is initialized as an empty string (same as ""
or
vbNullString) so you can concatenate it without a problem.

Also, when you pass an empty string in the OpenReport method, it is
exactly
the same as not even passing the argument.


Hello Forum Members:

I have a form which has a preview report button. If there is no
filtered
recrod, it works, but when there are filtered records, an error
message
pops
up such as
"Q_Biopsy_Product.CompanyName" or "Q_Biopsy_Product.JawsSize"
I made sure that all the control names are matching with those
in
the
sCriteria statement. Can you see something that I am doing wrong?

And this is the code:
{starts}
Private Sub cmdPreview_Report_Click()
On Error Resume Next
Dim sCriteria As String
sCriteria = " 1 = 1 "
If FindCompany <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.CompanyName
=
""" & FindCompany & """"
End If
If FindSize <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.JawsSize
=
""" & FindSize & """"
End If
If cboFilterFenestrated <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.JawsFenestrated = """ & cboFilterFenestrated &
""""
End If
If FindProduct <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.ProductName
=
""" & FindProduct & """"
End If
If FindVolume <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.JawsVolume =
""" & FindVolume & """"
End If
If FindLength <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.Length
=
"""
& FindLength & """"
End If
If FindUsage <> "" Then
sCriteria = sCriteria & " AND
Q_Biopsy_Product.UsageArea
=
""" & FindUsage & """"
End If
If cboFilterPE <> "" Then
sCriteria = sCriteria & " AND Q_Biopsy_Product.PE
=
"""
&
cboFilterPE & """"
End If
DoCmd.OpenReport "T_Biopsy_Product", acPreview, ,
sCriteria
End Sub
{ends}