I tried your solution but I still get error 3265: Item not found in this collection.DAO.QueryDefs ??
Code:
Public Function sPeriod(OutLet As String) As Variant
On Error GoTo Err_Handler
'Purpose: Count Distinct Month/Year in Sales Records.
'Return: Integer, or 0 if no matches.
'Arguments: None
'
'Notes: 1. Use square brackets around field/table names with spaces or odd characters.
' 2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
' 3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
' 4. Returning more than 255 characters to a recordset triggers this Access bug:
' http://allenbrowne.com/bug-16.html
Dim SalRec2 As DAO.Recordset 'Related records
Dim strSql1 As String 'SQL statement
Dim strSql2 As String 'SQL statement
Dim strOut As Variant 'Output string to concatenate to.
Dim qryDef As DAO.QueryDef
'Initialize to 0
sPeriod = 0
'Build SQL strings, and get the records.
'select Records to Count
strSql1 = "SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales WHERE Sales.[Venue] = '" & OutLet & "'"
Set qryDef = CurrentDb.QueryDefs("qrySalesRec")
qryDef.SQL = strSql1
'Count the Records
strSql2 = "SELECT Count([sMth]) FROM qrySalesRec"
Set SalRec2 = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
' Check for Count
If Not IsNull(SalRec2(1)) Then
strOut = SalRec2(1)
End If
SalRec.Close
'Return the result
sPeriod = strOut
Exit_Handler:
'Clean up
Set SalRec2 = Nothing
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description & Err.Source
Resume Exit_Handler
End Function
Your try of my original does the same thing, you didn't have the where clause on it??
Code:
strSql2 = "SELECT Count(sMth) AS MonthCount"
strSql2 = strSql2 & " FROM (SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales WHERE Sales.[Venue] = '" & OutLet & "')
AS temptable;"