CODEWITHSUNDEEP
sqlvb.netms-access
Donncha
Donncha

Reputation: 43

Access to VB SQL Statements

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

Answers (1)

jleach
jleach

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

Related Questions