By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,940 Members | 1,300 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,940 IT Pros & Developers. It's quick & easy.

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

P: n/a
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 "&InventorySource&""&_
"WHERE(((["&InventorySource&"].NRMU_NO_1=Forms.frmMainSearch.NRMU_NO_1)
Or (Forms.frmMainSearch.NRMU_NO_1) Is Null)"&_
"And ((["&InventorySource&"].USETYPE=Forms.frmMainSearch.USETYPE) Or
(Forms.frmMainSearch.USETYPE) Is Null)"&_
"And ((["&InventorySource&"].LANDCOVER=Forms.frmMainSearch.LANDCOVER)
Or (Forms.frmMainSearch.LANDCOVER) Is Null)"&_
"And ((["&InventorySource&"].DOM1=Forms.frmMainSearch.DOM1) Or
(Forms.frmMainSearch.DOM1) Is Null)"&_
"And ((["&InventorySource&"].DOM2=Forms.frmMainSearch.DOM2) Or
(Forms.frmMainSearch.DOM2) Is Null)"&_
"And ((["&InventorySource&"].DOM3=Forms.frmMainSearch.DOM3) Or
(Forms.frmMainSearch.DOM3) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP1=Forms.frmMainSearch.AGE2SP1) Or
(Forms.frmMainSearch.AGE2SP1) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP2=Forms.frmMainSearch.AGE2SP2) Or
(Forms.frmMainSearch.AGE2SP2) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP3=Forms.frmMainSearch.AGE2SP3) Or
(Forms.frmMainSearch.AGE2SP3) Is Null)"&_
"And ((["&InventorySource&"].MGTCON=Forms.frmMainSearch.MGTCON) Or
(Forms.frmMainSearch.MGTCON) Is Null)"&_
"And ((["&InventorySource&"].MGTACT=Forms.frmMainSearch.MGTACT) Or
(Forms.frmMainSearch.MGTACT) Is Null)"&_
"And ((["&InventorySource&"].ADDINFO=Forms.frmMainSearch.ADDINFO) Or
(Forms.frmMainSearch.ADDINFO) Is Null)"&_
"And ((["&InventorySource&"].BASAL=Forms.frmMainSearch.BASAL) Or
(Forms.frmMainSearch.BASAL) Is Null)"&_
"And ((["&InventorySource&"].DIAM=Forms.frmMainSearch.DIAM) Or
(Forms.frmMainSearch.DIAM) Is Null)"&_
"And ((["&InventorySource&"].REGEN=Forms.frmMainSearch.REGEN) Or
(Forms.frmMainSearch.REGEN) Is Null)"&_
"And ((["&InventorySource&"].EUAGED=Forms.frmMainSearch.EUAGED) Or
(Forms.frmMainSearch.EUAGED) Is Null)"&_
"And ((["&InventorySource&"].MUD=Forms.frmMainSearch.MUD) Or
(Forms.frmMainSearch.MUD) Is Null)"&_
"And ((["&InventorySource&"].METAL=Forms.frmMainSearch.METAL) Or
(Forms.frmMainSearch.METAL) Is Null)"&_
"And ((Forms.frmMainSearch.STARTDATE Is Null) Or
(Forms.frmMainSearch.ENDDATE Is Null)"&_
"Or (["&InventorySource&"].DATE Between Forms.frmMainSearch.STARTDATE
And Forms.frmMainSearch.ENDDATE))"&_
"And ((Forms.frmMainSearch.MINACR Is Null) Or
(Forms.frmMainSearch.MAXACR Is Null)"&_
"Or (["&InventorySource&"].ACREAGE Between Forms.frmMainSearch.MINACR
And Forms.frmMainSearch.MAXACR))"&_
"And ((["&InventorySource&"].UpWet=Forms.frmMainSearch.UPWET) Or
(Forms.frmMainSearch.UPWET Is Null)));"

Thanks in advance for any input!

Don

Sep 28 '06 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Don

What does this piece of your query , ["&InventorySource&"] , 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*********************@m73g2000cwd.googlegro ups.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 "&InventorySource&""&_
"WHERE(((["&InventorySource&"].NRMU_NO_1=Forms.frmMainSearch.NRMU_NO_1)
Or (Forms.frmMainSearch.NRMU_NO_1) Is Null)"&_
"And ((["&InventorySource&"].USETYPE=Forms.frmMainSearch.USETYPE) Or
(Forms.frmMainSearch.USETYPE) Is Null)"&_

Sep 28 '06 #2

P: n/a
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.
Sep 28 '06 #3

