HAVING (((DatePart("m",[Date From]))=[Forms]![ExpenseForm]![cboMonth]) AND ((DatePart("m",[Date To]))=[Forms]![ExpenseForm]![cboMonth]) AND ((DatePart("yyyy",[Date From]))=[Forms]![ExpenseForm]![cboYear]) AND ((DatePart("yyyy",[Date To]))=[Forms]![ExpenseForm]![cboYear]) AND ((MWPRO.[Code 3])="1" Or (MWPRO.[Code 3])="2" Or (MWPRO.[Code 3])="3" Or (MWPRO.[Code 3])="4" Or (MWPRO.[Code 3])="5") AND ((Facility.FacilityID) Like [Forms]![ExpenseForm]![lstFacilityID]) AND ((Month.MonthValue)=[Forms]![ExpenseForm]![cboMonth]));
i hope my explanation is clear. Thanks in advance.
Ok, I create a query that will work but I was wondering, can I put a custom query in a report.
SELECT Count(qryProcedureCode.[Procedure Code]) AS [CountOfProcedure Code], qryProcedureCode.[Code 3]
GROUP BY qryProcedureCode.[Code 3]
HAVING (((qryProcedureCode.[Code 3])="2"));
The only way that I know of is to design the table structure to reflect the results in a horizontal fashion ... BTW, just showing a report doesn't impart the tricks used to generate it ... Generally you can't do this if the # of columns is unknown but, if they are finite then you can plan for a MAX and proceed accordingly ... Another thought would be for a subreport that does the same thing ...
The column is finite. I did some searching on google and someone did much about the subreports. I not very familiar with the subreport. I have try it though but I can't seem to display it horizontally. How do you go about doing it?
Originally posted by M Owen
DOH! Try a tabular subreport ... or a crosstab report ... I've not used them myself but they can be done ...
I have try it and it won't work for me. I'm doing VBA now, I think this is the best way to get what I want exactly but I can't connect to the query via VBA recordset method.
Dim conndb as ADODB.Connection
Dim rsqryProCode as ADODB.Recordset
Set conndb = CurrentProject.Connection
Set rsqryProCode = New ADODB.Recoredset
rsqryProCode.Open "qryProcedureCode", conndb
The error is" Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
I can't see what the problem is with this connection code. I manually click on the query and it work but the VBA code doesn't. I have been using this for a half a year now and I cna't what wrong with it.
Originally posted by M Owen
.Execute returns an optional "Records Affected" recordset as a result of running an SQL action query.
Sorry, i 'm still confuse with this. CAn you write me a sample code for it. conndb.Execute(CommandText as String, [RecordsAffected], [Options as Long = -1]) as Recordset <----what do i have to put in here?
Well, I have gone a different route. I would have a column for each ProcedureCode in the query like iif([Code 3] = 5), 1, 0), then I would add this up. HOpe this make sense to anyone is reading this and interested in doing this.