Finding error in query vba code

[George]

Good Day Everyone.

I am trying to create a query Using VBA code but it is failing. Strangely, thought, the code used in the VBA module was cut and pasted from an actual query where it worked, but yet it wouldn't work when placed in the VBA code.

Here is the code:

 Public Sub IncomeStatementSummed(ByVal Period As String)Dim db As DAO.Database
    Set db = CurrentDb
    Dim qdf As DAO.QueryDef
    Dim newSQL As String
    
    IncomeStatementDetailed (Period)
    
    On Error Resume Next
    
    DoCmd.DeleteObject acQuery, "IncomeStatementSummed"
    On Error GoTo 0


    newSQL = "SELECT    DatePart('yyyy',[Journal Date]) AS [years] "
        
    Set qdf = db.CreateQueryDef("IncomeStatementSummed", newSQL)


End Sub

The Error message is: The select statement includes a reserved word or an argument name that is missed spelled or missing, or the punctuation is incorrect.

Would be grateful for some assistance.

[Micron]

Go back to the query that worked and check the sql (sql view). I think you missed some of it - there is no FROM clause. Also, [Journal Date] may be part of the query's fields but once you migrate the sql over to vba, it has no exposure there. It needs to be a form control reference or a variable. Also would advise to step through the code and report which line the error is raised on, just in case it has anyting to do with the function you're calling: IncomeStatementDetailed.

[George]

Thank for your response Micron.

I did relook my Code and there was an error. So it's working now.

Thanks

[Micron]

Glad you solved it!

[ssanfu]

You should really add two lines:

     'Clean up
      Set db = Nothing     '<<-- Add
      Set qdf = Nothing   '<<-- Add

End Sub