[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
>

__._,_.___

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

Messages in this topic (7)

Recent Activity:

• New Members 7
• New Files 3

Visit Your Group

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

.

__,_._,___