query for report question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to create a form to open a report based on what is selected in a
combobox and textbox. The combobox would contain the field names and the
textbox would contain the criteria. Does anyone have any information on how
exactly to do this? Or am I stuck with creating a seperate query for each
report.

Thanks in advance
 
Assuming that you are opening the report from a button on the form, all you
need do is include a Where parameter in the command to open the report, such
as :
DoCmd.OpenReport "rptMyReportName", acPreview, , cboxFieldName & " = " &
txtValue

This will work fine if your fields are numeric. If they are text you'll
need:
DoCmd.OpenReport "rptMyReportName", acPreview, , cboxFieldName & " = '"
& txtValue & "'"

and if they are datetime values you'll need:
DoCmd.OpenReport "rptMyReportName", acPreview, , cboxFieldName & " = #"
& txtValue & "#"

If the fields are mixed datatypes, the easiest way is probably to include a
field for the datatype in the combobox recordsource (it does not have to be
visible), and use it to build the Where clause, such as:
Dim strWhere as String
Select Case cboxFieldName.Column(1) 'assuming you add a datatype
field as the second field of your combobox source
Case "text"
strWhere = cboxFieldName & " = " & txtValue
Case "numeric"
strWhere = cboxFieldName & " = '" & txtValue & "'"
Case "datetime"
strWhere = cboxFieldName & " = #" & txtValue & "#"
Case Else
End Select
DoCmd.OpenReport "rptMyReportName", acPreview, , strWhere

You could use a number to indicate the datatype (eg. 1 = numeric, 2 = text,
etc), and use Case 1, Case 2, etc.
You may also need to manipulate date values to get the correct format for
the query, if the textbox control is formatting them.

HTH,

Rob
 
You are on the right track. Assuming your combo box is named "cboFieldName"
and your text box is named "txtCriteria". You could use code like:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
Dim strWhere As String
Dim intDataType As DAO.DataTypeEnum
Dim strDelimiter As String
strWhere = "1=1 "
If Not IsNull(Me.cboFieldName) And _
Not IsNull(Me.txtCriteria) Then
' the next line should end with ".Type"
intDataType =
CurrentDb.TableDefs(Me.cboFieldName.RowSource).Fields(Me.cboFieldName).Type
Select Case intDataType
Case dbText, dbMemo
strDelimiter = """"
Case dbDate, dbTime
strDelimiter = "#"
Case Else
strDelimiter = ""
End Select
strWhere = strWhere & "And [" & Me.cboFieldName & _
"] = " & strDelimiter & Me.txtCriteria & _
strDelimiter
End If
MsgBox strWhere
stDocName = "rptTest"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub
 
I would like to create a form to open a report based on what is selected in a
combobox and textbox. The combobox would contain the field names and the
textbox would contain the criteria. Does anyone have any information on how
exactly to do this? Or am I stuck with creating a seperate query for each
report.
Click to expand...

You can certainly use a single parameter query. Either use the
WhereCondition of the OpenReport code as suggested by Rob, or base the
Report on a query referencing controls on the form:

=[Forms]![NameOfTheForm]![NameOfTheControl]


John W. Vinson[MVP]
 
Back
Top