Breaking down large VBA - SQL query code

Hi,

CurrentDb.Execute "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY], [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M) SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE], P2CSameTx65Table.CITY AS [DESTIN CITY], P2CSameTx65Table.PROVINCE AS [DESTIN PROVINCE], P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![Min]+[getDestinBeyond]![MIN] AS [MIN], [P2CSameTx65Table]![LTL]+[getDestinBeyond]![LTL] AS LTL, [P2CSameTx65Table]![500]+[getDestinBeyond]![500] AS 500, [P2CSameTx65Table]![1M]+[getDestinBeyond]![1M] AS 1M, [P2CSameTx65Table]![2M]+[getDestinBeyond]![2M] AS 2M, [P2CSameTx65Table]![5M]+[getDestinBeyond]![5M] AS 5M, [P2CSameTx65Table]![10M]+[getDestinBeyond]![10M] AS 10M, [P2CSameTx65Table]![20M]+[getDestinBeyond]![20M] AS 20M"
FROM [ZONE] INNER JOIN INTER ON ZONE.[TCOMBI] = INTER.DTCOMBO WHERE INTER.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo] & Chr(34) & " And ZONE.[ZONE] = " & Chr(34) & "LOCAL" & Chr(34) & " And INTER.[TCOMBO] Like " & Chr(34) & userinput & "*" & Chr(34) & " ORDER BY ZONE.CITY, INTER.Class", dbFailOnError

I have the above code which run over the MS VB window and into the 2nd line. How do I break it down into 2 lines? Is there is a possible way?

Thanks!
LVL 13
Shanan212Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Rey Obrero (Capricorn1)Commented:
use this formatting
Dim sSQL As String
sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY],"
sSQL = sSQL & " [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
sSQL = sSQL & " SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE],"
sSQL = sSQL & " P2CSameTx65Table.CITY AS [DESTIN CITY], P2CSameTx65Table.PROVINCE AS [DESTIN PROVINCE],"
sSQL = sSQL & " P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![Min]+[getDestinBeyond]![MIN] AS [MIN],"
sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[getDestinBeyond]![LTL] AS LTL,"
sSQL = sSQL & " [P2CSameTx65Table]![500]+[getDestinBeyond]![500] AS 500,"
sSQL = sSQL & " [P2CSameTx65Table]![1M]+[getDestinBeyond]![1M] AS 1M,"
sSQL = sSQL & " [P2CSameTx65Table]![2M]+[getDestinBeyond]![2M] AS 2M,"
sSQL = sSQL & " [P2CSameTx65Table]![5M]+[getDestinBeyond]![5M] AS 5M,"
sSQL = sSQL & " [P2CSameTx65Table]![10M]+[getDestinBeyond]![10M] AS 10M,"
sSQL = sSQL & " [P2CSameTx65Table]![20M]+[getDestinBeyond]![20M] AS 20M"
sSQL = sSQL & " FROM [ZONE] INNER JOIN INTER ON ZONE.[TCOMBI] = INTER.DTCOMBO"
sSQL = sSQL & " WHERE INTER.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo] & Chr(34) & ""
sSQL = sSQL & " And ZONE.[ZONE] = " & Chr(34) & "LOCAL" & Chr(34) & " And INTER.[TCOMBO] Like " & Chr(34) & UserInput & "*" & Chr(34) & ""
sSQL = sSQL & " ORDER BY ZONE.CITY, INTER.Class"

CurrentDb.Execute sSQL,dbfailonerror

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Dale FyeCommented:
shanan,

Best way is to create a string variable and assign the SQL to it

strSQL =  "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY], [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M) " _
            & "SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE], " _
                                 & "P2CSameTx65Table.CITY AS [DESTIN CITY], " _
                                 & "P2CSameTx65Table.PROVINCE AS [DESTIN PROVINCE], " _
                                 & "P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, " _
                                 & "[P2CSameTx65Table]![Min]+[getDestinBeyond]![MIN] AS [MIN], " _
                                 & "[P2CSameTx65Table]![LTL]+[getDestinBeyond]![LTL] AS LTL, " _
                                 & "[P2CSameTx65Table]![500]+[getDestinBeyond]![500] AS 500, " _
                                 & "[P2CSameTx65Table]![1M]+[getDestinBeyond]![1M] AS 1M, " _
                                 & "[P2CSameTx65Table]![2M]+[getDestinBeyond]![2M] AS 2M, " _
                                 & "[P2CSameTx65Table]![5M]+[getDestinBeyond]![5M] AS 5M, " _
                                 & "[P2CSameTx65Table]![10M]+[getDestinBeyond]![10M] AS 10M, " _
                                 & "[P2CSameTx65Table]![20M]+[getDestinBeyond]![20M] AS 20M " _
             & "FROM [ZONE] INNER JOIN INTER ON ZONE.[TCOMBI] = INTER.DTCOMBO " _
             & "WHERE INTER.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo] & Chr(34) _
             & " And ZONE.[ZONE] = " & Chr(34) & "LOCAL" & Chr(34) _
             & " And INTER.[TCOMBO] Like " & Chr(34) & userinput & "*" & Chr(34) _
             & " ORDER BY ZONE.CITY, INTER.Class", dbFailOnError
