use a form to create a report based on a query

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

Guest

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
 
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
 
Thank you.

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

Tom

Duane Hookom said:
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

Tom said:
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
Click to expand...
Click to expand...
 
Back
Top