P: n/a
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 "&InventorySource&""&_
"WHERE(((["&InventorySource&"].NRMU_NO_1=Forms.frmMainSearch.NRMU_NO_1)
Or (Forms.frmMainSearch.NRMU_NO_1) Is Null)"&_
"And ((["&InventorySource&"].USETYPE=Forms.frmMainSearch.USETYPE) Or
(Forms.frmMainSearch.USETYPE) Is Null)"&_
"And ((["&InventorySource&"].LANDCOVER=Forms.frmMainSearch.LANDCOVER)
Or (Forms.frmMainSearch.LANDCOVER) Is Null)"&_
"And ((["&InventorySource&"].DOM1=Forms.frmMainSearch.DOM1) Or
(Forms.frmMainSearch.DOM1) Is Null)"&_
"And ((["&InventorySource&"].DOM2=Forms.frmMainSearch.DOM2) Or
(Forms.frmMainSearch.DOM2) Is Null)"&_
"And ((["&InventorySource&"].DOM3=Forms.frmMainSearch.DOM3) Or
(Forms.frmMainSearch.DOM3) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP1=Forms.frmMainSearch.AGE2SP1) Or
(Forms.frmMainSearch.AGE2SP1) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP2=Forms.frmMainSearch.AGE2SP2) Or
(Forms.frmMainSearch.AGE2SP2) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP3=Forms.frmMainSearch.AGE2SP3) Or
(Forms.frmMainSearch.AGE2SP3) Is Null)"&_
"And ((["&InventorySource&"].MGTCON=Forms.frmMainSearch.MGTCON) Or
(Forms.frmMainSearch.MGTCON) Is Null)"&_
"And ((["&InventorySource&"].MGTACT=Forms.frmMainSearch.MGTACT) Or
(Forms.frmMainSearch.MGTACT) Is Null)"&_
"And ((["&InventorySource&"].ADDINFO=Forms.frmMainSearch.ADDINFO) Or
(Forms.frmMainSearch.ADDINFO) Is Null)"&_
"And ((["&InventorySource&"].BASAL=Forms.frmMainSearch.BASAL) Or
(Forms.frmMainSearch.BASAL) Is Null)"&_
"And ((["&InventorySource&"].DIAM=Forms.frmMainSearch.DIAM) Or
(Forms.frmMainSearch.DIAM) Is Null)"&_
"And ((["&InventorySource&"].REGEN=Forms.frmMainSearch.REGEN) Or
(Forms.frmMainSearch.REGEN) Is Null)"&_
"And ((["&InventorySource&"].EUAGED=Forms.frmMainSearch.EUAGED) Or
(Forms.frmMainSearch.EUAGED) Is Null)"&_
"And ((["&InventorySource&"].MUD=Forms.frmMainSearch.MUD) Or
(Forms.frmMainSearch.MUD) Is Null)"&_
"And ((["&InventorySource&"].METAL=Forms.frmMainSearch.METAL) Or
(Forms.frmMainSearch.METAL) Is Null)"&_
"And ((Forms.frmMainSearch.STARTDATE Is Null) Or
(Forms.frmMainSearch.ENDDATE Is Null)"&_
"Or (["&InventorySource&"].DATE Between Forms.frmMainSearch.STARTDATE
And Forms.frmMainSearch.ENDDATE))"&_
"And ((Forms.frmMainSearch.MINACR Is Null) Or
(Forms.frmMainSearch.MAXACR Is Null)"&_
"Or (["&InventorySource&"].ACREAGE Between Forms.frmMainSearch.MINACR
And Forms.frmMainSearch.MAXACR))"&_
"And ((["&InventorySource&"].UpWet=Forms.frmMainSearch.UPWET) Or
(Forms.frmMainSearch.UPWET Is Null)));"

Thanks in advance for any input!

Don
Sep 28 '06 #4

