topic Re: Coding NULL in a field in VBA Forum

Coding NULL in a field

Anonymous — Wed, 01 Feb 2006 13:07:42 GMT

Hi all,

Any idea on coding NULL values in a field of MS Access?
Any small part of code or suggestion much appreciated.

Thanks,

MNRaghu

Re: Coding NULL in a field

Anonymous — Wed, 01 Feb 2006 13:14:03 GMT

Raghu,

Others like myself might need a bit more information.

Within Acces dbText types have a property to allow zero length strings. That will allow you to use Isnull() and VBNullString to determine if values exist within the data fields your evaluating.

Hope that helps but if you expand your request a little there are some talented people with DB experience in this NG that can help.

G'Luck

Bob Coward

Re: Coding NULL in a field

Anonymous — Wed, 01 Feb 2006 13:41:06 GMT

Not sure what you're trying to do.

If you're trying to enter null values in the Access field, you should be
able to just do

field.value = null

If you're having trouble dealing with null values coming out of the
database, you can do something like

If IsNull(field.value) then
myvar = ""
Else
myvar = field.value
End if

Hope this makes sense and that it helps you.

wrote in message news:5074125@discussion.autodesk.com...
Hi all,

Any idea on coding NULL values in a field of MS Access?
Any small part of code or suggestion much appreciated.

Thanks,

MNRaghu

Re: Coding NULL in a field

Anonymous — Wed, 01 Feb 2006 19:01:09 GMT

To take the latter process one step further:

I do a lot of programming involving both Access and AutoCad, so the issue of
null fields and their conflict with string variables comes up a lot. There's
an Nz function in Access VBA that checks whether a value is null, and if so
returns something else that you specify. I wrote a little function in
AutoCad VBA to do the same thing, as follows:

Public Function tl_Nz(v1 As Variant, v2 As Variant) As Variant
tl_Nz = v1
If IsNull(v1) Then
tl_Nz = v2
End If
End Function

When you're reading data out of a recordset in Autocad, you can do this:

Dim intVar as integer
intvar = tl_Nz(field.value, 0)

or

Dim strVar as string
intvar = tl_Nz(field.value, "")

That way you always get a useable value and no "Type Mismatch" or "Invalid
Input" errors.

"Tony Burba" wrote in message
news:5074161@discussion.autodesk.com...
Not sure what you're trying to do.

If you're trying to enter null values in the Access field, you should be
able to just do

field.value = null

If you're having trouble dealing with null values coming out of the
database, you can do something like

If IsNull(field.value) then
myvar = ""
Else
myvar = field.value
End if

Hope this makes sense and that it helps you.

wrote in message news:5074125@discussion.autodesk.com...
Hi all,

Any idea on coding NULL values in a field of MS Access?
Any small part of code or suggestion much appreciated.

Thanks,

MNRaghu

Re: Coding NULL in a field

Anonymous — Wed, 01 Feb 2006 19:03:55 GMT

The "intVar" in the second example should be "strVar," of course.

"Tony Burba" wrote in message
news:5074624@discussion.autodesk.com...
To take the latter process one step further:

I do a lot of programming involving both Access and AutoCad, so the issue of
null fields and their conflict with string variables comes up a lot. There's
an Nz function in Access VBA that checks whether a value is null, and if so
returns something else that you specify. I wrote a little function in
AutoCad VBA to do the same thing, as follows:

Public Function tl_Nz(v1 As Variant, v2 As Variant) As Variant
tl_Nz = v1
If IsNull(v1) Then
tl_Nz = v2
End If
End Function

When you're reading data out of a recordset in Autocad, you can do this:

Dim intVar as integer
intvar = tl_Nz(field.value, 0)

or

Dim strVar as string
intvar = tl_Nz(field.value, "")

That way you always get a useable value and no "Type Mismatch" or "Invalid
Input" errors.

"Tony Burba" wrote in message
news:5074161@discussion.autodesk.com...
Not sure what you're trying to do.

If you're trying to enter null values in the Access field, you should be
able to just do

field.value = null

If you're having trouble dealing with null values coming out of the
database, you can do something like

If IsNull(field.value) then
myvar = ""
Else
myvar = field.value
End if

Hope this makes sense and that it helps you.

wrote in message news:5074125@discussion.autodesk.com...
Hi all,

Any idea on coding NULL values in a field of MS Access?
Any small part of code or suggestion much appreciated.

Thanks,

MNRaghu

Re: Coding NULL in a field

Anonymous — Thu, 02 Feb 2006 05:09:59 GMT

Tony Burba,

Thanks for the suggestions. That helped me to solve my problem.

Bob Coward,
Thanks. I should have been more specific too. However, Tony has solved my problem for now.

Thanks,
MNRaghu

Re: Coding NULL in a field

Anonymous — Thu, 02 Feb 2006 14:24:08 GMT

I have found this useful lately. Assuming oRS is a DAO/ADO recordset.
If you are expecting a string:
s1 = iif(IsNull(oRS!FieldName), "", oRS!FieldName)
If you are expecting a double:
d1 = iif(IsNull(oRS!FieldName), 0#, CDbl(oRS!FieldName))
--
John Goodfellow
irtfnm
use john at goodfellowassoc dot com

"Tony Burba" wrote in message
news:5074624@discussion.autodesk.com...
To take the latter process one step further:

I do a lot of programming involving both Access and AutoCad, so the issue of
null fields and their conflict with string variables comes up a lot. There's
an Nz function in Access VBA that checks whether a value is null, and if so
returns something else that you specify. I wrote a little function in
AutoCad VBA to do the same thing, as follows:

Public Function tl_Nz(v1 As Variant, v2 As Variant) As Variant
tl_Nz = v1
If IsNull(v1) Then
tl_Nz = v2
End If
End Function

When you're reading data out of a recordset in Autocad, you can do this:

Dim intVar as integer
intvar = tl_Nz(field.value, 0)

or

Dim strVar as string
intvar = tl_Nz(field.value, "")

That way you always get a useable value and no "Type Mismatch" or "Invalid
Input" errors.

"Tony Burba" wrote in message
news:5074161@discussion.autodesk.com...
Not sure what you're trying to do.

If you're trying to enter null values in the Access field, you should be
able to just do

field.value = null

If you're having trouble dealing with null values coming out of the
database, you can do something like

If IsNull(field.value) then
myvar = ""
Else
myvar = field.value
End if

Hope this makes sense and that it helps you.

wrote in message news:5074125@discussion.autodesk.com...
Hi all,

Any idea on coding NULL values in a field of MS Access?
Any small part of code or suggestion much appreciated.

Thanks,

MNRaghu

Re: Coding NULL in a field

Anonymous — Fri, 03 Feb 2006 02:45:47 GMT

John,

Thanks for the useful information.

MNRaghu