[MS_AccessPros] Re: Insert Into statement getting syntax error

 

That was it! Thanks soooooo much for sticking with me! Connie

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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 (9)

Recent Activity:

• New Members 6

Visit Your Group

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

.

__,_._,___