Coding NULL in a field

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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:[email protected]...
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

[Anonymous]

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:[email protected]...
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:[email protected]...
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

[Anonymous]

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


"Tony Burba" wrote in message
news:[email protected]...
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:[email protected]...
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:[email protected]...
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

[Anonymous]

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

[Anonymous]

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:[email protected]...
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:[email protected]...
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:[email protected]...
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

[Anonymous]

John,

Thanks for the useful information.

MNRaghu