ado Field null

CrakED
04-18-2004, 09:54 PM
I need my string to equal the following if my db field is NULL, I've searched the board, as this should be a simple fix, but I've tried everything and can't get it to work...it must be REALLy simple, but I'm not catching it.

If adoMRS.Fields("ClearName") = NULL Then
strCD = " "
strCN = " "

strT1 = strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " &adoMRS.Fields("Description")
End If

couch612
04-18-2004, 10:08 PM
assuming you are using Access:



If IsNull(adoMRS.Fields("ClearName")) then
strCD = " "
strCN = " "

strT1 = strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " &adoMRS.Fields("Description")
End If

CrakED
04-18-2004, 10:25 PM
Actually I'm using SQl, and that command did not work...my error states "Null value can't be used here"

couch612
04-18-2004, 10:40 PM
can you use the len function in SQL to retrieve the length of the data in the field?


if len(adoMRS.Fields("ClearName")) = 0 then
....

wengwashere
04-18-2004, 11:22 PM
Actually I'm using SQl, and that command did not work...my error states "Null value can't be used here"

If youre using SQL:

ISNULL() function

provides another option if the value passed is NULL. In this case, if firstname field of Customers table is NULL then, NOFIRSTNAME will be the data retrieved

SELECT ISNULL(firstname, 'NOFIRSTNAME') FROM Customers

but, if you want to compare a value and check if it is NULL, you use IS NULL :

SELECT * FROM Customers WHERE firstname IS NULL

this works in SQL Server (T-SQL). Not sure on access, but i think it does :)

MKoslof
04-19-2004, 08:20 AM
IS NULL or IS NOT NULL should work for all popular database engines: MS Server, Oracle, MS Access, Pervasive, MySQL, etc. It is one of the data type standards a database engineer can rely on :).

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum