Nesting strings inside strings...

[Matt P]

Hello!

Alright so I am just trying to tidy up my code a little bit. I have a
Select Case statement that I used and I have it so it runs some sql /
query based on the option box they chose. That works perfect, my
problem was when I went to tidy my code up I added the strings that I
had to copy and paste to each "Case":

Dim strcmbSearchComp As String
Dim strlstCompanyNames As String
Dim strlstAddresses As String


strcmbSearchComp = "SELECT DISTINCT tblContacts.fldCity " & _
"FROM tblContacts INNER JOIN tblHistory ON
tblContacts.fldContactsID=tblHistory.fldContactID " & _
"WHERE " & strContactType & _
"GROUP BY tblContacts.fldCity;"

strlstCompanyNames = "SELECT tblContacts.fldContactsID,
tblContacts.fldAddress " & _
"FROM tblContacts INNER JOIN tblHistory ON
tblContacts.fldContactsID=tblHistory.fldContactID " & _
"WHERE (((tblContacts.fldCity) =
[cmbSearchComp])) " & _
"GROUP BY tblContacts.fldAddress,
tblContacts.fldContactsID, Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress,' ')+1), tblHistory.fldType " & _
"HAVING " & strContactType & _
"ORDER BY Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress,' ')+1);"
strlstAddresses = "SELECT tblContacts.fldContactsID,
tblContacts.fldAddress " & _
"FROM tblContacts INNER JOIN tblHistory ON
tblContacts.fldContactsID=tblHistory.fldContactID " & _
"WHERE (((tblContacts.fldCity) = [cmbSearchComp])) "
& _
"GROUP BY tblContacts.fldAddress,
tblContacts.fldContactsID, Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress,' ')+1), tblHistory.fldType " & _
"HAVING " & strContactType & _
"ORDER BY Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress,' ')+1);"

There are three strings... Here is an example of one of my strings:

Case 2
'Insurance Companies
Me.RecordSource = "qryMainSortByInsurance"
'cmbSearchComp & lstAddresses
strContactType = "(((tblHistory.fldType) = 'INSURANCE'))"
'lstCompanyNames
strContactType2 = "(((tblContacts.fldCity)=[cmbSearchComp])
AND ((tblHistory.fldType)= 'INSURANCE'))"


Me.cmbSearchComp.RowSource = strcmbSearchComp
lstCompanyNames.RowSource = strlstCompanyNames
lstAddresses.RowSource = strlstAddresses
Me.cmbSearchComp.Value = ""
Me.lstCompanyNames.Value = ""
Me.Refresh


It gives me an error saying "Syntax error in WHERE clause" which is
the nested string "strcmbSearchComp"

So basically it comes down to this question... Can I nest strings
inside strings and if so, how?

Thanks,
Matt P

 

[Ken Snell [MVP]]

Do your CASE statements come before you set the value of the
strcmbSearchComp variable? If not, then there is no value for strContactType
when you try to build the string for strcmbSearchComp.

 

[Matt P]

Do your CASE statements come before you set the value of the
strcmbSearchComp variable? If not, then there is no value for strContactType
when you try to build the string for strcmbSearchComp.

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

Hello!

Alright so I am just trying to tidy up my code a little bit.  I have a
Select Case statement that I used and I have it so it runs some sql /
query based on the option box they chose.  That works perfect, my
problem was when I went to tidy my code up I added the strings that I
had to copy and paste to each "Case":

Dim strcmbSearchComp As String
Dim strlstCompanyNames As String
Dim strlstAddresses As String

strcmbSearchComp = "SELECT DISTINCT tblContacts.fldCity " & _
                   "FROM tblContacts INNER JOIN tblHistory ON
tblContacts.fldContactsID=tblHistory.fldContactID " & _
                   "WHERE " & strContactType & _
                   "GROUP BY tblContacts.fldCity;"

strlstCompanyNames = "SELECT tblContacts.fldContactsID,
tblContacts.fldAddress " & _
                    "FROM tblContacts INNER JOIN tblHistory ON
tblContacts.fldContactsID=tblHistory.fldContactID " & _
                    "WHERE (((tblContacts.fldCity) =
[cmbSearchComp])) " & _
                    "GROUP BY tblContacts.fldAddress,
tblContacts.fldContactsID, Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress,' ')+1), tblHistory.fldType " & _
                    "HAVING " & strContactType & _
                    "ORDER BY Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress,' ')+1);"
strlstAddresses = "SELECT tblContacts.fldContactsID,
tblContacts.fldAddress " & _
                 "FROM tblContacts INNER JOIN tblHistory ON
tblContacts.fldContactsID=tblHistory.fldContactID " & _
                 "WHERE (((tblContacts.fldCity) = [cmbSearchComp])) "
