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

Selasa, 28 Februari 2012

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

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