DoCmd.RunSQL strSQL - Slowly killing my brain

[panyd]

Hey, I'm trying to create a code which selects some records from one table and posts them into another. I'm a complete beginner at this but I've managed to get this far:


Private Sub Command83_Click()

Dim strSQL As String
Dim strCriteria As String


strSQL = ""
strSQL = strSQL & " INSERT INTO Table"
strSQL = strSQL & " SELECT ([Merchant Name],[Merchant Type],[Partner],[Merchant Sector])"
strSQL = strSQL & " FROM Table![Main table]"
strSQL = strSQL & " WHERE (((Table) IN ' & 1-5000 & ' AND ((Table) IN ' & 1-5000 & ')));"
DoCmd.RunSQL strSQL

End Sub

The trouble is that Access has a problem with the last line of code and I can't figure out for the life of me why this is. Does anyone have any ideas?

 

[jal]

You can use MsgBox strSQL to output your actual SQL and use control C to copy and paste it form the Msgbox into this forum.

You probably need
" WHERE [Main table].ID BETWEEN 1 AND 5000 "

 

[jal]

Also this line:

" FROM Table![Main table]"

probably should be:

" FROM [Main table] "

because VBA doesn't know all of the Access jargon (and thus doesn't use the keyword "table").

 

[Poppa Smurf]

This may help you as I had problems with the WHERE criteria when copying and pasting SQL into VBA. I had to split the where into various strings.

The "'" below is " ' " as the fields are strings.

sqlstring1 = "SELECT tbl_locations.location_direction, tbl_locations.location_name " & _
"FROM tbl_locations "
sqlstring2 = "WHERE tbl_locations.location_direction = "
sqlstring3 = "'" & [Forms]![frm_locations_add]![location_direction] & "'"
sqlstring4 = "AND tbl_locations.location_name = "
sqlstring5 = "'" & [Forms]![frm_locations_add]![location_name] & "'"
sqlstring6 = " ORDER BY tbl_locations.location_direction, tbl_locations.location_name "

 

[panyd]

Also this line:

" FROM Table![Main table]"

probably should be:

" FROM [Main table] "

because VBA doesn't know all of the Access jargon (and thus doesn't use the keyword "table").

Now that I've done that I'm getting

"runtime error = 3075
Syntax Error (missing operator) in query expression '([Merchant Name][Merchant Type][Partner][Merchant Sector])'."

Thank you both so much for your patience in helping me deal with this. I really appreciate it.

 

[panyd]

Sorry, scrap that. Now it says:

"Error - 3134
Syntax error in INSERT INTO statement"

 

[panyd]

Sorry scrap that again. Now it's telling me that I have an incomplete query clause

 

[jal]

Did you get rid of the keyword table as I suggested?

INSERT INTO Table![Merchant CCF]([Merchant

 

[jal]

Also, I asked you to output your SQL into this forum using a MsgBox (see above).

 

[panyd]

If I have the SQL as follows then I get the error message "Run-time error '3134' Syntax error in INSERT INTO statement"

INSERT INTO [Merchant CCF]([Merchant Name][Merchant Type][Partner][Merchant Sector]) SELECT ([Merchant Name,[Merchant Type],[Partner],[Merchant Sector]) FROM [Main Table] WHERE ( ([Main table].ID BETWEEN 1 AND 5000) )

If I change the syntax so that it reads:

INSERT INTO [Merchant CCF]([Merchant Name],[Merchant Type],[Partner],[Merchant Sector]) SELECT ([Merchant Name,[Merchant Type],[Partner],[Merchant Sector]) FROM [Main Table] WHERE ( ([Main table].ID BETWEEN 1 AND 5000) )

I get the error message "Run time error '3075'. Syntax error (comma) in query expression '([Merchant Name],[Merchant Type],[Partner],[Merchant Sector])'."

I definitely took on your advise and also removed the last reference to the first table as there was no join there.

 

[namliam]

1) Remove the extranious brackets,
2) keep it readable
3) add a ] after merchant name in the select (which I noticed only after doing 2)

All that turns into...

INSERT INTO [Merchant CCF]([Merchant Name],[Merchant Type],[Partner],[Merchant Sector]) 
SELECT                     [Merchant Name],[Merchant Type],[Partner],[Merchant Sector] 
FROM [Main Table] 
WHERE [Main table].ID BETWEEN 1 AND 5000

Good luck

 

[panyd]

1) Remove the extranious brackets,
2) keep it readable
3) add a ] after merchant name in the select (which I noticed only after doing 2)