P: n/a
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 "&InventorySource&""&_
"WHERE(((["&InventorySource&"].NRMU_NO_1=Forms.frmMainSearch.NRMU_NO_1)
Or (Forms.frmMainSearch.NRMU_NO_1) Is Null)"&_
"And ((["&InventorySource&"].USETYPE=Forms.frmMainSearch.USETYPE) Or
(Forms.frmMainSearch.USETYPE) Is Null)"&_
"And ((["&InventorySource&"].LANDCOVER=Forms.frmMainSearch.LANDCOVER)
Or (Forms.frmMainSearch.LANDCOVER) Is Null)"&_
"And ((["&InventorySource&"].DOM1=Forms.frmMainSearch.DOM1) Or
(Forms.frmMainSearch.DOM1) Is Null)"&_
"And ((["&InventorySource&"].DOM2=Forms.frmMainSearch.DOM2) Or
(Forms.frmMainSearch.DOM2) Is Null)"&_
"And ((["&InventorySource&"].DOM3=Forms.frmMainSearch.DOM3) Or
(Forms.frmMainSearch.DOM3) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP1=Forms.frmMainSearch.AGE2SP1) Or
(Forms.frmMainSearch.AGE2SP1) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP2=Forms.frmMainSearch.AGE2SP2) Or
(Forms.frmMainSearch.AGE2SP2) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP3=Forms.frmMainSearch.AGE2SP3) Or
(Forms.frmMainSearch.AGE2SP3) Is Null)"&_
"And ((["&InventorySource&"].MGTCON=Forms.frmMainSearch.MGTCON) Or
(Forms.frmMainSearch.MGTCON) Is Null)"&_
"And ((["&InventorySource&"].MGTACT=Forms.frmMainSearch.MGTACT) Or
(Forms.frmMainSearch.MGTACT) Is Null)"&_
"And ((["&InventorySource&"].ADDINFO=Forms.frmMainSearch.ADDINFO) Or
(Forms.frmMainSearch.ADDINFO) Is Null)"&_
"And ((["&InventorySource&"].BASAL=Forms.frmMainSearch.BASAL) Or
(Forms.frmMainSearch.BASAL) Is Null)"&_
"And ((["&InventorySource&"].DIAM=Forms.frmMainSearch.DIAM) Or
(Forms.frmMainSearch.DIAM) Is Null)"&_
"And ((["&InventorySource&"].REGEN=Forms.frmMainSearch.REGEN) Or
(Forms.frmMainSearch.REGEN) Is Null)"&_
"And ((["&InventorySource&"].EUAGED=Forms.frmMainSearch.EUAGED) Or
(Forms.frmMainSearch.EUAGED) Is Null)"&_
"And ((["&InventorySource&"].MUD=Forms.frmMainSearch.MUD) Or
(Forms.frmMainSearch.MUD) Is Null)"&_
"And ((["&InventorySource&"].METAL=Forms.frmMainSearch.METAL) Or
(Forms.frmMainSearch.METAL) Is Null)"&_
"And ((Forms.frmMainSearch.STARTDATE Is Null) Or
(Forms.frmMainSearch.ENDDATE Is Null)"&_
"Or (["&InventorySource&"].DATE Between Forms.frmMainSearch.STARTDATE
And Forms.frmMainSearch.ENDDATE))"&_
"And ((Forms.frmMainSearch.MINACR Is Null) Or
(Forms.frmMainSearch.MAXACR Is Null)"&_
"Or (["&InventorySource&"].ACREAGE Between Forms.frmMainSearch.MINACR
And Forms.frmMainSearch.MAXACR))"&_
"And ((["&InventorySource&"].UpWet=Forms.frmMainSearch.UPWET) Or
(Forms.frmMainSearch.UPWET Is Null)));"

Thanks in advance for any input!

Don
Sep 28 '06 #5

P: n/a
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 ***
Sep 28 '06 #6

P: n/a
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 "&InventorySource&"" & vbcrlf

strsql = strsql &
"WHERE(((["&InventorySource&"].NRMU_NO_1=Forms.frmMainSearch.NRMU_NO_1)
Or (Forms.frmMainSearch.NRMU_NO_1) Is Null)" & vbcrlf

etc, etc

BTW, the clause:

(Forms.frmMainSearch.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
Sep 28 '06 #7

P: n/a
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
Sep 28 '06 #8

P: n/a
"Kc-Mass" <co********@comcast.netwrote in
news:v7******************************@comcast.com:
What does this piece of your query , ["&InventorySource&"] , 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/
Sep 28 '06 #9

P: n/a
Tim Marshall <TI****@PurplePandaChasers.Moertheriumwrote 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/
Sep 28 '06 #10

P: n/a
"Don" <dk*****@twcny.rr.comwrote in
news:11*********************@m73g2000cwd.googlegro ups.com:
strsql = "SELECT * FROM "&InventorySource&""&_
"WHERE(((["&InventorySource&"].NRMU_NO_1=Forms.frmMainSearch.NRMU_N
O_1) Or (Forms.frmMainSearch.NRMU_NO_1) Is Null)"&_
If "&InventorySource& is a variable you're using to refer to a
different table in different circumstances, it's easier to do that
with a table alias in the FROM clause. If your FROM clause has:

FROM " & InventorySource & "...

simply change that to:

FROM " & InventorySource & " As Source"...

and then all your criteria can use Source.FieldName, without needing
to do string concatenation for the variable.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Sep 28 '06 #11

P: n/a
I'm assuming InventorySource is a variable or control name to select
which table to do the search on. If so, then:

strSQL = "Select * from [" & InventorySource & "] As AliasTableName
where 0=0
'String Variable
If not Isnull(forms!frmMainSearch.NRMU_NO_1) then
strsql = strsql & " AND (nrmu_no_1 = '" &
forms.frmmainsearch.nrmu_no_1 & "') "
end if
'Date Variable
If not Isnull(forms!frmMainSearch.NRMU_NO_1) then
strsql = strsql & " AND (nrmu_no_1 = #" &
forms.frmmainsearch.nrmu_no_1 & "#) "
end if
'Numeric
If not Isnull(forms!frmMainSearch.NRMU_NO_1) then
strsql = strsql & " AND (nrmu_no_1 = " &
forms.frmmainsearch.nrmu_no_1 & ") "
end if

