Lengthy SQL String In VBA (Requesting Help With Line Continuation and Concatenation)

[Don]

Hi,

I have an SQL string that I'm trying to code into VBA and it's giving
me trouble. I tried to use line continuation and concatenation as best
I can to make it work. However, I'm stuck. I know that there are
limitations to the length of the string but I can't break it up
properly. I'm also thinking that I need to use multiples occurrences of
strsql to do so but I'm not sure how.

Can someone please tell me how to break this string up?

This is what I have right now (I know that wrapping is going to skew
it):

Dim strSQL As String
strsql = "SELECT * FROM "&InventorySour ce&""&_
"WHERE(((["&InventorySour ce&"].NRMU_NO_1=Form s.frmMainSearch .NRMU_NO_1)
Or (Forms.frmMainS earch.NRMU_NO_1 ) Is Null)"&_
"And ((["&InventorySour ce&"].USETYPE=Forms. frmMainSearch.U SETYPE) Or
(Forms.frmMainS earch.USETYPE) Is Null)"&_
"And ((["&InventorySour ce&"].LANDCOVER=Form s.frmMainSearch .LANDCOVER)
Or (Forms.frmMainS earch.LANDCOVER ) Is Null)"&_
"And ((["&InventorySour ce&"].DOM1=Forms.frm MainSearch.DOM1 ) Or
(Forms.frmMainS earch.DOM1) Is Null)"&_
"And ((["&InventorySour ce&"].DOM2=Forms.frm MainSearch.DOM2 ) Or
(Forms.frmMainS earch.DOM2) Is Null)"&_
"And ((["&InventorySour ce&"].DOM3=Forms.frm MainSearch.DOM3 ) Or
(Forms.frmMainS earch.DOM3) Is Null)"&_
"And ((["&InventorySour ce&"].AGE2SP1=Forms. frmMainSearch.A GE2SP1) Or
(Forms.frmMainS earch.AGE2SP1) Is Null)"&_
"And ((["&InventorySour ce&"].AGE2SP2=Forms. frmMainSearch.A GE2SP2) Or
(Forms.frmMainS earch.AGE2SP2) Is Null)"&_
"And ((["&InventorySour ce&"].AGE2SP3=Forms. frmMainSearch.A GE2SP3) Or
(Forms.frmMainS earch.AGE2SP3) Is Null)"&_
"And ((["&InventorySour ce&"].MGTCON=Forms.f rmMainSearch.MG TCON) Or
(Forms.frmMainS earch.MGTCON) Is Null)"&_
"And ((["&InventorySour ce&"].MGTACT=Forms.f rmMainSearch.MG TACT) Or
(Forms.frmMainS earch.MGTACT) Is Null)"&_
"And ((["&InventorySour ce&"].ADDINFO=Forms. frmMainSearch.A DDINFO) Or
(Forms.frmMainS earch.ADDINFO) Is Null)"&_
"And ((["&InventorySour ce&"].BASAL=Forms.fr mMainSearch.BAS AL) Or
(Forms.frmMainS earch.BASAL) Is Null)"&_
"And ((["&InventorySour ce&"].DIAM=Forms.frm MainSearch.DIAM ) Or
(Forms.frmMainS earch.DIAM) Is Null)"&_
"And ((["&InventorySour ce&"].REGEN=Forms.fr mMainSearch.REG EN) Or
(Forms.frmMainS earch.REGEN) Is Null)"&_
"And ((["&InventorySour ce&"].EUAGED=Forms.f rmMainSearch.EU AGED) Or
(Forms.frmMainS earch.EUAGED) Is Null)"&_
"And ((["&InventorySour ce&"].MUD=Forms.frmM ainSearch.MUD) Or
(Forms.frmMainS earch.MUD) Is Null)"&_
"And ((["&InventorySour ce&"].METAL=Forms.fr mMainSearch.MET AL) Or
(Forms.frmMainS earch.METAL) Is Null)"&_
"And ((Forms.frmMain Search.STARTDAT E Is Null) Or
(Forms.frmMainS earch.ENDDATE Is Null)"&_
"Or (["&InventorySour ce&"].DATE Between Forms.frmMainSe arch.STARTDATE
And Forms.frmMainSe arch.ENDDATE))" &_
"And ((Forms.frmMain Search.MINACR Is Null) Or
(Forms.frmMainS earch.MAXACR Is Null)"&_
"Or (["&InventorySour ce&"].ACREAGE Between Forms.frmMainSe arch.MINACR
And Forms.frmMainSe arch.MAXACR))"& _
"And ((["&InventorySour ce&"].UpWet=Forms.fr mMainSearch.UPW ET) Or
(Forms.frmMainS earch.UPWET Is Null)));"

