OpenReport and specifying criteria

[jonesda]

Hi,

I setup a generic report just to count the number of table entries by a particular field. E.g. Count of sales by product.

I then hoped to open this report using the Docmd.OpentReport and specify further criteria (strCondition) for the report.

DoCmd.OpenReport strReportName, acViewPreview, , strCondition

This doesn't work - I think because I have got an aggregate function in the report which it is calling.

Is there any other way to achieve the same result? - i.e. have a generic report (which contains an aggregate function) and pass further criteria to this using openreport or otherwise???

Is it possible to achieve the following in Access:
1. Create a SQL Statement.
2. Run the SQL Statement and send the results to a Report or Microsoft Excel document without having to have a report or query created.

Any help would be greatly appreciated.
Thanks

 

[Bat17]

You can set the record source of the Report to SQL but I cant see that that would be anydiffernt than adding a where clause when you apon it.

You can export a record set to XL. This code is using a template but it works the same for a standard XL file.
You will need a freference set to XL Tools>References... in any module

Sub ExportTo()
Dim appXL As Excel.Application
Dim wk As Excel.Workbook
Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "Select * From tblMyData"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
'rs.MoveFirst

Set appXL = New Excel.Application
Set wk = appXL.Workbooks.Add("C:\tblMyData1.xlt")
wk.Worksheets(1).Range("A1").CopyFromRecordset rs

'appXL.Visible = True
wk.SaveAs "C:\MyData" & Format(Now, "yyymmddhhnn") & ".xls"
wk.Close
appXL.Quit


Set rs = Nothing
Set wk = Nothing
Set appXL = Nothing

End Sub

HTH

Peter

 

[jonesda]

Thanks for Code Peter

I am still trying to figure out the OpenReport action.

I have the following query setup for a report:

SELECT Query.Case_Reference, Query.Date_Logged, Query.Date_Closed, Query.Staff_ID AS Query_Staff_ID, Query.Problem_Description, Query.query_type, Query.Date_Logged, Activity.Updated_Time, Activity.Staff_ID AS Activity_Staff_ID, Activity.Activity_Description
FROM Query INNER JOIN Activity ON Query.Case_Reference = Activity.Case_Reference;


The name of the report which uses this query is "ALLQUERY"

I then use the following code to open the report and to pass a further condition to the query:

DoCmd.OpenReport "ALLQUERY", acViewPreview, , strWhrcndtn

The string strWhrcndtn = "Query.query_type = 2"

However when I run this code I am prompted with a message box to enter a value for Query.query_type

Does anyone know why it might be prompting me for Query.query_type?

Do I need to change the format of the where condition I am passing in in OpenReport??

Any help would be greatly appreciated.

 

[Bat17]

try it without the query prefix, I think it should refer to a field
"[query_type] = 2"

HTH

Peter

 

[jonesda]

Thanks Peter,

I eventually got it woking. The reason why it didn't work was the fields in the where clause must also be in the select clause of the query. This is what I have below but I musn't have saved my query properly the first time ?!?

This is causing another headache though. I want to setup some generic queries with aggregate functions.

select count(field), field
from table
group by field
where fieldA = x

This sort of query works fine but when I setup a report with the query
select count(field), field
from table
group by field

and then pass the where clause in in the OpenReport condition I am prompted with a message box to enter the value for fieldA.

If I put fieldA in the select clause and do a count of it and then just ignore it from the report display it still doen't work. However, if I select it in the select clause and it then appears in the group by clause as well - it then works without prompting. But this isn't the query I want - i.e. I don't want to have to group by fieldA.

Any ideas about how I might resolve this??


Peter - another question about the above code:

Is it possible to export the results of a query to excel without exporting it to a specific file.

Set wk = appXL.Workbooks.Add("C:\tblMyData1.xlt")

I would like to leave the file path blank and let the users save it themselves after. Or else prompt the users about where they want to save the file.

Many Thanks

 

[Bat17]

prompt them for a name/path first and just replace the name/path in that line.

Peter