.....
You get the point.
Chris Nebinger
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 "&InventorySource&""&_
"WHERE(((["&InventorySource&"].NRMU_NO_1=Forms.frmMainSearch.NRMU_NO_1)
Or (Forms.frmMainSearch.NRMU_NO_1) Is Null)"&_
"And ((["&InventorySource&"].USETYPE=Forms.frmMainSearch.USETYPE) Or
(Forms.frmMainSearch.USETYPE) Is Null)"&_
"And ((["&InventorySource&"].LANDCOVER=Forms.frmMainSearch.LANDCOVER)
Or (Forms.frmMainSearch.LANDCOVER) Is Null)"&_
"And ((["&InventorySource&"].DOM1=Forms.frmMainSearch.DOM1) Or
(Forms.frmMainSearch.DOM1) Is Null)"&_
"And ((["&InventorySource&"].DOM2=Forms.frmMainSearch.DOM2) Or
(Forms.frmMainSearch.DOM2) Is Null)"&_
"And ((["&InventorySource&"].DOM3=Forms.frmMainSearch.DOM3) Or
(Forms.frmMainSearch.DOM3) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP1=Forms.frmMainSearch.AGE2SP1) Or
(Forms.frmMainSearch.AGE2SP1) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP2=Forms.frmMainSearch.AGE2SP2) Or
(Forms.frmMainSearch.AGE2SP2) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP3=Forms.frmMainSearch.AGE2SP3) Or
(Forms.frmMainSearch.AGE2SP3) Is Null)"&_
"And ((["&InventorySource&"].MGTCON=Forms.frmMainSearch.MGTCON) Or
(Forms.frmMainSearch.MGTCON) Is Null)"&_
"And ((["&InventorySource&"].MGTACT=Forms.frmMainSearch.MGTACT) Or
(Forms.frmMainSearch.MGTACT) Is Null)"&_
"And ((["&InventorySource&"].ADDINFO=Forms.frmMainSearch.ADDINFO) Or
(Forms.frmMainSearch.ADDINFO) Is Null)"&_
"And ((["&InventorySource&"].BASAL=Forms.frmMainSearch.BASAL) Or
(Forms.frmMainSearch.BASAL) Is Null)"&_
"And ((["&InventorySource&"].DIAM=Forms.frmMainSearch.DIAM) Or
(Forms.frmMainSearch.DIAM) Is Null)"&_
"And ((["&InventorySource&"].REGEN=Forms.frmMainSearch.REGEN) Or
(Forms.frmMainSearch.REGEN) Is Null)"&_
"And ((["&InventorySource&"].EUAGED=Forms.frmMainSearch.EUAGED) Or
(Forms.frmMainSearch.EUAGED) Is Null)"&_
"And ((["&InventorySource&"].MUD=Forms.frmMainSearch.MUD) Or
(Forms.frmMainSearch.MUD) Is Null)"&_
"And ((["&InventorySource&"].METAL=Forms.frmMainSearch.METAL) Or
(Forms.frmMainSearch.METAL) Is Null)"&_
"And ((Forms.frmMainSearch.STARTDATE Is Null) Or
(Forms.frmMainSearch.ENDDATE Is Null)"&_
"Or (["&InventorySource&"].DATE Between Forms.frmMainSearch.STARTDATE
And Forms.frmMainSearch.ENDDATE))"&_
"And ((Forms.frmMainSearch.MINACR Is Null) Or
(Forms.frmMainSearch.MAXACR Is Null)"&_
"Or (["&InventorySource&"].ACREAGE Between Forms.frmMainSearch.MINACR
And Forms.frmMainSearch.MAXACR))"&_
"And ((["&InventorySource&"].UpWet=Forms.frmMainSearch.UPWET) Or
(Forms.frmMainSearch.UPWET Is Null)));"

Thanks in advance for any input!

Don
Sep 28 '06 #12

P: n/a
the SQL you have is copied from a querydef that has to read the
form and validate each control for nulls. If you create the SQL
statement in the form's module, you can do things much more
simply

start with
DIM strSQL as String
Dim strWHERE as string

strsql = "SELECT * FROM " & InventorySource

If not isnull(me.NRMU_NO_1) then
strWHERE = strWHERE & "NRMU_NO_1= " & me.MU_NO_1 & " AND "
end if