& _
                 "GROUP BY tblContacts.fldAddress,
tblContacts.fldContactsID, Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress,' ')+1), tblHistory.fldType " & _
                 "HAVING " & strContactType & _
                 "ORDER BY Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress,' ')+1);"

There are three strings... Here is an example of one of my strings:

     Case 2
           'Insurance Companies
        Me.RecordSource = "qryMainSortByInsurance"
        'cmbSearchComp & lstAddresses
        strContactType = "(((tblHistory.fldType) = 'INSURANCE'))"
        'lstCompanyNames
        strContactType2 = "(((tblContacts.fldCity)=[cmbSearchComp])
AND ((tblHistory.fldType)= 'INSURANCE'))"

        Me.cmbSearchComp.RowSource = strcmbSearchComp
        lstCompanyNames.RowSource = strlstCompanyNames
        lstAddresses.RowSource = strlstAddresses
        Me.cmbSearchComp.Value = ""
        Me.lstCompanyNames.Value = ""
        Me.Refresh

It gives me an error saying "Syntax error in WHERE clause"  which is
the nested string "strcmbSearchComp"

So basically it comes down to this question... Can I nest strings
inside strings  and if so, how?

Thanks,
Matt P

Thanks for your reponse! Ok I have the strcmbSearchComp variable set
inside each of the CASE statements before the strcmbSearchComp is run,
which worked perfectly fine before when I didn't have strcmbSearchComp
(just the sql statement). Did I answer your question?

 

[Ken Snell [MVP]]

It'll be easier for us to troubleshoot your situation if you post all the
code; that will let us see the order of things and possibly more quickly see
what the issue is.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Do your CASE statements come before you set the value of the
strcmbSearchComp variable? If not, then there is no value for
strContactType
when you try to build the string for strcmbSearchComp.

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

Hello!

Alright so I am just trying to tidy up my code a little bit. I have a
Select Case statement that I used and I have it so it runs some sql /
query based on the option box they chose. That works perfect, my
problem was when I went to tidy my code up I added the strings that I
had to copy and paste to each "Case":

Dim strcmbSearchComp As String
Dim strlstCompanyNames As String
Dim strlstAddresses As String

strcmbSearchComp = "SELECT DISTINCT tblContacts.fldCity " & _
"FROM tblContacts INNER JOIN tblHistory ON
tblContacts.fldContactsID=tblHistory.fldContactID " & _
"WHERE " & strContactType & _
"GROUP BY tblContacts.fldCity;"

strlstCompanyNames = "SELECT tblContacts.fldContactsID,
tblContacts.fldAddress " & _
"FROM tblContacts INNER JOIN tblHistory ON
tblContacts.fldContactsID=tblHistory.fldContactID " & _
"WHERE (((tblContacts.fldCity) =
[cmbSearchComp])) " & _
"GROUP BY tblContacts.fldAddress,
tblContacts.fldContactsID, Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress,' ')+1), tblHistory.fldType " & _
"HAVING " & strContactType & _
"ORDER BY Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress,' ')+1);"
strlstAddresses = "SELECT tblContacts.fldContactsID,
tblContacts.fldAddress " & _
"FROM tblContacts INNER JOIN tblHistory ON
tblContacts.fldContactsID=tblHistory.fldContactID " & _
"WHERE (((tblContacts.fldCity) = [cmbSearchComp])) "
& _
"GROUP BY tblContacts.fldAddress,
tblContacts.fldContactsID, Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress,' ')+1), tblHistory.fldType " & _
"HAVING " & strContactType & _
"ORDER BY Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress,' ')+1);"

There are three strings... Here is an example of one of my strings:

Case 2
'Insurance Companies
Me.RecordSource = "qryMainSortByInsurance"
'cmbSearchComp & lstAddresses
strContactType = "(((tblHistory.fldType) = 'INSURANCE'))"
'lstCompanyNames
strContactType2 = "(((tblContacts.fldCity)=[cmbSearchComp])
AND ((tblHistory.fldType)= 'INSURANCE'))"

Me.cmbSearchComp.RowSource = strcmbSearchComp
lstCompanyNames.RowSource = strlstCompanyNames
lstAddresses.RowSource = strlstAddresses
Me.cmbSearchComp.Value = ""
Me.lstCompanyNames.Value = ""
Me.Refresh

It gives me an error saying "Syntax error in WHERE clause" which is
the nested string "strcmbSearchComp"

So basically it comes down to this question... Can I nest strings
inside strings and if so, how?

Thanks,
Matt P

Thanks for your reponse! Ok I have the strcmbSearchComp variable set
inside each of the CASE statements before the strcmbSearchComp is run,
which worked perfectly fine before when I didn't have strcmbSearchComp
(just the sql statement). Did I answer your question?