Reputation: 43
I am trying to bring data from Access over to VB using the TableAdapter Configuration Wizard. Everything in my database is set as string and I need to keep it that way. I need to add 3 figures together and get the average of each figure in each row. My issue is that it is joining the string together rather than adding the 3 figures.
SELECT
ID, StudentID, FirstName, Surname, MCQ, Project,
Exam, [Password], IIF(ISNULL(MCQ), 'Not Completed', MCQ) AS Expr2,
IIF(ISNULL(Project), 'Not Completed', Project) AS Expr3,
IIF(ISNULL(Exam), 'Not Completed', Exam) AS Expr4,
IIF(ISNULL(MCQ), 1, MCQ) + IIF(ISNULL(Project), 1, Project) + IIF(ISNULL(Exam), 1, Exam) AS Expr5
FROM
tblStudents
Some columns will be null and this is why I an using the IsNull
function.
Any suggestions from anyone as a way around this?
Upvotes: 1
Views: 72
Reputation: 7800
Why, oh why would you need to keep everything stored as a string? Anyway, wrap the IIF() statements with a CDBL() or CINT() or some cast function to force them to a numeric type for the purpose of adding the values instead of concatenating them:
SELECT
ID, StudentID, FirstName, Surname,
MCQ, Project, Exam, [Password],
IIF(ISNULL(MCQ), 'Not Completed', MCQ) AS Expr2,
IIF(ISNULL(Project), 'Not Completed', Project) AS Expr3,
IIF(ISNULL(Exam), 'Not Completed', Exam) AS Expr4,
CINT(IIF(ISNULL(MCQ), 1, MCQ)) +
CINT(IIF(ISNULL(Project), 1, Project)) +
CINT(IIF(ISNULL(Exam), 1, Exam) AS Expr5))
FROM tblStudents
Upvotes: 3