RE: [MS_AccessPros] Re: Insert Into statement getting syntax error

 

Connie-

The only thing that comes to mind is perhaps Note is a reserved word. Try
enclosing Note in brackets: [Note]

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Boston, MA)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Thursday, March 01, 2012 2:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Insert Into statement getting syntax error

John,

I created a new query with the results for SQL? in the Immediate Window. When I
try to switch to Design view I get an error message: Syntax error in INSERT INTO
statement. When I click on the OK button, Notes is highlighted.

I doublechecked the table ListingsMiscCat to make sure I'm spelling Note the
same way and it's correct.

Here's what strSQL was (as I'm sure you know :-) ):
INSERT INTO ListingsMiscCat (ListID, MiscCatID, MiscCatDate, Note) VALUES (1840,
5, #12/9/2010#, "ListID = 132")

Thanks a ZILLION!
Connie

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Connie-
>
> What is the text of the message with the 3134 error?
>
> Try this:
>
> Dim strSQL As String
>
> strSQL = "INSERT INTO ListingsMiscCat " & _
> "(ListID, MiscCatID, MiscCatDate, Note) " & _
> "VALUES (" & Me.ReListID & ", 5, #" & Me.ListDate & _
> "#, ""ListID = " & Me.ListID & """)"
> Debug.Print strSQL
> CurrentDb.Execute strSQL, dbFailOnError
>
> When that fails, go to the Immediate Window. Copy the SQL that was dumped out
> to the clipboard, then start a new query, switch to SQL view, paste the SQL,
> then switch to Design view. If that produces no error, try to run the query
and
> see if you get a more explicit error.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Kirkland, WA)
>
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> Sent: Tuesday, February 28, 2012 12:12 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Insert Into statement getting syntax error
>
> John,
>
> Thanks for the example of the Immediate window with an INSERT statement. I
used
> your solution and still am getting a 3134 syntax error which debugs to the
> insert statement. I checked and the Note field is a text field limited to 100
> characters. When I delete the Note portion from the Insert Into statement
> everything works. Crazy!
>
> The Immediate window for the INSERT statement looks good:
> INSERT INTO ListingsMiscCat (ListID, MiscCatID, MiscCatDate, Note) VALUES
(1840,
> 5, #12/9/2010#, "ListID = 132")
>
> Here's all of the code for this procedure:
> Private Sub cmdSELECT_Click()
> On Error GoTo Proc_Err
> 'Enter older listing date as the one to use for DOM calculations
> 'Create a new record in ListingsMiscCat table
>
> CurrentDb.Execute "INSERT INTO ListingsMiscCat " & _
> "(ListID, MiscCatID, MiscCatDate, Note) " & _
> "VALUES (" & Me.ReListID & ", 5, #" & Me.ListDate & _
> "#, ""ListID = " & Me.ListID & """)", dbFailOnError
>
> 'Close Form
> DoCmd.Close acForm, "RelistedProperty", acSaveNo
>
> Proc_Exit:
> Exit Sub ' or Exit Function
> Proc_Err:
> Msgbox Err.Description, , _
> "ERROR " & Err.Number _
> & " cmdSELECT_Click"
> Resume Proc_Exit
> Resume
> End Sub
>
> Thanks for all your help!
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Connie-
> >
> > The Immediate Window seems to like it as is. I tried this:
> >
> > ?"INSERT INTO ListingsMiscCat (Note) " & _
> > "VALUES (""ListID = " & 999 & """)"
> > INSERT INTO ListingsMiscCat (Note) VALUES ("ListID = 999")
> >
> > I suspect that won't run by itself because the new row is potentially
missing
> > required values for ListID, MiscCatID, or MiscCatDate.
> >
> > Here's my solution again with better line breaks:
> >
> > CurrentDb.Execute "INSERT INTO ListingsMiscCat " & _
> > "(ListID, MiscCatID, MiscCatDate, Note) " & _
> > "VALUES (" & Me.ReListID & ", 5, #" & Me.ListDate & _
> > "#, ""ListID = " & Me.ListID & """)", dbFailOnError
> >
> > Testing in the Immediate Window, I get:
> >
> > ?"INSERT INTO ListingsMiscCat " & _
> > "(ListID, MiscCatID, MiscCatDate, Note) " & _
> > "VALUES (" & 1 & ", 5, #" & Date() & _
> > "#, ""ListID = " & 999 & """)"
> > INSERT INTO ListingsMiscCat (ListID, MiscCatID, MiscCatDate, Note) VALUES
(1,
> 5,
> > #2/28/2012#, "ListID = 999")
> >
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Kirkland, WA)
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > Sent: Tuesday, February 28, 2012 8:46 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Insert Into statement getting syntax error
> >
> > John,
> >
> > I'm still having trouble. Have separated out the Note portion on its
> > own--that's where the syntax error occurs. The Note field is a memo field.
> > Does that have any relevance?
> >
> > Here's what I'm using:
> > CurrentDb.Execute "INSERT INTO ListingsMiscCat (Note) " & _
> > "VALUES (""ListID = " & Me.ListID & """)", dbFailOnError
> >
> > Really appreciate the input on using the # signs.
> >
> > Connie
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
wrote:
> > >
> > > Connie-
> > >
> > > Like this:
> > >
> > > CurrentDb.Execute "INSERT INTO ListingsMiscCat (ListID, MiscCatID,
> > MiscCatDate,
> > > Note) " & _
> > > "VALUES (" & Me.ReListID & ", 5, #" & Me.ListDate & "#, ""ListID = " &
> > > Me.ListID & """)", _
> > > dbFailOnError
> > >
> > > Note that I also surrounded the date literal with #.
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Kirkland, WA)
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > > Sent: Monday, February 27, 2012 2:13 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Insert Into statement getting syntax error
> > >
> > > Hi all! Hope your day is going well. I have checked out the following
> > > statement and it's the last value that is giving me the problem. It is to
> be
> > > put into the Note and would look like: ListID = 832
> > >
> > > I've tried several different approaches and can't get it to work. My
guess
> is
> > > that I have more to learn about using quotations.
> > >
> > > CurrentDb.Execute "INSERT INTO ListingsMiscCat (ListID, MiscCatID,
> > MiscCatDate,
> > > Note) VALUES (" & Me.ReListID & ", 5, " & Me.ListDate & ", ""ListID = """
&
> > > Me.ListID & ")", dbFailOnError
> > >
> > > Thanks!
> > > Connie
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

------------------------------------

Yahoo! Groups Links

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (8)

Recent Activity:

• New Members 5
• New Files 1

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___