If not isnull(me.usetype) then
strWHERE = strWHERE & "USETYPE= """ & me.USETYPE & """ AND "
end if

If not isnull(me.LANDCOVER) then
strWHERE = strWHERE & "LANDCOVER= """ & me.LANDCOVER& """ AND "
end if

so on until the end, where you need to cut off the last 5
characters, and add the semicolon
if isnull strWHERE then
' nothing to do, select all records.
else
strSQL = strSQL & " WHERE " & left(strWHERE, LEN(strWHERE -5)
end if
strSQL = strSQL & ";"

This will run much faster because it simply doesn't have to
evaluate each condition for each row.
Bob Q
"Don" <dk*****@twcny.rr.comwrote in
news:11*********************@m73g2000cwd.googlegro ups.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 "&InventorySource&""&_
"WHERE(((["&InventorySource&"].NRMU_NO_1
=Forms.frmMainSearch.NR
MU_NO_1) Or (Forms.frmMainSearch.NRMU_NO_1) Is Null)"&_
"And
((["&InventorySource&"].USETYPE=Forms.frmMainSearch.USETYPE)
Or (Forms.frmMainSearch.USETYPE) Is Null)"&_
"And
((["&InventorySource&"].LANDCOVER=Forms.frmMainSearch.LANDCOVE
R
) Or (Forms.frmMainSearch.LANDCOVER) Is Null)"&_
"And ((["&InventorySource&"].DOM1=Forms.frmMainSearch.DOM1) Or
(Forms.frmMainSearch.DOM1) Is Null)"&_
"And ((["&InventorySource&"].DOM2=Forms.frmMainSearch.DOM2) Or
(Forms.frmMainSearch.DOM2) Is Null)"&_
"And ((["&InventorySource&"].DOM3=Forms.frmMainSearch.DOM3) Or
(Forms.frmMainSearch.DOM3) Is Null)"&_
"And
((["&InventorySource&"].AGE2SP1=Forms.frmMainSearch.AGE2SP1)
Or (Forms.frmMainSearch.AGE2SP1) Is Null)"&_
"And
((["&InventorySource&"].AGE2SP2=Forms.frmMainSearch.AGE2SP2)
Or (Forms.frmMainSearch.AGE2SP2) Is Null)"&_
"And
((["&InventorySource&"].AGE2SP3=Forms.frmMainSearch.AGE2SP3)
Or (Forms.frmMainSearch.AGE2SP3) Is Null)"&_
"And
((["&InventorySource&"].MGTCON=Forms.frmMainSearch.MGTCON) Or
(Forms.frmMainSearch.MGTCON) Is Null)"&_
"And
((["&InventorySource&"].MGTACT=Forms.frmMainSearch.MGTACT) Or
(Forms.frmMainSearch.MGTACT) Is Null)"&_
"And
((["&InventorySource&"].ADDINFO=Forms.frmMainSearch.ADDINFO)
Or (Forms.frmMainSearch.ADDINFO) Is Null)"&_
"And ((["&InventorySource&"].BASAL=Forms.frmMainSearch.BASAL)
Or (Forms.frmMainSearch.BASAL) Is Null)"&_
"And ((["&InventorySource&"].DIAM=Forms.frmMainSearch.DIAM) Or
(Forms.frmMainSearch.DIAM) Is Null)"&_
"And ((["&InventorySource&"].REGEN=Forms.frmMainSearch.REGEN)
Or (Forms.frmMainSearch.REGEN) Is Null)"&_
"And
((["&InventorySource&"].EUAGED=Forms.frmMainSearch.EUAGED) Or
(Forms.frmMainSearch.EUAGED) Is Null)"&_
"And ((["&InventorySource&"].MUD=Forms.frmMainSearch.MUD) Or
(Forms.frmMainSearch.MUD) Is Null)"&_
"And ((["&InventorySource&"].METAL=Forms.frmMainSearch.METAL)
Or (Forms.frmMainSearch.METAL) Is Null)"&_
"And ((Forms.frmMainSearch.STARTDATE Is Null) Or
(Forms.frmMainSearch.ENDDATE Is Null)"&_
"Or (["&InventorySource&"].DATE Between
Forms.frmMainSearch.STARTDATE And
Forms.frmMainSearch.ENDDATE))"&_ "And
((Forms.frmMainSearch.MINACR Is Null) Or
(Forms.frmMainSearch.MAXACR Is Null)"&_ "Or
(["&InventorySource&"].ACREAGE Between
Forms.frmMainSearch.MINACR And Forms.frmMainSearch.MAXACR))"&_
"And ((["&InventorySource&"].UpWet=Forms.frmMainSearch.UPWET)
Or (Forms.frmMainSearch.UPWET Is Null)));"

Thanks in advance for any input!

Don


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Sep 28 '06 #13

P: n/a
Hi Don:

Here's an example of how I do it. It works for me, I hope it works for you.
Note that, because of wrapping the lines might look strange.
Essentially, all lines starting with strSELECT, strFROM, strGROUPBY and
strHAVING are single lines of code.
Note how there is a leading space after the quote, before FROM, GROUP BY and
HAVING.
Good luck.

'--Start Code--
Sub Create_qryScoreCard()

On Error GoTo ProcErr

Dim dbs As DAO.Database
Dim qdfScoreCard As DAO.QueryDef
Dim strSELECT As String
Dim strFROM As String
Dim strGROUPBY As String
Dim strHAVING As String

Set dbs = DBEngine(0)(0)

strSELECT = "SELECT qryAllPeriodsStores.PERIOD AS P,
qrySalesMonth.SalesMonth, qryAllPeriodsStores.STORE4_ID,
qryAllPeriodsStores.STORE6_ID, qryAllPeriodsStores.STORE_NAME,
qryAllPeriodsStores.STORE_TYPE, qryAllPeriodsStores.MARKET_NAME,
qryAllPeriodsStores.REGION_OLD, qryAllPeriodsStores.REGION_NEW,
qryAllPeriodsStores.TOTAL_SF, qryAllPeriodsStores.TOTAL_SALES_SF,
qryAllPeriodsStores.TOTAL_SERVICE_SF, qryScorecard_FTE.SumOfFTE,
([EQUIP_SALE_TRAN]+[PMT_ACCT_TRAN]) AS [Gross POS Transactions],
tblHO_Trans.EQUIP_SALE_TRAN, tblHO_Trans.PMT_ACCT_TRAN,
tblUpgrade.QUALIFY_COUNT, tblUpgrade.UNQUAL_COUNT,
tblFBNP_React.REACTIVATION, tblFBNP_React.DIR_COMRATE,
tblFBNP_React.IMP_RBT_RATE, tblCPE.DTTD_ALLOC_CPE, "

strSELECT = strSELECT & "NZ([pre200509_QTY],0)*1 AS [In Store Insurance
Deductible], NZ([50_QTY],0)*1 AS InsDed50Qty, NZ([50_AMT],0)*1 AS
InsDed50Amt, NZ([85_QTY],0)*1 AS InsDed85Qty, NZ([85_AMT],0)*1 AS
IndDed85Amt, NZ([OTHER_QTY],0)*1 AS InsDedOTHERQty, NZ([OTHER_AMT],0)*1 AS
InsDedOTHERAmt, (tblIns_Trans.INS_CLAIM+tblIns_Trans.UNFULFILLED) AS
[Insurance Replacement Claims Submitted], tblIns_Trans.INS_CLAIM,
tblLDDA.LD_TRANSACTION, tblPOS_Trans_SumACCSY.TRANSACTION,
tblDTTD_GA.DTTD_ALLOC_GA, tblDTTD_GA.DTTD_GA, tblDTTD_GA.GA_ALLOCATION,
tblFBNP_React.FIRST_BILL_NON_PAY, tblPriceOverride.OVERRIDE_AMT,
tblStoreQtyByMonth.DIRECT_STORE_QTY, tblMindshare.SCORE AS MindshareScore,
qryGrossAddsbyPremiumRateCodes2.TotalPremRateGA"

strFROM = " FROM ((((((((((((((((((qryAllPeriodsStores LEFT JOIN
tblHO_Trans ON (qryAllPeriodsStores.STORE4_ID = tblHO_Trans.STORE4_ID) AND
(qryAllPeriodsStores.PERIOD = tblHO_Trans.PERIOD)) LEFT JOIN tblUpgrade ON
(qryAllPeriodsStores.STORE4_ID = tblUpgrade.STORE4_ID) AND
(qryAllPeriodsStores.PERIOD = tblUpgrade.PERIOD)) LEFT JOIN tblFBNP_React ON
(qryAllPeriodsStores.PERIOD = tblFBNP_React.PERIOD) AND
(qryAllPeriodsStores.STORE4_ID = tblFBNP_React.STORE4_ID)) LEFT JOIN tblCPE
ON (qryAllPeriodsStores.STORE4_ID = tblCPE.STORE4_ID) AND
(qryAllPeriodsStores.PERIOD = tblCPE.PERIOD)) LEFT JOIN tblIns_Trans ON
(qryAllPeriodsStores.PERIOD = tblIns_Trans.PERIOD) AND
(qryAllPeriodsStores.STORE4_ID = tblIns_Trans.STORE4_ID)) LEFT JOIN tblLDDA
ON (qryAllPeriodsStores.STORE4_ID = tblLDDA.STORE4_ID) AND
(qryAllPeriodsStores.PERIOD = tblLDDA.PERIOD)) LEFT JOIN tblDTTD_GA ON
(qryAllPeriodsStores.PERIOD = tblDTTD_GA.PERIOD) AND
(qryAllPeriodsStores.STORE4_ID = tblDTTD_GA.STORE4_ID)) "

strFROM = strFROM & "LEFT JOIN tblPriceOverride ON
(qryAllPeriodsStores.STORE4_ID = tblPriceOverride.STORE4_ID) AND
(qryAllPeriodsStores.PERIOD = tblPriceOverride.PERIOD)) LEFT JOIN
tblStoreQtyByMonth ON qryAllPeriodsStores.PERIOD =
tblStoreQtyByMonth.PERIOD) LEFT JOIN tblMindshare ON
(qryAllPeriodsStores.PERIOD = tblMindshare.PERIOD) AND
(qryAllPeriodsStores.STORE4_ID = tblMindshare.STORE4_ID)) LEFT JOIN
qrySalesMonth ON qryAllPeriodsStores.PERIOD = qrySalesMonth.PERIOD) LEFT
JOIN tblGrossAddsbyRatePlan ON (qryAllPeriodsStores.STORE4_ID =
tblGrossAddsbyRatePlan.STORE4_ID) AND (qryAllPeriodsStores.PERIOD =
tblGrossAddsbyRatePlan.PERIOD)) LEFT JOIN qryGrossAddsbyPremiumRateCodes2 ON
(qryAllPeriodsStores.PERIOD = qryGrossAddsbyPremiumRateCodes2.PERIOD) AND
(qryAllPeriodsStores.STORE4_ID = qryGrossAddsbyPremiumRateCodes2.STORE4_ID))
LEFT JOIN qryScorecard_FTE ON (qryAllPeriodsStores.STORE6_ID =
qryScorecard_FTE.STORE6_ID) AND (qryAllPeriodsStores.PERIOD =
qryScorecard_FTE.PERIOD)) "

strFROM = strFROM & "LEFT JOIN qryScorecard_POS_Ins_Trans_50 ON
(qryAllPeriodsStores.PERIOD = qryScorecard_POS_Ins_Trans_50.PERIOD) AND
(qryAllPeriodsStores.STORE4_ID = qryScorecard_POS_Ins_Trans_50.STORE4_ID))
LEFT JOIN qryScorecard_POS_Ins_Trans_85 ON (qryAllPeriodsStores.PERIOD =
qryScorecard_POS_Ins_Trans_85.PERIOD) AND (qryAllPeriodsStores.STORE4_ID =
qryScorecard_POS_Ins_Trans_85.STORE4_ID)) LEFT JOIN
qryScorecard_POS_Ins_Trans_OTHER ON (qryAllPeriodsStores.PERIOD =
qryScorecard_POS_Ins_Trans_OTHER.PERIOD) AND (qryAllPeriodsStores.STORE4_ID
= qryScorecard_POS_Ins_Trans_OTHER.STORE4_ID)) LEFT JOIN
qryScorecard_POS_Ins_Trans_pre200509 ON (qryAllPeriodsStores.PERIOD =
qryScorecard_POS_Ins_Trans_pre200509.PERIOD) AND
(qryAllPeriodsStores.STORE4_ID =
qryScorecard_POS_Ins_Trans_pre200509.STORE4_ID)) LEFT JOIN
tblPOS_Trans_SumACCSY ON (qryAllPeriodsStores.STORE4_ID =
tblPOS_Trans_SumACCSY.STORE4_ID) AND (qryAllPeriodsStores.PERIOD =
tblPOS_Trans_SumACCSY.PERIOD)"

strGROUPBY = " GROUP BY qryAllPeriodsStores.PERIOD,
qrySalesMonth.SalesMonth, qryAllPeriodsStores.STORE4_ID,
qryAllPeriodsStores.STORE6_ID, qryAllPeriodsStores.STORE_NAME,
qryAllPeriodsStores.STORE_TYPE, qryAllPeriodsStores.MARKET_NAME,
qryAllPeriodsStores.REGION_OLD, qryAllPeriodsStores.REGION_NEW,
qryAllPeriodsStores.TOTAL_SF, qryAllPeriodsStores.TOTAL_SALES_SF,
qryAllPeriodsStores.TOTAL_SERVICE_SF, qryScorecard_FTE.SumOfFTE,
([EQUIP_SALE_TRAN]+[PMT_ACCT_TRAN]), tblHO_Trans.EQUIP_SALE_TRAN,
tblHO_Trans.PMT_ACCT_TRAN, tblUpgrade.QUALIFY_COUNT,
tblUpgrade.UNQUAL_COUNT, tblFBNP_React.REACTIVATION, "

strGROUPBY = strGROUPBY & "tblFBNP_React.DIR_COMRATE,
tblFBNP_React.IMP_RBT_RATE, tblCPE.DTTD_ALLOC_CPE, NZ([pre200509_QTY],0)*1,
NZ([50_QTY],0)*1, NZ([50_AMT],0)*1, NZ([85_QTY],0)*1, NZ([85_AMT],0)*1,
NZ([OTHER_QTY],0)*1, NZ([OTHER_AMT],0)*1,
(tblIns_Trans.INS_CLAIM+tblIns_Trans.UNFULFILLED), tblIns_Trans.INS_CLAIM,
tblLDDA.LD_TRANSACTION, tblPOS_Trans_SumACCSY.TRANSACTION,
tblDTTD_GA.DTTD_ALLOC_GA, tblDTTD_GA.DTTD_GA, tblDTTD_GA.GA_ALLOCATION,
tblFBNP_React.FIRST_BILL_NON_PAY, tblPriceOverride.OVERRIDE_AMT,
tblStoreQtyByMonth.DIRECT_STORE_QTY, tblMindshare.SCORE,
qryGrossAddsbyPremiumRateCodes2.TotalPremRateGA"

strHAVING = " HAVING (((qryAllPeriodsStores.PERIOD) In (" & m_strPeriod
& ")) AND ((qryAllPeriodsStores.STORE4_ID) In (" & m_strStore4 & ")) AND
((qryAllPeriodsStores.STORE6_ID) In (" & m_strStore6 & ")));"

'--Create the QueryDef if it doesn't exist
On Error Resume Next
dbs.QueryDefs.Refresh
Set qdfScoreCard = dbs.QueryDefs("qryScoreCard")
If (3265 = Err) Then
Set qdfScoreCard = dbs.CreateQueryDef("qryScoreCard")
dbs.QueryDefs.Refresh
Err = 0
End If
On Error GoTo ProcErr

qdfScoreCard.SQL = strSELECT & strFROM & strGROUPBY & strHAVING
qdfScoreCard.Close
dbs.QueryDefs.Refresh

ProcExit:
dbs.Close
Set dbs = Nothing
Exit Sub
ProcErr:
MsgBox "In Create_qryScoreCard routine, Error Number is " & Err.Number &
_
vbCrLf & "Error Description is " & Err.Description
Resume ProcExit

End Sub
'--End Code--

Alan

"Don" <dk*****@twcny.rr.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.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 "&InventorySource&""&_
"WHERE(((["&InventorySource&"].NRMU_NO_1=Forms.frmMainSearch.NRMU_NO_1)
Or (Forms.frmMainSearch.NRMU_NO_1) Is Null)"&_
"And ((["&InventorySource&"].USETYPE=Forms.frmMainSearch.USETYPE) Or
(Forms.frmMainSearch.USETYPE) Is Null)"&_
"And ((["&InventorySource&"].LANDCOVER=Forms.frmMainSearch.LANDCOVER)
Or (Forms.frmMainSearch.LANDCOVER) Is Null)"&_
"And ((["&InventorySource&"].DOM1=Forms.frmMainSearch.DOM1) Or
(Forms.frmMainSearch.DOM1) Is Null)"&_
"And ((["&InventorySource&"].DOM2=Forms.frmMainSearch.DOM2) Or
(Forms.frmMainSearch.DOM2) Is Null)"&_
"And ((["&InventorySource&"].DOM3=Forms.frmMainSearch.DOM3) Or
(Forms.frmMainSearch.DOM3) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP1=Forms.frmMainSearch.AGE2SP1) Or
(Forms.frmMainSearch.AGE2SP1) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP2=Forms.frmMainSearch.AGE2SP2) Or
(Forms.frmMainSearch.AGE2SP2) Is Null)"&_
"And ((["&InventorySource&"].AGE2SP3=Forms.frmMainSearch.AGE2SP3) Or
(Forms.frmMainSearch.AGE2SP3) Is Null)"&_
"And ((["&InventorySource&"].MGTCON=Forms.frmMainSearch.MGTCON) Or
(Forms.frmMainSearch.MGTCON) Is Null)"&_
"And ((["&InventorySource&"].MGTACT=Forms.frmMainSearch.MGTACT) Or
(Forms.frmMainSearch.MGTACT) Is Null)"&_
"And ((["&InventorySource&"].ADDINFO=Forms.frmMainSearch.ADDINFO) Or
(Forms.frmMainSearch.ADDINFO) Is Null)"&_
"And ((["&InventorySource&"].BASAL=Forms.frmMainSearch.BASAL) Or
(Forms.frmMainSearch.BASAL) Is Null)"&_
"And ((["&InventorySource&"].DIAM=Forms.frmMainSearch.DIAM) Or
(Forms.frmMainSearch.DIAM) Is Null)"&_
"And ((["&InventorySource&"].REGEN=Forms.frmMainSearch.REGEN) Or
(Forms.frmMainSearch.REGEN) Is Null)"&_
"And ((["&InventorySource&"].EUAGED=Forms.frmMainSearch.EUAGED) Or
(Forms.frmMainSearch.EUAGED) Is Null)"&_
"And ((["&InventorySource&"].MUD=Forms.frmMainSearch.MUD) Or
(Forms.frmMainSearch.MUD) Is Null)"&_
"And ((["&InventorySource&"].METAL=Forms.frmMainSearch.METAL) Or
(Forms.frmMainSearch.METAL) Is Null)"&_
"And ((Forms.frmMainSearch.STARTDATE Is Null) Or
(Forms.frmMainSearch.ENDDATE Is Null)"&_
"Or (["&InventorySource&"].DATE Between Forms.frmMainSearch.STARTDATE
And Forms.frmMainSearch.ENDDATE))"&_
"And ((Forms.frmMainSearch.MINACR Is Null) Or
(Forms.frmMainSearch.MAXACR Is Null)"&_
"Or (["&InventorySource&"].ACREAGE Between Forms.frmMainSearch.MINACR
And Forms.frmMainSearch.MAXACR))"&_
"And ((["&InventorySource&"].UpWet=Forms.frmMainSearch.UPWET) Or
(Forms.frmMainSearch.UPWET Is Null)));"

Thanks in advance for any input!

Don

Sep 29 '06 #14

This discussion thread is closed

Replies have been disabled for this discussion.