Filter A Report From a Pop-Up Box for Text, Numbers, and Yes/No Field Types 
Author Message
 Filter A Report From a Pop-Up Box for Text, Numbers, and Yes/No Field Types

I have a database with three different field types: text, number, and yes/no
(check boxes).  These fields are imported from another database which I
cannot control.

I want to give the users the ability to make a report with choices that use
all three types of fields as criteria.

I have copied the method shown in RptSmp97 from Microsoft's Website and
explained at:

http://www.*-*-*.com/

The problem is that this method only works for text fields because it passes
the query to the report with quotes.

How do I change it to work for text, numbers, and yes/no (-1,0)?

Here's the coding:

Dim strSQL As String, intCounter As Integer
     'Build SQL String
     For intCounter = 1 To 5
       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![rptCustomersNew].Filter = strSQL
        Reports![rptCustomersNew].FilterOn = True
     End If

Is there a better way to do this?

Can I change the formatting of the fields temporarily to pull the report?

Any ideas are welcome.

Thanks,

Michelle



Sat, 10 Nov 2001 03:00:00 GMT  
 Filter A Report From a Pop-Up Box for Text, Numbers, and Yes/No Field Types

Hiya,
Assuming that the Controls of the Form are Named the same As fields in
Tables
and the Form is unbound
Set the forms Tag Property to the Reports Recordsource in the Form_Open
Procedure
ie Me.Tag = "rptReport01"

Dim strSQL As String, intCounter As Integer
Dim strFld As String
Dim Qdef As QueryDef, Fld As Field
Dim C As Control
  Set QDef = DbEngine(0)(0).QueryDefs(Me.Tag)
  'Build SQL String
     For intCounter = 1 To 5
       Set C = Me("Filter" & intCounter)
       If Len(C) > 0 Then ' Quicker than <> ""
         Set Fld = Qdef.Fields("[" & C.Tag & "])
         If C = "Null" Then
            strFld = " Is Null"
         ElseIf "Not Null" Then
            strFld = "Is Not Null"
         Else
           Select Case Fld.Type
           Case dbText:
             strFld = " = " & Chr(34) & C & Chr(34)  
           Case dbDate:
             strFld = "= #" & Month(C) & "/" & Day(C) & "/" & Year(C) &
"#"
           Case Else
             strFld = " = " & C
           End Select
         End If
         strSQL = strSQL & "[" & C.Tag & "] " & strFld
       End If
     Next i

--
Regards
Pieter Wijnen


http://www.thuleeng.com

Tough guys don't take backup - tough guys cry a lot..

Good site to look for Access answers is:
http://home.att.net/~dashish

  wijnen.vcf
< 1K Download


Sun, 11 Nov 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Allowing ONLY text to be typed in my text box - NO NUMBERS

2. Allowing ONLY text to be typed in my text box - NO NUMBERS

3. How to write a Find filter which has a Yes/No field in C++

4. Type of data of a yes/no field

5. yes no field = type mismatch

6. Newbee Question: Access field type and VB text box

7. Yes/No Field and Check Box help

8. Yes - Rich Text Box assistance needed again ..

9. Pop-ups in MS Office

10. pop-ups

11. Pop-ups in TreeView

12. downloading file without pop-ups

 

 
Powered by phpBB® Forum Software