本文介绍了在Access数据库中将Null转换为零的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

考虑到 Grade.firstExam,Grade.secondExam, Grade.finalExam 都是TEXT,而不是数字,我无法理解将空值转换为零的确切解决方案.我已经尝试使用NZ(),但它返回错误消息(表达式中未定义的函数"NZ")即时通讯使用VB.net和MS Access作为我的数据库.我还导入了 System.Data.OleDb 命名空间.

Considering that Grade.firstExam, Grade.secondExam, and Grade.finalExam are all TEXT and not numbers, i can't get the exact solution to convert null values into Zeros. I already tried to use NZ() but it returns an error message (Undefined Function 'NZ' in expression)im using VB.net with MS Access as my database. i also imported System.Data.OleDb Namespace.

    SQLStatement = " SELECT     Student.idNumber as 'IDno', "
    SQLStatement &= "           Student.lastName + ', ' + Student.firstName as 'FullName', "
    SQLStatement &= "           Grade.firstExam as 'firstExam', "
    SQLStatement &= "           Grade.secondExam as 'secondExam', "
    SQLStatement &= "           Grade.finalExam as 'finalExam', "
    SQLStatement &= "           ((CDBL(Grade.firstExam) + CDBL(Grade.secondExam) + CDBL(Grade.finalExam) + CDBL(Grade.finalExam)) / 4) as 'Average' "
    sqlstatement &= "FROM       Student LEFT OUTER JOIN Grade ON "
    sqlstatement &= "           Student.idNumber = Grade.idNumber "
    sqlstatement &= "WHERE      Student.idNumber = '" & StudentID & "' "
    SQLStatement &= "ORDER BY   ((CDBL(Grade.firstExam) + CDBL(Grade.secondExam) + CDBL(Grade.finalExam) + CDBL(Grade.finalExam)) / 4) DESC"

如何转换字段中存在的空字符串或null?

How can i convert empty string or null that exist in the field?

推荐答案

在您的SQL中,尝试执行以下操作:

In your SQL try something like this:

SQLStatement &= "          IIf(IsNull(Grade.firstExam),'0',Grade.firstExam) as 'firstExam', "

这篇关于在Access数据库中将Null转换为零的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-31 19:25