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

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