Writing SQL into a module, cant get it right

[CGill]

Hello can anyone advise the correct format to write the below SQL into a VBA module. I've attempted to write as a macro and convert but this has not worked either.

INSERT INTO [L-MajorBusiness]
SELECT [Standard-Elec*tmp].*
FROM [Standard-Elec*tmp]
WHERE ((([Standard-Elec*tmp].Supplier)="L") AND (([Standard-Elec*tmp].Customer_Category)="MB" Or ([Standard-Elec*tmp].Customer_Category)="LA") AND (([Standard-Elec*tmp].[RCBA_Y/N])="NON_RCBA") AND (([Standard-Elec*tmp].Stamp)=Date())) OR ((([Standard-Elec*tmp].Supplier)="L") AND (([Standard-Elec*tmp].[RCBA_Y/N])="WITH_RCBA") AND (([Standard-Elec*tmp].Stamp)=Date()));

 

[scriverb]

SQL can't be written into a module. You can place this SQL into a queries SQL window and save the query for running later. Or, you can place this SQL, which is an ACTION query SQL into a string variable and execute the string variable with a DoCmd.RunSQL vSQLstring command.

What is is that you are trying to accomplish?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[amorous]

First thing:

Use single quotes instead of double quotes:

Use
[Standard-Elec*tmp].Supplier)='L'

Instead of

[Standard-Elec*tmp].Supplier)="L"

-VJ

 

[CGill]

The quotes has no bearing it runs with "" or ''.

Thanks Scriverb, for you answer what I trying to do is streamline an update process. I have 104 updates with varying conditions to execute, Its alot easy to write them in SQL could you advise how to do the latter part of your reply i.e excute via DoCmd.

Cheers

CG

 

[scriverb]

Try this:

Dim vSQLstring as String
vSQLstring = "INSERT INTO [L-MajorBusiness] " & _
"SELECT [Standard-Elec*tmp].* FROM [Standard-Elec*tmp] " & _
"WHERE ((([Standard-Elec*tmp].Supplier)='L') AND " & _ "(([Standard-Elec*tmp].Customer_Category)='MB' Or " & _ "([Standard-Elec*tmp].Customer_Category)='LA') AND " & _ "(([Standard-Elec*tmp].[RCBA_Y/N])='NON_RCBA') AND " & _ "(([Standard-Elec*tmp].Stamp)=Date())) OR  " & _ "((([Standard-Elec*tmp].Supplier)='L') AND  " & _ "(([Standard-Elec*tmp].[RCBA_Y/N])='WITH_RCBA') AND  " & _ "(([Standard-Elec*tmp].Stamp)=Date()));"
DoCmd.SetWarnings False
DoCmd.RunSQL vSQLstring
DoCmd.SetWarnings True

This above code create a SQL string with the code provided, with the use of single quotes within the string. This must be done because we are using double-quotes to build the string and ACCESS would get confused if we inserted within the string the doubles. Also I have used the ampersand(&) to hook or cancatenate the strings together the use of the Underscore(_) is just a line continuation character so all of the code with an underscore character is interepreted as all being on the same line.

Let me know if this helps you or not.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[CGill]

Thanks Bob,

I've place the code into a module but the module highlights the section below and states syntax error. Any ideas ??

vSQLstring = "INSERT INTO [L-MajorBusiness] " & _
"SELECT [Standard-Elec*tmp].* FROM [Standard-Elec*tmp] " & _
"WHERE ((([Standard-Elec*tmp].Supplier)='L') AND " & _ "(([Standard-Elec*tmp].Customer_Category)='MB' Or " & _ "([Standard-Elec*tmp].Customer_Category)='LA') AND " & _ "(([Standard-Elec*tmp].[RCBA_Y/N])='NON_RCBA') AND " & _ "(([Standard-Elec*tmp].Stamp)=Date())) OR " & _ "((([Standard-Elec*tmp].Supplier)='L') AND " & _ "(([Standard-Elec*tmp].[RCBA_Y/N])='WITH_RCBA') AND " & _ "(([Standard-Elec*tmp].Stamp)=Date()));"

 

[scriverb]

I don't see anything wrong with the statement. All you are doing at this time is creating a string and the line advances and ampersands all look good. The syntax should have nothing to do with the contents of the SQL so the only thing that is left is the variable vSQLString. Have you dimensioned the variable vSQLstring. This will cause a syntax error.

Exactly what was the ACCESS syntax error that occurred. You should always be specific with what ACCESS returns when posting an error. Those are the hints to the problem.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]