Hi Steve,
I like to this by:
1.) contructing the entire SQL string in code.
2.) use that SQL string as the recordsource for a datasheet subform to
"preview" the data that will be used for the report.
3.) Use the same SQL string as the recordsource for the report.
********************* Code **************
Option Compare Database
Option Explicit
Dim MySQL As String
Dim whr As String
'------------------------------
Public Sub GetSQL()
'--------------- Step One ------------------
'-------- Build the SELECT portion ---------
MySQL = ""
MySQL = MySQL & "SELECT tblContacts.* "
MySQL = MySQL & "FROM tblContacts"
'--------------- Step Two ------------------
'- Determine data type of the compare value -
'-------- and format it accordingly. --------
Dim CV
CV = (Me.txtCompareValue)
If IsNumeric(CV) Then
CV = CLng(CV)
ElseIf IsDate(CV) Then
CV = "#" & CDate(CV) & "#"
Else
CV = Chr(34) & CV & Chr(34)
End If
'--------------- Step Three ------------------
'-- Insert the selected comparison operator ---
'-- and ready the string for assembly into ----
'------------- the WHERE statement ------------
whr = ""
Select Case Me.optCriteriaType
Case 1 'Equal To
whr = whr & " = "
whr = whr & CV
Case 2 'Greater Than
whr = whr & " > "
whr = whr & CV
Case 3 'Less Than
whr = whr & " < "
whr = whr & CV
Case 4 'Like _____
'WHERE (((tblContacts.ContactName) Like "D" & '*'));
whr = whr & " Like "
whr = whr & CV
whr = whr & " & '*'"
Case 5 'Contains ____
'WHERE (((tblContacts.ContactName) Like '*' & "on" & '*'));
whr = whr & " Like "
whr = whr & "'*' & "
whr = whr & CV
whr = whr & " & '*'"
Case Else
whr = ""
End Select
'--------------- Step Four ------------------
'--- IF the user has entered any Criteria ----
'--- construct a WHERE clause and insert -----
'-------- it into the SQL statement ----------
If Len(whr) > 0 Then
MySQL = MySQL & " WHERE (((tblContacts."
MySQL = MySQL & Me.lstFieldNames & ")"
MySQL = MySQL & whr & " ))"
End If
'--------------- Step Five ------------------
'----- "Close out" the SQL statement ---------
'-- There may be those that argue that this --
'-- step is unnessecary, but it's just good --
'------- programming practice, IMHO ----------
MySQL = MySQL & " ;"
'Debug.Print MySQL
'--------------- Step Six ---------------------
'- Use this SQL statement as the RecordSource -
'- for the subform (and a report if you like) -
Me.sbfContacts.Form.RecordSource = MySQL
End Sub
'------------------------------------------
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim stDocName As String
stDocName = "rptContacts"
DoCmd.OpenReport stDocName, acPreview, MySQL
Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click
End Sub
************************************************** ********
--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.
================================================== ========================
"mooseshoes" <mo********@gmx.net> wrote in message
news:Tb*****************@newssvr21.news.prodigy.co m...
All:
I'm using Access 2000 on a Windows XP platform.
My goal is to use a form to gather user criteria which I will then parse
into a useable SQL string. At this point I would like to open one or more
reports and use the query as the recordsource.
What would be a common approach to doing this? I have been using ADO
methods up until this point for other tasks.
Using docmd.openreport is easy, but it doesn't accommodate a change in a
report's recordsouce from what I can tell although it does allow for SQL
filtering.
Your advice is appreciated and I'm happy to provide further information
upon request.
Best,
Steve