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
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
(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
The Access application calls the Stored Procedure as per the following:
.CommandType = adCmdStoredProc
.CommandText = "dbo.procFlTestFI"
.Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
.ActiveConnection = cn
Set rstQueryFS = .Execute