All that turns into...

INSERT INTO [Merchant CCF]([Merchant Name],[Merchant Type],[Partner],[Merchant Sector]) 
SELECT                     [Merchant Name],[Merchant Type],[Partner],[Merchant Sector] 
FROM [Main Table] 
WHERE [Main table].ID BETWEEN 1 AND 5000

Good luck

It works! Please be assured that if you guys were here I would kiss you.

 

[panyd]

Is it possible for me to run more than one of these queries in a single module/button? If it is, how would I tell VBA that one query had ended and another one was going to begin?

 

[namliam]

strSQL = ""
strSQL = strSQL & " INSERT INTO Table"
strSQL = strSQL & " SELECT ([Merchant Name],[Merchant Type],[Partner],[Merchant Sector])"
strSQL = strSQL & " FROM Table![Main table]"
strSQL = strSQL & " WHERE (((Table) IN ' & 1-5000 & ' AND ((Table) IN ' & 1-5000 & ')));"
DoCmd.RunSQL strSQL

strSQL = ""
strSQL = strSQL & " INSERT INTO Table"
strSQL = strSQL & " SELECT ([Merchant Name],[Merchant Type],[Partner],[Merchant Sector])"
strSQL = strSQL & " FROM Table![Main table]"
strSQL = strSQL & " WHERE (((Table) IN ' & 1-5000 & ' AND ((Table) IN ' & 1-5000 & ')));"
DoCmd.RunSQL strSQL

strSQL = ""
strSQL = strSQL & " INSERT INTO Table"
strSQL = strSQL & " SELECT ([Merchant Name],[Merchant Type],[Partner],[Merchant Sector])"
strSQL = strSQL & " FROM Table![Main table]"
strSQL = strSQL & " WHERE (((Table) IN ' & 1-5000 & ' AND ((Table) IN ' & 1-5000 & ')));"
DoCmd.RunSQL strSQL

strSQL = ""
strSQL = strSQL & " INSERT INTO Table"
strSQL = strSQL & " SELECT ([Merchant Name],[Merchant Type],[Partner],[Merchant Sector])"
strSQL = strSQL & " FROM Table![Main table]"
strSQL = strSQL & " WHERE (((Table) IN ' & 1-5000 & ' AND ((Table) IN ' & 1-5000 & ')));"
DoCmd.RunSQL strSQL

strSQL = ""
strSQL = strSQL & " INSERT INTO Table"
strSQL = strSQL & " SELECT ([Merchant Name],[Merchant Type],[Partner],[Merchant Sector])"
strSQL = strSQL & " FROM Table![Main table]"
strSQL = strSQL & " WHERE (((Table) IN ' & 1-5000 & ' AND ((Table) IN ' & 1-5000 & ')));"
DoCmd.RunSQL strSQL
strSQL = ""
strSQL = strSQL & " INSERT INTO Table"
strSQL = strSQL & " SELECT ([Merchant Name],[Merchant Type],[Partner],[Merchant Sector])"
strSQL = strSQL & " FROM Table![Main table]"
strSQL = strSQL & " WHERE (((Table) IN ' & 1-5000 & ' AND ((Table) IN ' & 1-5000 & ')));"
DoCmd.RunSQL strSQL

will run the queries consequetively

 

[panyd]

When I do that I get the error message:

'Run-time error '3075':

Syntax error in string in query expression '[Master Query beta 1].[ID]= DATE() & " & DATE()-1;'.

Any idea why?

Thank you so much for the assistance

 

[jal]

When I do that I get the error message:

'Run-time error '3075':

Syntax error in string in query expression '[Master Query beta 1].[ID]= DATE() & " & DATE()-1;'.

Any idea why?

Thank you so much for the assistance

I'm totally lost as to what you're trying to accomplish - how can an "ID" be equal to a Date?

 

[namliam]

= date() & " & Date() - 1; ???

That just seems wrong...

If you still have no clue, post your full code....
You probably need either...
= date() "
= date() -1"
= #" & Date() - 1 & "#"

 

[Poppa Smurf]

Should ID be a date or Text or a number etc?

 

[gemma-the-husky]

here is the sensible way to do this, and inded most things

design your query in a visual environment and save it

then just do

docmd.openquery "mystoredquery"

(or currentdb.execute etc)

and look at the sql, if you are really interested.

 

[jason97m]

One issue is that when you do a SELECT statement from DoCMD.RUNSQL you must select into a new table or it will not work.