Thanks in advance for any input!

Don

[Kc-Mass]

Don

What does this piece of your query , ["&InventorySour ce&"] , refer to ? What
is the purpose of the ampersands (&) used in that?
I don't think length is the problem. In the QBE panel, SQL View I think
queries are limited to 254 characters. In VBA code I believe
the character limit of an SQL string is 32,000 characters.
"Don" <dk*****@twcny. rr.comwrote in message
news:11******** *************@m 73g2000cwd.goog legroups.com...

Hi,

I have an SQL string that I'm trying to code into VBA and it's giving
me trouble. I tried to use line continuation and concatenation as best
I can to make it work. However, I'm stuck. I know that there are
limitations to the length of the string but I can't break it up
properly. I'm also thinking that I need to use multiples occurrences of
strsql to do so but I'm not sure how.

Can someone please tell me how to break this string up?

This is what I have right now (I know that wrapping is going to skew
it):

Dim strSQL As String
strsql = "SELECT * FROM "&InventorySour ce&""&_
"WHERE(((["&InventorySour ce&"].NRMU_NO_1=Form s.frmMainSearch .NRMU_NO_1)
Or (Forms.frmMainS earch.NRMU_NO_1 ) Is Null)"&_
"And ((["&InventorySour ce&"].USETYPE=Forms. frmMainSearch.U SETYPE) Or
(Forms.frmMainS earch.USETYPE) Is Null)"&_

[rkc]

Don wrote:

Hi,

I have an SQL string that I'm trying to code into VBA and it's giving
me trouble.

Store the beast in a table and read it into the variable.

[Don]

Sorry. It must be lucky for me to post to these groups. Everytime that
I do, I figure out a solution. Again, I apologize for using up the
bandwidth.

Don
Don wrote:

Hi,

I have an SQL string that I'm trying to code into VBA and it's giving
me trouble. I tried to use line continuation and concatenation as best
I can to make it work. However, I'm stuck. I know that there are
limitations to the length of the string but I can't break it up
properly. I'm also thinking that I need to use multiples occurrences of
strsql to do so but I'm not sure how.

Can someone please tell me how to break this string up?

This is what I have right now (I know that wrapping is going to skew
it):

