check if query exists

[Legacy 15162]

I want to write an if statement in code that will check to see if a query exists....if it does then delete and create a new query.

stmaxdate = "SELECT tblSFT.Storeno, tblSFT.Year, tblSFT.Period, tblSFT.Week, Max(tblSFT.Report_Date) AS MaxOfReport_Date" & _
" FROM tblSFT" & _
" GROUP BY tblSFT.Storeno, tblSFT.Year, tblSFT.Period, tblSFT.Week"

CurrentDb.CreateQueryDef "tmpmaxdate", stmaxdate

 

[Norie]

You could use something like this:

Function IsQuery(qryName As String) As Boolean
' returns true if there is a query called qryName

Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb

    For Each qdf In db.QueryDefs
        IsQuery = (qdf.Name = qryName)
        If IsQuery Then Exit Function
    Next
    
End Function

 

[Stubby Holder]

or, just delete it anyway, & skip the error if it doesn't exist.

On Error Resume Next
DoCmd.DeleteObject acQuery, "tmpmaxdate"
On Error GoTo 0