Why does the following record appear when executing the Stored Procedure ?

Could you explain why I get a record to display under the following conditions:
I would have thought that it would have not passed my conditional test.

The record has the following field values:

DateLost is 8/1/2000 (as per the output file) This is the value from tblCustomers

BondsFS = 5 (as per the output file) This is the value from tblStatesAll for the state of DE (Delaware). I placed the following statement in my WHERE clause:
tblStatesAll.StateFS = 'DE'.

IntYearSP = 2005 (as per the Debugger). This value is passed along to @RptYear. The value is selected from a list box and assigned to IntYearSP.


In the stored procedure, I created the following variable:
@DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll --> I assume this would return 6/30/2000 (6/30/2005 - 5)

Thus, I would have thought that DateLost (8/1/2000) <= (6/30/2000) would be false. Yet the record with a Date Lost value of 8/1/2000 is in the output file.

My stored procedure is as follows:

CREATE PROCEDURE dbo.procFlTestFI

@RptYear int
AS

DECLARE @DateFI AS DATETIME

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

SELECT Null AS Title, UPPER(tblCustomers.FirstName) AS [First Name], UPPER(tblCustomers.MiddleInitial) AS [MI],
UPPER(tblCustomers.LastName) AS [Last Name], UPPER(tblCustomers.SecondNameFirst) AS [Second Name First],
UPPER(tblCustomers.SecondNameMid) AS [Second Name Mid], UPPER(tblCustomers.SecondNameLast) AS [Second Name Last],
Null AS Relation, UPPER(tblCustomers.Address1) AS [Address 1], UPPER (tblCustomers.Address2) AS [Address 2],
UPPER(tblCustomers.Address3) AS [Address 3], UPPER(tblCustomers.Address4) AS [Address 4], UPPER(tblCustomers.City) AS City,
UPPER(tblCustomers.State) AS State, tblCustomers.Zip AS Zip, tblCustomers.SSN, Null AS [DDA Number], Null AS [Check/Cert Number],
tblProducts.CUSIP, tblProducts.SecurityName AS [Security Name], Null AS [Sub-Issue],
Right(tblCustomers.OfficeNumber,3) + ' ' + tblCustomers.CustomerNumber AS [Account Number],
tblProducts.PropertyType AS [Property Type], Null AS [Property Status],
CASE WHEN tblProducts.CashBalance IS NULL THEN tblProducts.Quantity ELSE tblProducts.CashBalance END AS [Check/Cert Amount],tblStatesAll.BondsFS,tblCustomers.DateLost
 
FROM tblStatesAll INNER JOIN (tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber = tblProducts.CustomerNumber)
ON tblStatesAll.StateFS <> tblCustomers.State
WHERE
               (tblProducts.PropertyType='FIXED INCOME' AND tblCustomers.DateLost <= @DateFI AND tblProducts.IRACode Is Null  AND tblStatesAll.StateFS = 'DE' AND LEN(Zip) = 0)
             

ORDER BY tblCustomers.State
GO

The Access application calls the Stored Procedure as per the following:

With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procFlTestFI"
       .Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
       .ActiveConnection = cn
       Set rstQueryFS = .Execute
End With
zimmer9Asked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
Change this:

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

to this:

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll
WHERE State = 'DE'
0
 
ABaruhCommented:
Even though your @DateFI is datetime, try doing an additional cast in your WHERE...

WHERE
               (tblProducts.PropertyType='FIXED INCOME' AND cast(tblCustomers.DateLost as datetime) <= cast(@DateFI as datetime) AND tblProducts.IRACode Is Null  AND tblStatesAll.StateFS = 'DE' AND cast(LEN(Zip) as int) = 0)
0
 
rafranciscoCommented:
I believe this one is the one giving you the error.  Since you are not qualifying from which state you are getting the BondsFS, this can be a 3 and not 5, which will give you're @DateFI a value of '2002-06-30' instead of '2000-06-30'.

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

Make sure you specify from which state to get the BondsFS.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.