Dim strSQL As String
strsql = "SELECT * FROM "&InventorySour ce&""&_
"WHERE(((["&InventorySour ce&"].NRMU_NO_1=Form s.frmMainSearch .NRMU_NO_1)
Or (Forms.frmMainS earch.NRMU_NO_1 ) Is Null)"&_
"And ((["&InventorySour ce&"].USETYPE=Forms. frmMainSearch.U SETYPE) Or
(Forms.frmMainS earch.USETYPE) Is Null)"&_
"And ((["&InventorySour ce&"].LANDCOVER=Form s.frmMainSearch .LANDCOVER)
Or (Forms.frmMainS earch.LANDCOVER ) Is Null)"&_
"And ((["&InventorySour ce&"].DOM1=Forms.frm MainSearch.DOM1 ) Or
(Forms.frmMainS earch.DOM1) Is Null)"&_
"And ((["&InventorySour ce&"].DOM2=Forms.frm MainSearch.DOM2 ) Or
(Forms.frmMainS earch.DOM2) Is Null)"&_
"And ((["&InventorySour ce&"].DOM3=Forms.frm MainSearch.DOM3 ) Or
(Forms.frmMainS earch.DOM3) Is Null)"&_
"And ((["&InventorySour ce&"].AGE2SP1=Forms. frmMainSearch.A GE2SP1) Or
(Forms.frmMainS earch.AGE2SP1) Is Null)"&_
"And ((["&InventorySour ce&"].AGE2SP2=Forms. frmMainSearch.A GE2SP2) Or
(Forms.frmMainS earch.AGE2SP2) Is Null)"&_
"And ((["&InventorySour ce&"].AGE2SP3=Forms. frmMainSearch.A GE2SP3) Or
(Forms.frmMainS earch.AGE2SP3) Is Null)"&_
"And ((["&InventorySour ce&"].MGTCON=Forms.f rmMainSearch.MG TCON) Or
(Forms.frmMainS earch.MGTCON) Is Null)"&_
"And ((["&InventorySour ce&"].MGTACT=Forms.f rmMainSearch.MG TACT) Or
(Forms.frmMainS earch.MGTACT) Is Null)"&_
"And ((["&InventorySour ce&"].ADDINFO=Forms. frmMainSearch.A DDINFO) Or
(Forms.frmMainS earch.ADDINFO) Is Null)"&_
"And ((["&InventorySour ce&"].BASAL=Forms.fr mMainSearch.BAS AL) Or
(Forms.frmMainS earch.BASAL) Is Null)"&_
"And ((["&InventorySour ce&"].DIAM=Forms.frm MainSearch.DIAM ) Or
(Forms.frmMainS earch.DIAM) Is Null)"&_
"And ((["&InventorySour ce&"].REGEN=Forms.fr mMainSearch.REG EN) Or
(Forms.frmMainS earch.REGEN) Is Null)"&_
"And ((["&InventorySour ce&"].EUAGED=Forms.f rmMainSearch.EU AGED) Or
(Forms.frmMainS earch.EUAGED) Is Null)"&_
"And ((["&InventorySour ce&"].MUD=Forms.frmM ainSearch.MUD) Or
(Forms.frmMainS earch.MUD) Is Null)"&_
"And ((["&InventorySour ce&"].METAL=Forms.fr mMainSearch.MET AL) Or
(Forms.frmMainS earch.METAL) Is Null)"&_
"And ((Forms.frmMain Search.STARTDAT E Is Null) Or
(Forms.frmMainS earch.ENDDATE Is Null)"&_
"Or (["&InventorySour ce&"].DATE Between Forms.frmMainSe arch.STARTDATE
And Forms.frmMainSe arch.ENDDATE))" &_
"And ((Forms.frmMain Search.MINACR Is Null) Or
(Forms.frmMainS earch.MAXACR Is Null)"&_
"Or (["&InventorySour ce&"].ACREAGE Between Forms.frmMainSe arch.MINACR
And Forms.frmMainSe arch.MAXACR))"& _
"And ((["&InventorySour ce&"].UpWet=Forms.fr mMainSearch.UPW ET) Or
(Forms.frmMainS earch.UPWET Is Null)));"

Thanks in advance for any input!

Don

[Don]

Sorry. It must be lucky for me to post to these groups. Everytime that
I do, I figure out a solution. Again, I apologize for using up the
bandwidth.

Don
Don wrote:

Hi,

I have an SQL string that I'm trying to code into VBA and it's giving
me trouble. I tried to use line continuation and concatenation as best
I can to make it work. However, I'm stuck. I know that there are
limitations to the length of the string but I can't break it up
properly. I'm also thinking that I need to use multiples occurrences of
strsql to do so but I'm not sure how.

Can someone please tell me how to break this string up?

This is what I have right now (I know that wrapping is going to skew
it):

