I am having a problem with calculated fields (...the most important one in the database!) in an MS Access query that pulls info from one table and a few other calculated fields stored in other queries.
[ExpectedPmt] simply adds the values of several fields for each record:
ExpectedPmt: CLng([InterceptScore]+[StateScore]+[EmploymentScore]+
[HomePhoneScore]+[WorkPhoneScore]+[BirthdateScore]+[GenderScore]+
[CustomerTypeSco re]+[AccountStatusSc ore]+[BalanceScore]+
[InterestRateSco re]+[LastPmtAmtScore]+[LastPmtDateScor e]+
[LastContactDate Score]+[OtherObligation sScore]+[RentOwnScore])
[Score] then gives each record a "score" based on predetermined ranges for [ExpectedPmt]:
Score: CLng(IIf([ExpectedPmt]<="0","0",IIf ([ExpectedPmt]>"0" And [ExpectedPmt]<"50","1",IIf ([ExpectedPmt]>="50" And [ExpectedPmt]<"100","2",II f([ExpectedPmt]>="100" And [ExpectedPmt]<"250","3",II f([ExpectedPmt]>="250" And [ExpectedPmt]<"500","4",II f([ExpectedPmt]>="500" And [ExpectedPmt]<"750","5",II f([ExpectedPmt]>="750" And [ExpectedPmt]<"1000","6","7" ))))))))
Based on the above, I would expect values of "73", "397", "772", and "1056" for [ExpectedPmt] to return values of "2", 4", 6", and "7" for [Score], respectively.
However, the query returns values of "5", "1", "7", and "1" for [Score], respectively. It seems that in the equation for [Score] it is only reading the first digit for both the value of [ExpectedPmt] and criteria value in the IIf statements.
Any help would be greatly appreciated.
FYI - My user skill-level is low and I have no programming experience...
[ExpectedPmt] simply adds the values of several fields for each record:
ExpectedPmt: CLng([InterceptScore]+[StateScore]+[EmploymentScore]+
[HomePhoneScore]+[WorkPhoneScore]+[BirthdateScore]+[GenderScore]+
[CustomerTypeSco re]+[AccountStatusSc ore]+[BalanceScore]+
[InterestRateSco re]+[LastPmtAmtScore]+[LastPmtDateScor e]+
[LastContactDate Score]+[OtherObligation sScore]+[RentOwnScore])
[Score] then gives each record a "score" based on predetermined ranges for [ExpectedPmt]:
Score: CLng(IIf([ExpectedPmt]<="0","0",IIf ([ExpectedPmt]>"0" And [ExpectedPmt]<"50","1",IIf ([ExpectedPmt]>="50" And [ExpectedPmt]<"100","2",II f([ExpectedPmt]>="100" And [ExpectedPmt]<"250","3",II f([ExpectedPmt]>="250" And [ExpectedPmt]<"500","4",II f([ExpectedPmt]>="500" And [ExpectedPmt]<"750","5",II f([ExpectedPmt]>="750" And [ExpectedPmt]<"1000","6","7" ))))))))
Based on the above, I would expect values of "73", "397", "772", and "1056" for [ExpectedPmt] to return values of "2", 4", 6", and "7" for [Score], respectively.
However, the query returns values of "5", "1", "7", and "1" for [Score], respectively. It seems that in the equation for [Score] it is only reading the first digit for both the value of [ExpectedPmt] and criteria value in the IIf statements.
Any help would be greatly appreciated.
FYI - My user skill-level is low and I have no programming experience...
Comment