Currentdb.execute strsql, dbfailonerror

I think what you were looking for was the line break character (the _ at the end of each of these lines).  
0
 
Nick67Commented:
You know that you can save queries as objects, right?
Create a new query
Switch it to SQL view
Paste in just the SQL
Save and name it qryWhatever

Then in code, execute it
dim db as database
dim qdf as querydef
set db = currentdb
set qdf = db.querydefs("qryWhatever")
qdf.Execute dbFailOnError
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Shanan212Author Commented:
Thanks for both answers. I know how to run using currentdb.execute but thought there maybe a shortcut!

As for creating query, I have variables among the query.
0
 
Dale FyeCommented:
you might also want to consider that all of your computed columns look like:

[P2CSameTx65Table]![500]+[getDestinBeyond]![500]

and that syntax will return a NULL value if either of these is NULL.  To circumvent that, you could use:

NZ([P2CSameTx65Table]![500], 0)+NZ([getDestinBeyond]![500], 0)
0
 
Nick67Commented:
<As for creating query, I have variables among the query. >
And?
It's not like variables are a barrier to creating a saved query.
0
 
Shanan212Author Commented:
Well, I really didn't go into this deep.

When I created queries from query-design, I researched my options and found out that you can get a value as a criteria via 2 ways.

1. Directly calling the field:

eg: [forms][formname][field] or [table][field] - something like this

2. Calling the field via a function

function1(getfieldvalue)


Now I have the senario where I have to repeat the query for each value in another table.

So I am looping through a table (say table1) values. I don't see how I can call table1.value (at a particular moment) to query design.

I thought of creating a function which loops through table value but then that function would loop through the table for each call:

eg: function1(getvalue)

The above would run through the table and return all values.


I am sure you can assign primary keys and get related value ONLY but I really didnt want to go that far :/
0
 
Nick67Commented:
There is a third, and very important way to pass in your parameters.
You saw this earlier

dim db as database
dim qdf as querydef
set db = currentdb
set qdf = db.querydefs("qryWhatever")
qdf.Execute dbFailOnError
 

If you have parameters in the query, it becomes

dim db as database
dim qdf as querydef
set db = currentdb
set qdf = db.querydefs("qryWhatever")
'for as many parameters as you have
qdf(0) = FirstParameterValue
qdf(1) = #SomeSecondParameterValueThatIsADate#
qdf(2) = "SomeThirdParameterThatIsAString"
'and so on an so on
qdf.Execute dbFailOnError

If you are doing this over and over, you can put it inside a loop that changes the parameters and executes.

If the parameters are functions you can also use Eval() to make the parameters evaluate themselves as written in the query.
 
0
 
Shanan212Author Commented:
OW!

How would this parameter be called in criteria?


qdf(number)?

Considering number = currentvalue from a table of 'numbers'

Is this called a term? how would I go on to google further studies on this?

Thanks!

0
 
Nick67Commented:
The mechanism is straightforward.
Let's say we have the following Select query
"Select * from SomeTable where SomeID = [forms]![SomeForm]![SomeControl];"
We save the query and give it a name 'qrySomeThing'

In code, when we want to use that parameter query we do the following
dim db as database
dim rs as recordset
dim qdf as querydef
set db = currentdb
set qdf = db.querydefs("qrySomething")
'for as many parameters as you have
qdf(0) = 1234 'the value we are interested in
set rs = qdf.OpenRecordset(dbOpenDynaset)

For the qdf(0) you could also feed in a VBA variable
dim x as integer
x = 12345
qdf(0) = x

You can feed it a function
You can feed it a control value
qdf(0) = Me.SomeControl.value

Look in the Access 2003 help for QueryDef
The later versions of Access help aren't so helpful
Google up 'MS Access VBA QueryDef'

If the form that your parameters comes from you can add the following to the code snippets I have posted

dim prm as Parameter
For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
Next prm

Start poking around here
http://access.mvps.org/access/queries/qry0013.htm


0
 
Shanan212Author Commented:
Much appreciated! Thanks Nick!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.