Dim strSQL As String
strsql = "SELECT * FROM "&InventorySour ce&""&_
"WHERE(((["&InventorySour ce&"].NRMU_NO_1=Form s.frmMainSearch .NRMU_NO_1)
Or (Forms.frmMainS earch.NRMU_NO_1 ) Is Null)"&_
"And ((["&InventorySour ce&"].USETYPE=Forms. frmMainSearch.U SETYPE) Or
(Forms.frmMainS earch.USETYPE) Is Null)"&_
"And ((["&InventorySour ce&"].LANDCOVER=Form s.frmMainSearch .LANDCOVER)
Or (Forms.frmMainS earch.LANDCOVER ) Is Null)"&_
"And ((["&InventorySour ce&"].DOM1=Forms.frm MainSearch.DOM1 ) Or
(Forms.frmMainS earch.DOM1) Is Null)"&_
"And ((["&InventorySour ce&"].DOM2=Forms.frm MainSearch.DOM2 ) Or
(Forms.frmMainS earch.DOM2) Is Null)"&_
"And ((["&InventorySour ce&"].DOM3=Forms.frm MainSearch.DOM3 ) Or
(Forms.frmMainS earch.DOM3) Is Null)"&_
"And ((["&InventorySour ce&"].AGE2SP1=Forms. frmMainSearch.A GE2SP1) Or
(Forms.frmMainS earch.AGE2SP1) Is Null)"&_
"And ((["&InventorySour ce&"].AGE2SP2=Forms. frmMainSearch.A GE2SP2) Or
(Forms.frmMainS earch.AGE2SP2) Is Null)"&_
"And ((["&InventorySour ce&"].AGE2SP3=Forms. frmMainSearch.A GE2SP3) Or
(Forms.frmMainS earch.AGE2SP3) Is Null)"&_
"And ((["&InventorySour ce&"].MGTCON=Forms.f rmMainSearch.MG TCON) Or
(Forms.frmMainS earch.MGTCON) Is Null)"&_
"And ((["&InventorySour ce&"].MGTACT=Forms.f rmMainSearch.MG TACT) Or
(Forms.frmMainS earch.MGTACT) Is Null)"&_
"And ((["&InventorySour ce&"].ADDINFO=Forms. frmMainSearch.A DDINFO) Or
(Forms.frmMainS earch.ADDINFO) Is Null)"&_
"And ((["&InventorySour ce&"].BASAL=Forms.fr mMainSearch.BAS AL) Or
(Forms.frmMainS earch.BASAL) Is Null)"&_
"And ((["&InventorySour ce&"].DIAM=Forms.frm MainSearch.DIAM ) Or
(Forms.frmMainS earch.DIAM) Is Null)"&_
"And ((["&InventorySour ce&"].REGEN=Forms.fr mMainSearch.REG EN) Or
(Forms.frmMainS earch.REGEN) Is Null)"&_
"And ((["&InventorySour ce&"].EUAGED=Forms.f rmMainSearch.EU AGED) Or
(Forms.frmMainS earch.EUAGED) Is Null)"&_
"And ((["&InventorySour ce&"].MUD=Forms.frmM ainSearch.MUD) Or
(Forms.frmMainS earch.MUD) Is Null)"&_
"And ((["&InventorySour ce&"].METAL=Forms.fr mMainSearch.MET AL) Or
(Forms.frmMainS earch.METAL) Is Null)"&_
"And ((Forms.frmMain Search.STARTDAT E Is Null) Or
(Forms.frmMainS earch.ENDDATE Is Null)"&_
"Or (["&InventorySour ce&"].DATE Between Forms.frmMainSe arch.STARTDATE
And Forms.frmMainSe arch.ENDDATE))" &_
"And ((Forms.frmMain Search.MINACR Is Null) Or
(Forms.frmMainS earch.MAXACR Is Null)"&_
"Or (["&InventorySour ce&"].ACREAGE Between Forms.frmMainSe arch.MINACR
And Forms.frmMainSe arch.MAXACR))"& _
"And ((["&InventorySour ce&"].UpWet=Forms.fr mMainSearch.UPW ET) Or
(Forms.frmMainS earch.UPWET Is Null)));"

