Filtering Report with Form (Data Type error)

  • Thread starter Edgar Chado via AccessMonster.com
  • Start date
E

Edgar Chado via AccessMonster.com

Hi to all,

I am using this code in the onclick event of a submit command button in a
Form which has two combo boxes which I use to filter a Report. This is a
pop up form.

The code works as long as the data types i use are text. The problem is
that i have a date datatype and a number datatype. So each timen I use
this form with these comboboxes I get a type mismatch error.

Could someone show me what to put into my code so dates are dates and
numbers numbers. I know that the error is associated to the Chr(34)

One more thing, the combo boxes are unbound.

Here is the code:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer
' Build SQL String.
For intCounter = 1 To 2
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" &
intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" &
intCounter) & Chr(34) & " And "
End If
Next
If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))
' Set the Filter property.
Reports![infReporte_Pedidos].Filter = strSQL
Reports![infReporte_Pedidos].FilterOn = True
End If
End Sub
 
G

Guest

Edgar Chado via AccessMonster.com said:
Hi to all,

I am using this code in the onclick event of a submit command button in a
Form which has two combo boxes which I use to filter a Report. This is a
pop up form.

The code works as long as the data types i use are text. The problem is
that i have a date datatype and a number datatype. So each timen I use
this form with these comboboxes I get a type mismatch error.

Could someone show me what to put into my code so dates are dates and
numbers numbers. I know that the error is associated to the Chr(34)

One more thing, the combo boxes are unbound.

Here is the code:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer
' Build SQL String.
For intCounter = 1 To 2
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" &
intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" &
intCounter) & Chr(34) & " And "
End If
Next
If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))
' Set the Filter property.
Reports![infReporte_Pedidos].Filter = strSQL
Reports![infReporte_Pedidos].FilterOn = True
End If
End Sub
Click to expand...

Edgar,

Strings are delimited with quotes (single or double): "Hello"
Dates are delimited with the pound sign: #4/10/2005# (April 10th)
Numbers are not delimited: 100

If you had 3 combo boxes where you could select a string, a date and a
number, the code would look like:

' watch for line wrap '
'***** Air Code **************
Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer
' Build SQL String.

strSQL = ""

For intCounter = 1 To 3
If Me("Filter" & intCounter) <> "" Then
Select Case intCounter
Case 1 ' string
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] =
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "

Case 2 ' Date
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] =
#" & Me("Filter" & intCounter) & "# And "

Case 3 ' Number
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] =
" & Me("Filter" & intCounter) & " And "
End Select

End If
Next
If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))
' Set the Filter property.
Reports![infReporte_Pedidos].Filter = strSQL
Reports![infReporte_Pedidos].FilterOn = True
End If
'****** End code ********

I also changed you code a little. From this:

.... & "] " & " = " & Chr(34) & .......
^^^^^^^
to this:

.... & "] = " & Chr(34) & .......
^^^

this also works:

.... & "] = '" & ....... & "' And "

If you look close, it is: space/ equals/ space/ single quote/ double quote

and: double quote / single quote/ space/ AND / space/ double quote


HTH
 
E

Edgar Chado via AccessMonster.com

SteveS,

I don?t know how to thank you. I have been looking for this solution for
several days. It is working!!!!!! It is great to see the report behave as
I intended it to!!!!! :)

Thank you very much.

Edgar.
 
E

Edgar Chado via AccessMonster.com

SteveS,

I have one more question. It came up while trying out the form and the
report. What should I do to add an option in the combo boxes which will
let me select ALL the posible results. This way I could only filter the
report with any of the other combo boxes.

Thanks in advance.
 
G

Guest

Edgar Chado via AccessMonster.com said:
SteveS,

I have one more question. It came up while trying out the form and the
report. What should I do to add an option in the combo boxes which will
let me select ALL the posible results. This way I could only filter the
report with any of the other combo boxes.

Thanks in advance.
Click to expand...

The easiest way is to not select anything in the combo box (the combo boxes
are unbound??). The code checks to see if there is a value in the combo box.
If the combo box is empty, it is skipped (not added to the filter string).
You should change two lines and add two lines.

You might be able to show "ALL" in the combo box bychange the combo box row
source to a union query (see http://www.mvps.org/access/forms/frm0043.htm)

How many combo boxes are there and how many fields are in the combo boxes?
 
E

Edgar Chado via AccessMonster.com

SteveS,

In my form I have a clear selection button. Up till now, this button only
clears the selection of the combo boxes. But what I would really like it
to do is to reset the report and show all the data in the report. I would
like it to clear all the filters in the report.

Is this possible???

Thanks.
 
G

Guest

Edgar Chado via AccessMonster.com said:
SteveS,

In my form I have a clear selection button. Up till now, this button only
clears the selection of the combo boxes. But what I would really like it
to do is to reset the report and show all the data in the report. I would
like it to clear all the filters in the report.

Is this possible???

Thanks.
Click to expand...

Try adding there two lines to the bottom of the code for clearing the combo
boxes:

Reports![infReporte_Pedidos].FilterOn = False
Reports![infReporte_Pedidos].Requery
 
E

Edgar Chado via AccessMonster.com

SteveS,

It is working. I can now clear the selection and reset the report.

Thanks.
 
E

Edgar Chado via AccessMonster.com

SteveS,

I am now having problems with the date filter. I have no problem when
filtering by client and Order Id, but I do have a problem with the date. I
do not use the american format for dates (mm/dd/yyy), instead I use
dd/mm/yyyy.

The problem is that the filter is using the american format, but I need to
use the other format.

Should I change anything? Is it the form or the report?

Thanks.
 
S

SteveS

Edgar said:
SteveS,

I am now having problems with the date filter. I have no problem when
filtering by client and Order Id, but I do have a problem with the date. I
do not use the american format for dates (mm/dd/yyy), instead I use
dd/mm/yyyy.

The problem is that the filter is using the american format, but I need to
use the other format.

Should I change anything? Is it the form or the report?

Thanks.
Click to expand...

Edgar,

From what I have found, SQL *requires* dates to be in US format. See item #2 at

http://allenbrowne.com/ser-36.html


Try replacing the Case 2 line with:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] =
#" & Format(Me("Filter" & intCounter), "mm/dd/yyyy") & "# And "
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using a filter to show results on a report 5
Filter a Report from a Pop-Up Form 3
REPOST - Can't quite link a report to a form 2
Run-time error 13 (in a pop-up form) 4
Filter a Report from a Pop-Up Form 1
Combo Box frustration 6
VB Code for form filter with different Data types 4
Improper coding for my Form Filter 8

Top