RunSQL (insert into... ) HELP

Kevinsharrison · April 19th, 2005, 05:27 PM

For some reason this statement is not can you see any reason why

DoCmd.RunSQL ("insert into broadcast_orders (stn_call, start_date, " _
        & "end_date, total_weeks, employee, client, WEMG_rate, spotperweek, date, " _
        & "order_num) value ('" & lstAllOwner.ItemData(i) & "" _
        & "',#" & datestart & "#,#" & dateend & "#,'" & txtTotWeeks & "" _
        & "','" & cboemployee & "','" & txtclient & "','" & txtRate & "" _
        & "','" & txtspotwk & "',#" & txttodaydate & "#,'" & txtordernumb & "')")

I would appreciate any help

Thanks
Kevin

rjweers · April 20th, 2005, 09:50 AM

Several things...

You are using "Date" as a field name (after spotperweek). If your table truly has a field called "Date" you will have to put [] around the field like

Code:

spotperweek, [date],

Frankly, I recommend you rename the field.

You need to change "value" to "Select".

You need to drop the parenthesis around the Select. So, after you change "value" to "Select", remove the next ( and remove the ) inside the quote on the last line.

This isn't causing the problem, but the 3rd, 4th and 5th lines don't need the

Code:

& ""

at the end of the line because you put the close quote for your text on the next line.

To debug this yourself, open the query builder and paste the statement. Remove all of the VB syntax (the quotes on the strings, the ampersands and the underscores). Change the the field names to sample values. E.g.

Code:

'" & txtspotwk & "'

becomes

Code:

'spotwksample'

Then try to view the query.

If I was writing this, it would be like:

Code:

Dim strSQL as string

strSQL = "insert into broadcast_orders (stn_call, start_date, " _
        & "end_date, total_weeks, employee, client, WEMG_rate, spotperweek, " _
        & "abetternamedfieldfordate, " _
        & "order_num) Select '" & lstAllOwner.ItemData(i) & "', " _
        & "#" & datestart & "#, #" & dateend & "#, '" & txtTotWeeks & "', " _
        & "'" & cboemployee & "', '" & txtclient & "', '" & txtRate & "', " _
        & "'" & txtspotwk & "', #" & txttodaydate & "#, '" & txtordernumb & "'")
Debug.print strSQL
DoCmd.RunSQL strSQL

Notice, by building the string before the RunSQL, I can use "Debug.Print" to see the actual SQL statement. I can then take that SQL statement to query builder and find out what is wrong. When the code is correct, I remove (or comment) the "Debug.Print".

NOTE: Beware of your text fields having immbedded quotes (") or apostrophies ('). This will hose your SQL statement. If that's a possibility, you should open an RecordSet for append and set the fields one at a time.

Give the man a fish, he eats for a day. Teach the man to fish...

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Kevinsharrison · April 20th, 2005, 10:01 AM

Thanks for the info rjweers. I am a new database builder so any info is good. I know that i don't know everything.

I think that i will try something like that. I finally got it to work.

I apreciate the help and the knowledge.

Kevin

BrianWren · April 20th, 2005, 02:21 PM

Randall,

Code:

INSERT INTO tblTarget (      fld1,       fld2,  num1)
VALUES                ('Text one', 'Text two', 12345)

is valid syntax.

You would replace VALUE with SELECT (and remove the parens) if the values to go into the target table are coming from a select query:

Code:

INSERT INTO tblTarget ( fld1, fld2, num1)
  SELECT fldA, fldB, numA
  FROM   tblSrc
  WHERE  fldTest='Test Value'

(Note that the field count, and the fieldsâ types must match, or conversion functions of the database system must be employed [TODATE(), TOCHAR(), etc.].)

But you are spot-on about the field name âDateâ being problematic, and a prime candidate for a renaming.

Brian

rjweers · April 26th, 2005, 07:45 AM

Thanks Brian. I suppose the trouble I had after correcting the original query in Query Builder was that it was "VALUE" not "VALUES". Since I often use Query Builder to get my SQL Syntax correct, it uses SELECT. I guess I knew there was a different syntax. I just never bothered to check into. Hey, if it works, use it!

BrianWren · April 26th, 2005, 11:17 AM

Yeah, query builder is the greatest!

For instance, I can never remember how to specify finding duplicates, but query builder doesn't forget.

I often use query builder to get to the syntax that I wind up using in Oracle!