SQL Help

  • Thread starter Thread starter RayToddJr
  • Start date Start date
R

RayToddJr

I'm trying to get this sql to work. I have a feeling the error I am getting
is the result of not properly using double or single quotes.

The error seems to be around the concatenate function as the code stops and
I am told that the variable taProperty.PropertyID is not defined.

Any helps is appreciated.

- - - -
INSERT INTO taTEMP3 ( PropertyID, CLT, PropertyAddress, DefendantName,
Address1, Address2, City, State, Zip, ParcelNumber, Property_Owner,
CombinedParcelNumber, DefendantType ) SELECT DISTINCT taPROPERTY.PropertyID,
taPROPERTY.CLT, taPROPERTY.PropertyAddress, Trim([Lastname] & (', '
+[FirstName] & ' ' & [MiddleName] & ' ' & [Suffix])) AS DefendantName,
taDEFENDANTNAMES.Address1, taDEFENDANTNAMES.Address2, taDEFENDANTNAMES.City,
taDEFENDANTNAMES.State, taDEFENDANTNAMES.Zip, taPROPERTY.ParcelNumber,
Trim(CONCATENATE(""SELECT tadefendantnames.FirstName & ' ' & MiddleName & ' '
& LastName & ' ' & Suffix FROM taDEFENDANTNAMES INNER JOIN taDefendants ON
taDEFENDANTNAMES.DefendantsnameID=taDEFENDANTS.DefendantsnameID WHERE
tadefendants.PropertyID= '" & taProperty.PropertyID & "' And ""
tadefendants.DefendantTypeID=20"")) AS Property_Owner,
Trim(ConcatenateParcelNumber(""SELECT DISTINCT ParcelNumber from taPROPERTY
INNER JOIN taDefendants on taProperty.PropertyID=taDefendants.PropertyID
WHERE taDefendants.DefendantsNameID="" & DefendantNames.DefendantsNameID)) AS
CombinedParcelNumber , taDEFENDANTTYPE.DefendantType FROM taTAXSALESTATUS
RIGHT JOIN (taPROPERTY INNER JOIN (taDEFENDANTTYPE RIGHT JOIN
(taDEFENDANTNAMES INNER JOIN taDEFENDANTS ON
taDEFENDANTNAMES.DefendantsNameID = taDEFENDANTS.DefendantsNameID) ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID) ON
taTAXSALESTATUS.TaxSaleStatusID = taPROPERTY.TaxSaleStatusID WHERE
(((taPROPERTY.PropertyID)= " & lngPROPID & "));"
 
INSERT INTO taTEMP3 ( PropertyID, CLT, PropertyAddress, DefendantName,
Address1, Address2, City, State, Zip, ParcelNumber, Property_Owner,
CombinedParcelNumber, DefendantType )

SELECT DISTINCT taPROPERTY.PropertyID,
taPROPERTY.CLT
, taPROPERTY.PropertyAddress
, Trim([Lastname] & (', ' + [FirstName] & ' ' & [MiddleName] & ' ' &
[Suffix])) AS DefendantName
, taDEFENDANTNAMES.Address1
, taDEFENDANTNAMES.Address2
, taDEFENDANTNAMES.City
, taDEFENDANTNAMES.State
, taDEFENDANTNAMES.Zip
, taPROPERTY.ParcelNumber
, Trim(CONCATENATE("SELECT tadefendantnames.FirstName & ' ' & MiddleName & ' '
& LastName & ' ' & Suffix FROM taDEFENDANTNAMES INNER JOIN taDefendants
ON taDEFENDANTNAMES.DefendantsnameID=taDEFENDANTS.DefendantsnameID WHERE
tadefendants.PropertyID= " & taProperty.PropertyID & " And
tadefendants.DefendantTypeID=20") AS Property_Owner
,
Trim(ConcatenateParcelNumber("SELECT DISTINCT ParcelNumber from taPROPERTY
INNER JOIN taDefendants on taProperty.PropertyID=taDefendants.PropertyID
WHERE taDefendants.DefendantsNameID=" & DefendantNames.DefendantsNameID)) AS
CombinedParcelNumber
, taDEFENDANTTYPE.DefendantType

FROM taTAXSALESTATUS
RIGHT JOIN (taPROPERTY INNER JOIN (taDEFENDANTTYPE RIGHT JOIN
(taDEFENDANTNAMES INNER JOIN taDEFENDANTS ON
taDEFENDANTNAMES.DefendantsNameID = taDEFENDANTS.DefendantsNameID) ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID) ON
taTAXSALESTATUS.TaxSaleStatusID = taPROPERTY.TaxSaleStatusID
WHERE taPROPERTY.PropertyID= lngPROPID

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I'm trying to get this sql to work. I have a feeling the error I am getting
is the result of not properly using double or single quotes.

The error seems to be around the concatenate function as the code stops and
I am told that the variable taProperty.PropertyID is not defined.

Any helps is appreciated.

- - - -
INSERT INTO taTEMP3 ( PropertyID, CLT, PropertyAddress, DefendantName,
Address1, Address2, City, State, Zip, ParcelNumber, Property_Owner,
CombinedParcelNumber, DefendantType ) SELECT DISTINCT taPROPERTY.PropertyID,
taPROPERTY.CLT, taPROPERTY.PropertyAddress, Trim([Lastname] & (', '
+[FirstName] & ' ' & [MiddleName] & ' ' & [Suffix])) AS DefendantName,
taDEFENDANTNAMES.Address1, taDEFENDANTNAMES.Address2, taDEFENDANTNAMES.City,
taDEFENDANTNAMES.State, taDEFENDANTNAMES.Zip, taPROPERTY.ParcelNumber,
Trim(CONCATENATE(""SELECT tadefendantnames.FirstName & ' ' & MiddleName & ' '
& LastName & ' ' & Suffix FROM taDEFENDANTNAMES INNER JOIN taDefendants ON
taDEFENDANTNAMES.DefendantsnameID=taDEFENDANTS.DefendantsnameID WHERE
tadefendants.PropertyID= '" & taProperty.PropertyID & "' And ""
tadefendants.DefendantTypeID=20"")) AS Property_Owner,
Trim(ConcatenateParcelNumber(""SELECT DISTINCT ParcelNumber from taPROPERTY
INNER JOIN taDefendants on taProperty.PropertyID=taDefendants.PropertyID
WHERE taDefendants.DefendantsNameID="" & DefendantNames.DefendantsNameID)) AS
CombinedParcelNumber , taDEFENDANTTYPE.DefendantType FROM taTAXSALESTATUS
RIGHT JOIN (taPROPERTY INNER JOIN (taDEFENDANTTYPE RIGHT JOIN
(taDEFENDANTNAMES INNER JOIN taDEFENDANTS ON
taDEFENDANTNAMES.DefendantsNameID = taDEFENDANTS.DefendantsNameID) ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID) ON
taTAXSALESTATUS.TaxSaleStatusID = taPROPERTY.TaxSaleStatusID WHERE
(((taPROPERTY.PropertyID)= " & lngPROPID & "));"
Click to expand...
 
Hello John:

Thanks for your help. I'm still getting the same error as before, variable
not defined, the variable that Access thinks is a variable is:
taProperty.PropertyID

Here is the code exactly as I have it typed:




strSQL = "INSERT INTO taTEMP3 ( PropertyID, CLT, PropertyAddress, "
strSQL = strSQL & "DefendantName, Address1, Address2, City, State, "
strSQL = strSQL & "Zip, ParcelNumber, Property_Owner, "
strSQL = strSQL & "CombinedParcelNumber, DefendantType ) "
strSQL = strSQL & "SELECT DISTINCT taPROPERTY.PropertyID, "
strSQL = strSQL & "taPROPERTY.CLT, taPROPERTY.PropertyAddress, "
strSQL = strSQL & "Trim([Lastname] & "
strSQL = strSQL & "(', ' + [FirstName] & ' ' & [MiddleName] & ' ' & "
strSQL = strSQL & "[Suffix])) AS DefendantName, taDEFENDANTNAMES.Address1, "
strSQL = strSQL & "taDEFENDANTNAMES.Address2, taDEFENDANTNAMES.City, "
strSQL = strSQL & "taDEFENDANTNAMES.State, taDEFENDANTNAMES.Zip, "
strSQL = strSQL & "taPROPERTY.ParcelNumber, "


strSQL = strSQL & "Trim(CONCATENATE('SELECT tadefendantnames.FirstName & "
strSQL = strSQL & "' ' & MiddleName & ' ' "
strSQL = strSQL & "& LastName & ' ' & Suffix FROM taDEFENDANTNAMES "
strSQL = strSQL & "INNER JOIN taDefendants "
strSQL = strSQL & "ON
taDEFENDANTNAMES.DefendantsnameID=taDEFENDANTS.DefendantsnameID "
strSQL = strSQL & "WHERE tadefendants.PropertyID= " & taProperty.PropertyID
& " And "
strSQL = strSQL & "tadefendants.DefendantTypeID='20') AS Property_Owner, "


strSQL = strSQL & "Trim(ConcatenateParcelNumber('SELECT DISTINCT
ParcelNumber "
strSQL = strSQL & "from taPROPERTY "
strSQL = strSQL & "INNER JOIN taDefendants on "
strSQL = strSQL & "taProperty.PropertyID=taDefendants.PropertyID "
strSQL = strSQL & "WHERE taDEFENDANTS.DefendantsNameID = ' & "
strSQL = strSQL & "DefendantNames.DefendantsNameID)) AS "
strSQL = strSQL & "CombinedParcelNumber, taDEFENDANTTYPE.DefendantType "
strSQL = strSQL & "FROM taTAXSALESTATUS "
strSQL = strSQL & "RIGHT JOIN (taPROPERTY INNER JOIN (taDEFENDANTTYPE RIGHT
JOIN "
strSQL = strSQL & "(taDEFENDANTNAMES INNER JOIN taDEFENDANTS ON "
strSQL = strSQL & "taDEFENDANTNAMES.DefendantsNameID "
strSQL = strSQL & "= taDEFENDANTS.DefendantsNameID) ON "
strSQL = strSQL & "taDEFENDANTTYPE.DefendantTypeID = "
strSQL = strSQL & "taDEFENDANTS.DefendantTypeID) ON taPROPERTY.PropertyID = "
strSQL = strSQL & "taDEFENDANTS.PropertyID) "
strSQL = strSQL & "ON taTAXSALESTATUS.TaxSaleStatusID =
taProperty.TaxSaleStatusID "
strSQL = strSQL & "WHERE taProperty.PropertyID =" & lngPROPID
 
RayToddJr said:
Hello John:

Thanks for your help. I'm still getting the same error as before,
variable not defined, the variable that Access thinks is a variable
is: taProperty.PropertyID

Here is the code exactly as I have it typed:

strSQL = "INSERT INTO taTEMP3 ( PropertyID, CLT, PropertyAddress, "
Click to expand...
strSQL = strSQL & "WHERE taProperty.PropertyID =" & lngPROPID
Click to expand...

Here is where you need to debug this statement. Put this line of code in:

debug.write strsql

Run the code and look at the string written to the immediate window. Does
it look right? If you have built it properly, you should be able to copy it
from the immediate window and paste it into the sql view of a Query Builder
window and run it without modification. Can you do that? If not, show us the
string that resulted from this concatenation.
 
Thanks for your help. I'm still getting the same error as before, variable
not defined, the variable that Access thinks is a variable is:
taProperty.PropertyID
Click to expand...

That's because it's not. You're trying to concatenate taProperty.PropertyID to
string constants. It's not a string constant, it's not a variable, and you
can't use that syntax to look up the value of PropertyID from a table. What's
the purpose of the part of the expression

strSQL = strSQL & "WHERE tadefendants.PropertyID= " & taProperty.PropertyID

in your query? What value should taProperty.PropertyID have? Maybe you want
that in the JOIN clause?
 
See answers below:

John W. Vinson said:
On Tue, 5 May 2009 12:56:07 -0700, RayToddJr
Click to expand...
Access thinks is a variable is:
That's because it's not. You're trying to concatenate taProperty.PropertyID to
string constants. It's not a string constant, it's not a variable, and you
can't use that syntax to look up the value of PropertyID from a table. What's
the purpose of the part of the expression

strSQL = strSQL & "WHERE tadefendants.PropertyID= " & taProperty.PropertyID

in your query? What value should taProperty.PropertyID have? Maybe you want
that in the JOIN clause?
Click to expand...

PropertyID is a number (long).

The purpose of this part of the expression is to concatenate the names of
all of the owners of a property. Further in the expression, only the
defendant names whose DefendantType is equal to 20 (means owner) are
concatenated.

The purpose of this entire query is to generate the data necessary to send
notification letters.
 
Hello Bob:

I've got the sql from a working query and it works great as a query.
However, when I try to save the code to the strSQL string, I am getting
errors as I have described. I'm sure I'm just not formatting the string
correctly.

Thanks,

Ray.
 
And that is why you need to use debug.write to verify that your VBA code
is generating the same string that works in your query.

I think John has spotted your error in any case.
 
RayToddJr said:
See answers below:


Access thinks is a variable is:

PropertyID is a number (long).
Click to expand...

You're missing his point. taProperty.PropertyID is not available to
your VBA code. Try doing this:

MsgBox taProperty.PropertyID

You should get the same undefined variable error.

John's point is that your statement should be:
strSQL = strSQL & "WHERE tadefendants.PropertyID=taProperty.PropertyID"
 
Back
Top