X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> SQL Syntax Error, Access 2016    
post Aug 25 2019, 10:03 AM

Posts: 327
Joined: 22-April 11

I have a procedure that runs OnClick of a command button on a form. It includes these two statements:

DoCmd.RunSQL "DELETE * From tbl_Trips WHERE TripID = " & TrID
DoCmd.RunSQL "DELETE * From tbl_Member-Trip WHERE TripID = " & TrID

TrID is TripID, an integer.

The first line executes correctly, the second line returns error 3131 "Syntax error in FROM clause."

Since these statements are identical except for the target table, I don't see what the error could be. I can find no errors or problems with the table itself or any data it contains.

Any suggestions?
Go to the top of the page
post Aug 25 2019, 10:06 AM

Posts: 340
Joined: 2-April 18

Try this
"DELETE * From [tbl_Member-Trip] WHERE TripID = "

Please forgive in advance my horrible English.
Go to the top of the page
post Aug 25 2019, 10:07 AM

UtterAccess Editor
Posts: 10,085
Joined: 7-December 09
From: St Augustine, FL

Try putting the table name in square brackets (it's probably picking up the - as a minus sign)

DoCmd.RunSQL "DELETE * From [tbl_Member-Trip] WHERE TripID = " & TrID

Go to the top of the page
post Aug 25 2019, 11:12 AM

Posts: 2,621
Joined: 4-February 07
From: USA, Florida, Delray Beach

IMHO, the better option is to:
CurrentDB.Execute "DELETE * From [tbl_Member<span style='color:orange'>-Trip] WHERE TripID = " & TrID, dbFailOnError

This post has been edited by ADezii: Aug 25 2019, 11:57 AM
Go to the top of the page
post Aug 25 2019, 11:52 AM

Posts: 327
Joined: 22-April 11

The hypen was the problem. I should have caught that myself - another of the many joys of aging.

Thanks, gents!
Go to the top of the page
post Aug 25 2019, 12:04 PM

UA Admin
Posts: 35,656
Joined: 20-June 02
From: Newcastle, WA


This little syntax problem is a good example of why we try so hard to encourage Access developers NOT to use non-standard characters in object names. The hyphen can be finessed with the use of square brackets. The same is true of other characters like ? or / which we sometimes see. But ultimately, we're better off just avoiding the potential problem in the first place if we can.

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Aug 25 2019, 06:11 PM

Posts: 327
Joined: 22-April 11

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    15th September 2019 - 09:11 PM