18 queries!!!!???? 
Just for 1 report????? 
We need to fix that!!!
My question is, how to pass the data generated by the query, to a report. As I understand it, the SQL generates a record set. So, I need to get that recordset to the report. Or am I doing this all wrong? 
A report is bound to a table or query (the record source). The records can be limited for the report by using a filter (the WHERE clause). One way of doing this is to change the SQL "on-the-fly". You can modify the query def of the report or change the report record source to a different query.
The way I prefer is to use a basic query for the report record source. Just a standard "SELECT field1, field2, ... FROM MyTable" type of SQL string.
Using VBA, I get the parameters from a form and create a "WHERE" clause (without the WHERE). Then I use the "Docmd.Openreport" command to open the report. Below is a snippet of the code.
Code:
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the last trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
'***********************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'***********************************************************************
------<snip>--------
------<snip>--------
'DOB - Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull(Me.tbDOB) Then
strWhere = strWhere & "([end_dob] = " & Format(Me.tbDOB, conJetDate) & ") AND "
End If
'cboCity - Text field example.
If Not IsNull(Me.cboCity) Then
strWhere = strWhere & "([end_city] = '" & Me.cboCity & "') AND "
End If
'cboState - Text field example.
If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([end_state] = '" & Me.cboState & "') AND "
End If
'cboZip - Text field example.
If Not IsNull(Me.cboZip) Then
strWhere = strWhere & "([end_zip] = '" & Me.cboZip & "') AND "
End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(Trim(strWhere)) - 4
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'open the report on preview mode
' "lstForm" is a list box thst holds the report names
' "strWhere" is the criteria
DoCmd.OpenReport lstForm, acViewPreview, , strWhere
------<snip>--------
Using this approach, you can limit the records any way you want. If you get stuck, attach a zip file of the queries (the SQL for 18 queries is too much to post inline. 
) and I will try and whip up some code for you.
Windows 2K 
Access 2003
Reply With Quote
Originally Posted by khalid
don't have enough time to solve forum's problems.
