use a form to create a report based on a query

[Tom]

Hi,

I'm trying to make a form create a report from a form.

I the form the user is selecting the "WHERE" elements of the query. I can
make the query run from the form, its creating the report that is causing me
trouble.

I've pasted the SQL below to give an idea of what I'm trying to do.

SELECT DISTINCT tblissue.Start_date, tblissue.Originator,
tblissue.Serial_Number, tblissue.Log_Number, tblissue.Status,
tblissue.Severity, tblissue.Platform, tblissue.Issue_Type,
tblissue.Issue_Summary, tblissue.Current_Action
FROM tblissue INNER JOIN tblaction ON tblissue.Serial_Number =
tblaction.Serial_Number
WHERE (((tblissue.Status)=[FORMS]![frmReportGenerationSUMMARY]![Status]) AND
((tblissue.Issue_Type)=[FORMS]![frmReportGenerationSUMMARY]![IssueType]) AND
((Left([tblissue].[Serial_Number],3))=[FORMS]![frmReportGenerationSUMMARY]![Country]));

Thanks,
Tom

 

[Duane Hookom]

I would not place any dynamic criteria in the query. The code I would use to
open the report would look something like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.Status) Then
'If Status is Text
strWhere = strWhere & " And Status ='" & Me.Status & "' "
End If
If Not IsNull(Me.IssueType) Then
'If Issue_Type is numeric
strWhere = strWhere & " And Issue_Type=" & Me.IssueType
End If
If Not IsNull(Me.Country) Then
'assumes Country is text
strWhere = strWhere & " And Left([Serial_Number],3)='" & Me.Country & "' "
End If

DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

 

[Tom]

Thank you.

Had to tweek it a touch to fit the rest of what I'm doing, but it help a
lot, thanks.

Tom

I would not place any dynamic criteria in the query. The code I would use to
open the report would look something like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.Status) Then
'If Status is Text
strWhere = strWhere & " And Status ='" & Me.Status & "' "
End If
If Not IsNull(Me.IssueType) Then
'If Issue_Type is numeric
strWhere = strWhere & " And Issue_Type=" & Me.IssueType
End If
If Not IsNull(Me.Country) Then
'assumes Country is text
strWhere = strWhere & " And Left([Serial_Number],3)='" & Me.Country & "' "
End If

DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP

Hi,

I'm trying to make a form create a report from a form.

I the form the user is selecting the "WHERE" elements of the query. I can
make the query run from the form, its creating the report that is causing me
trouble.

I've pasted the SQL below to give an idea of what I'm trying to do.

SELECT DISTINCT tblissue.Start_date, tblissue.Originator,
tblissue.Serial_Number, tblissue.Log_Number, tblissue.Status,
tblissue.Severity, tblissue.Platform, tblissue.Issue_Type,
tblissue.Issue_Summary, tblissue.Current_Action
FROM tblissue INNER JOIN tblaction ON tblissue.Serial_Number =
tblaction.Serial_Number
WHERE (((tblissue.Status)=[FORMS]![frmReportGenerationSUMMARY]![Status]) AND
((tblissue.Issue_Type)=[FORMS]![frmReportGenerationSUMMARY]![IssueType]) AND
((Left([tblissue].[Serial_Number],3))=[FORMS]![frmReportGenerationSUMMARY]![Country]));

Thanks,
Tom