Using qdf.SQL method in Access 2007: receiving Run Time error 3141 2

[JimStrand]

Using Access 2007 and trying to recycle a method I've used for several years in prvious versions of Access.
After Updating a parameter on a main form I want to pass query logic into query named qdf and make table from the parameters in form. I am receiving Run Time error 3141: The select statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrect. Can anyone decifer the chnage in reserved words from prior versions of access that would create this error from code below? Thank you in advance.

Private Sub txtPeriodNumber_AfterUpdate()

DoCmd.SetWarnings False

Dim pmDelete As String
pmDelete = ""
pmDelete = pmDelete & "Delete * from GeneralJournalHeaderTbl;"

DoCmd.RunSQL pmDelete

Dim db As Database
Dim qdf As DAO.QueryDef
Dim tbl As TableDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qdf")

Dim intMarket As String

strProperty = Forms![checkmax]!txtPeriodNumber

Dim StrSQL As String

StrSQL = ""

StrSQL = StrSQL & " SELECT 1 AS DOCNUM, ([Period]+988) AS BATCHID, "
StrSQL = StrSQL & " [MonthName] & 'Close' AS REFERENCE, "
StrSQL = StrSQL & " Calendar.Period AS Period, "
StrSQL = StrSQL & " Calendar.Year AS Year, "
StrSQL = StrSQL & " Calendar.BeginningDate AS BeginningDate, "
StrSQL = StrSQL & " Calendar.EndingDate AS EndingDate, "
StrSQL = StrSQL & " INTO GeneralJournalHeaderTbl "
StrSQL = StrSQL & " FROM Calendar "
StrSQL = StrSQL & " WHERE (((Calendar.Period)=[Forms]![checkmax].[txtPeriodNumber]) "
StrSQL = StrSQL & " AND ((Calendar.Year)=[Forms]![checkmax].[txtYearNumber]));"

Debug.Print StrSQL

qdf.SQL = StrSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "qdf"

DoCmd.SetWarnings True

End Sub

 

[dhookom]

I see a few things I would change

StrSQL = StrSQL & " SELECT 1 AS DOCNUM, ([Period]+988) AS BATCHID, "
StrSQL = StrSQL & " [MonthName] & 'Close' AS REFERENCE, "
StrSQL = StrSQL & " Period , "               [COLOR=#4E9A06]'Why alias a field with its own name?[/color]
StrSQL = StrSQL & " [Year] , "               [COLOR=#4E9A06]'Year is a function name[/color]
StrSQL = StrSQL & " BeginningDate, "
StrSQL = StrSQL & " EndingDate  "            [COLOR=#4E9A06]'removed the comma[/color]
StrSQL = StrSQL & " INTO GeneralJournalHeaderTbl "
StrSQL = StrSQL & " FROM Calendar "
StrSQL = StrSQL & " WHERE Period= " & [Forms]![checkmax].[txtPeriodNumber]
StrSQL = StrSQL & " AND [Year]= " & [Forms]![checkmax].[txtYearNumber];"

If the code is running in the form checkmax, you can replace the Forms!checkmax with Me.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[JimStrand]

Thank you Duane!. This worked with slight mod. Not sure why the Me.[Year] wouldn't work like the [Period] syntax. You said Year is a function name but it is also driven by a value on the checkmax form. Not sure if this is related to the Me. syntax not working for the txtYearNumber parameter. Thanks again. Switzerland? I thought you were a MN guy..

StrSQL = ""

StrSQL = StrSQL & " SELECT 1 AS DOCNUM, ([Period]+988) AS BATCHID, "
StrSQL = StrSQL & " [MonthName] & 'Close' AS REFERENCE, "
StrSQL = StrSQL & " Period , " 'Why alias a field with its own name?
StrSQL = StrSQL & " Year , " 'Year is a function name
StrSQL = StrSQL & " BeginningDate, "
StrSQL = StrSQL & " EndingDate " 'removed the comma
StrSQL = StrSQL & " INTO GeneralJournalHeaderTbl "
StrSQL = StrSQL & " FROM Calendar "
StrSQL = StrSQL & " WHERE Period= " & Me.[txtPeriodNumber]
StrSQL = StrSQL & " AND Calendar.Year = Forms![checkmax].[txtYearNumber];

 

[MajP]

Not sure why the Me.[Year] wouldn't work like the [Period] syntax

If your code is run on form Checkmax, this will not work
" AND Calendar.Year = Me.[txtYearNumber];"
but this will
" AND Calendar.Year = " & Me.[txtYearNumber]
Which is what Duane was suggesting, I think

The latter would build a sql string like
Select..... And Calendar.Year = 2016

The first version would build a string like
Select....And Calendar.Year = Me.[txtYearNumber]
Which will not work because Sql strings have to fully reference
However this seems kind of silly to me.
" AND Calendar.Year = Forms![checkmax].[txtYearNumber];"
You write a Sql string in code and then still have the sql string reference a control on a form. Create the string with the literal as shown.

 

[dhookom]

MajP is correct.

I have been working in Switzerland for about nine months and with be here through June. Then back to Minnesota.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[JimStrand]

Thank you Gentleman! Revision works as described.

Enjoy your time in Switzerland Duane, I will be up on Lake Vermillion in August for my annual dose of Northland!

Jim
Bethesda, MD

 

[dhookom]

Jim,
Don't forget your mosquito lotion. Hopefully I'm back home about 4 hours south of you.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016