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

D

Don

I have a lengthy SQL string in my VBA code. Using line continuation and
character and concatenation, I've tried to appease the compiler.
However, I keep getting Expected errors. I think that there are
limitations to SQL strings in VBA i.e. lines and characters. From
reading other posts, I see that multiple occurences of strSQL can be
used too. I've tried coding this string different ways with no luck.

Can someone take a look at this code and help me reformat it properly
so that it doesn't keep giving me compile errors?

I'm pretty sure that I need to break it up so that I have
strSQL="Select * From "
strSQL=strSQL&"Where"
And so on.

Here's my code:
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.
 
R

Roger Carlson

Since you're building a string, why not just use the VALUE of the controls
instead of referencing the controls directly? Something like this:

strsql = "SELECT * FROM "&InventorySource& _
" WHERE (NRMU_NO_1= " & Forms.frmMainSearch.NRMU_NO_1 & _
" Or Forms.frmMainSearch.NRMU_NO_1 Is Null)"

This puts the VALUE store in the control directly into the SQL String, just
like any other variable.

Notice also that you don't need to add ["&InventorySource&"]. to the front
of every field name. Since there is only one table, SQL will assume it from
the FROM clause. You can also get rid of many of the parenthases, except
those necessary to establish order of precidence.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Don said:
I have a lengthy SQL string in my VBA code. Using line continuation and
character and concatenation, I've tried to appease the compiler.
However, I keep getting Expected errors. I think that there are
limitations to SQL strings in VBA i.e. lines and characters. From
reading other posts, I see that multiple occurences of strSQL can be
used too. I've tried coding this string different ways with no luck.

Can someone take a look at this code and help me reformat it properly
so that it doesn't keep giving me compile errors?

I'm pretty sure that I need to break it up so that I have
strSQL="Select * From "
strSQL=strSQL&"Where"
And so on.

Here's my code:
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.
Click to expand...
 
K

Ken Sheridan

Why build the SQL statement in code at all? Its made up completely of
literal strings, so you can just as easily create and save a query.

If you do build it in code then you should not include the parameters as
part of the literal string. Instead, examine the controls one by one and
concatenate the values of the controls into the string expression only where
a control is not Null.

Ken Sheridan
Stafford, England

Don said:
I have a lengthy SQL string in my VBA code. Using line continuation and
character and concatenation, I've tried to appease the compiler.
However, I keep getting Expected errors. I think that there are
limitations to SQL strings in VBA i.e. lines and characters. From
reading other posts, I see that multiple occurences of strSQL can be
used too. I've tried coding this string different ways with no luck.

Can someone take a look at this code and help me reformat it properly
so that it doesn't keep giving me compile errors?

I'm pretty sure that I need to break it up so that I have
strSQL="Select * From "
strSQL=strSQL&"Where"
And so on.

Here's my code:
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.
Click to expand...
 
J

John Vinson

Can someone take a look at this code and help me reformat it properly
so that it doesn't keep giving me compile errors?
Click to expand...

Several problems!
I'm pretty sure that I need to break it up so that I have
strSQL="Select * From "
strSQL=strSQL&"Where"
Click to expand...

No, you don't, actually. Continuation characters work fine. The VBA
continuation code is a space followed by an underscore followed by a
new line - all are essential in that order!


Dim strSQL As String
strsql = "SELECT * FROM "&InventorySource&""&_

Is InventorySource a variable name? or is it the actual table name? If
you're concatenating a zero lenght string, don't bother - that's a "do
nothing" operation. Also, it may be prudent and cannot hurt to include
brackets around the table name. Assuming it's a string variable, this
line should be

strsql = "SELECT * FROM [" & InventorySource & "]" & _

Note the required blank before the underscore, and blanks (not
essential but helpful for readability) around the ampersands.

Do note that rather than building a huge complex SQL string with every
field checked for NULL, you may be much better off checking for NULL
in the code and only including the criterion if it's needed! Is this
code actually running on your search form? If so you could concatenate
the values in the (non-null) form controls rather than their names.

John W. Vinson[MVP]
 
D

Douglas J. Steele

John Vinson said:
Several problems!


No, you don't, actually. Continuation characters work fine. The VBA
continuation code is a space followed by an underscore followed by a
new line - all are essential in that order!
Click to expand...


In Access 97, you couldn't have more than 10 lines connected using line
continuation characters. I don't know whether this limit has been increased
in later versions.
 
K

Ken Sheridan

On second thoughts, maybe its not all literal strings if InventorySource is a
variable not a table name as I'd assumed. But that suggests you have
multiple identically structured tables. Which in turn suggests you are
encoding data as table names. A fundamental principle of the database
relational model is that data is stored as values at column positions in rows
in tables. What you really need is another table in whose rows the values of
whatever attributes the different tables currently represent. All that’s
then required is a further column in a single table which references the key
of this additional table. You can then have a parameter on this foreign key
column to restrict the result set to the required value of whatever this
attribute is.

You also appear to have multiple DOM and AGE2SP columns in the table, which
suggests data is also being encoded as column headings, which again
contradicts the same principle of the relational model. Where multiple
values of the same attribute are required this should be by means of multiple
rows in a related table.

Ken Sheridan
Stafford, England

Don said:
I have a lengthy SQL string in my VBA code. Using line continuation and
character and concatenation, I've tried to appease the compiler.
However, I keep getting Expected errors. I think that there are
limitations to SQL strings in VBA i.e. lines and characters. From
reading other posts, I see that multiple occurences of strSQL can be
used too. I've tried coding this string different ways with no luck.

Can someone take a look at this code and help me reformat it properly
so that it doesn't keep giving me compile errors?

I'm pretty sure that I need to break it up so that I have
strSQL="Select * From "
strSQL=strSQL&"Where"
And so on.

Here's my code:
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.
Click to expand...
 
Top