Thanks in advance for any input!

Don

[Rich P]

Rather than writing your sql like this:

"Select * from tbl1 " & _
"Where ..."

use a string variable like this:

Dim strSql As String

StrSql = "Select * from tbl1 "
StrSql = StrSql & "Where .... "
StrSql = StrSql & "... "
...

I used to use the _ (underscore) method , but if you ever needed to
modify something or check for a problem in the string - debugging - it
was a real pain. By using the string Variable method, you can easily
add/exclude stuff on the string. In VB.Net it is even eaiser

strSql = "Select * from tbl1 "
strSql += "WHere ... "
strSql += "..... "

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Tim Marshall]

Don wrote:

I have an SQL string that I'm trying to code into VBA and it's giving
me trouble. I tried to use line continuation and concatenation as best
I can to make it work. However, I'm stuck. I know that there are
limitations to the length of the string but I can't break it up
properly. I'm also thinking that I need to use multiples occurrences of
strsql to do so but I'm not sure how.

Can someone please tell me how to break this string up?

There's a limit to the number of line continuations you can have.
However, a string variable is quite large and this doesn't look like
it's anywhere near that value (my Oracle statements on a nightmare
schema exceed 120k characters - too big for an SQL statement, but they
can easily be stored in a string variable.).

Just break it up where you have the line breaks. I also throw in vbcrlf
to make things a bit more readable if I want to debug.print it, but
these are optional (though Oracle - I know you're using Jet - gets mad
at me for lengthy lines without a break):

strsql = "SELECT * FROM "&InventorySour ce&"" & vbcrlf

strsql = strsql &
"WHERE(((["&InventorySour ce&"].NRMU_NO_1=Form s.frmMainSearch .NRMU_NO_1)
Or (Forms.frmMainS earch.NRMU_NO_1 ) Is Null)" & vbcrlf

etc, etc

BTW, the clause:

(Forms.frmMainS earch.NRMU_NO_1 ) Is Null

doesn't make sense to me. You're referring to criteria for a form
control and not a table field from whatever table is specified by
InventorySource . Unless NRMU_NO_1 is a listing of actual field names.
I also wouldn't construct the dynamic SQL this way - instead of using
form references, I'd take the value of the control of the form and plonk
it right into the SQL statement.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Don wrote:

Sorry. It must be lucky for me to post to these groups. Everytime that
I do, I figure out a solution. Again, I apologize for using up the
bandwidth.

I find the same thing. Very often when we ask a question on a NG or in
a person to person situation, we end up thinking about the problem in
just a little more different way in order to try and express the issue
to others. And often that's enough to spark a Eureka moment. 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David W. Fenton]

"Kc-Mass" <co********@com cast.netwrote in
news:v7******** *************** *******@comcast .com:

What does this piece of your query , ["&InventorySour ce&"] , refer
to ? What is the purpose of the ampersands (&) used in that?
I don't think length is the problem. In the QBE panel, SQL View I
think queries are limited to 254 characters. In VBA code I
believe the character limit of an SQL string is 32,000 characters.

That's not true about a saved query. I have one query over 11,000
characters in length (yes, it's a horror show; it was even longer
before I converted some of it to user-defined functions). I think
the length limitation is the same.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Tim Marshall <TI****@PurpleP andaChasers.Moe rtheriumwrote in
news:ef******** **@coranto.ucs. mun.ca:

Don wrote:

>Sorry. It must be lucky for me to post to these groups. Everytime
that I do, I figure out a solution. Again, I apologize for using
up the bandwidth.

I find the same thing. Very often when we ask a question on a NG
or in a person to person situation, we end up thinking about the
problem in just a little more different way in order to try and
express the issue to others. And often that's enough to spark a
Eureka moment. 8)

I start about 3 times as many posts as actually end up getting
posted, because in the process of putting it down in writing, I
solve it, because explaining it to someone else makes me think about
it differently and come up with the solution.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/