INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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


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

I see a few things I would change

CODE --> vba

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= " & [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

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

(OP)
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];"

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

Quote:

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.

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

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

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

(OP)
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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close