VB, invalid use of Null

[Cuc Tu]

I am using VB in Access to write out a file, but in the loop going through each record, if a record contains a blank field, I'd like to either stick some data there or skip the field entirely.


With rst

Do While .EOF = False

strCommand = ![Command]
strDescription = ![Description]
strQuery = ![Query]
strSyntax = ![Syntax]
strParameters = ![Parameters]
strRange = ![Range]
strDefault = ![Default]
strOutput = ![Output]
strExample = ![Example]

.MoveNext

fsoFile.WriteLine "<SCPICommand>" & strCommand
fsoFile.WriteLine "<Heading4>Description<Indented4>" & strDescription
fsoFile.WriteLine "<Heading4>Query<Indented4>" & strQuery
fsoFile.WriteLine "<Heading4>Syntax<Indented4>" & strSyntax
fsoFile.WriteLine "<Heading4>Parameters<Indented4>" & strParameters
fsoFile.WriteLine "<Heading4>Range<Indented4>" & strRange
fsoFile.WriteLine "<Heading4>Default<Indented4>" & strDefault
fsoFile.WriteLine "<Heading4>Output<Indented4>" & strOutput
fsoFile.WriteLine "<Heading4>Example<Indented4>" & strExample

Loop

End With


In this case, not all commands have parameters and the operation stops at

strParameters = ![Parameters]

with the invalid use of Null error.

I thought of an If null, then sort of statement, but can't quite complete the logic of it...

[Paul Komski]

I regularly use the following function written into a module.

Function NullToZero(anyValue As Variant) As Variant
' Accepts: a variant value
' Purpose: converts null values to zeros
' Returns: a zero or non-null value
' From: User's Guide Chapter 17
If IsNull(anyValue) Then
NullToZero = 0
Else
NullToZero = anyValue
End If
End Function

Then just alter the code to read something like:

strCommand = NullToZero(![Command])
strDescription = NullToZero(![Description])

etc .....

I use this so regularly that it is one of a number of self-written functions that I use in nearly every application I write.

PS
As you build-up a collection of your own favourite functions just keep them in one module. It is then very easy to simply import the module from one Db to another.

[Cuc Tu]

Cool! Does this have the same effect as if the field held "0"?

For example, a command can be sent with a parameter 0 or 1 or off or on, but some commands have no parameters, such as the reset command. In the latter case, I would prefer not to write out the Parameters --> Output fields.

If I set the function as NullToXXX, then I was thinking of using:

If [field] Not = XXX, then writeline...

But as I think about it, I should require some valiation on the data in the first place and not allow nulls.

[Paul Komski]

The effect is to simply substitute 0 for null wherever you call the function. That can be from other functions or routines, queries, SQL statements, macros, etc. It can thus very usefully prevent code from balking when it encounters an unintended null and it can also mean that mathematical calculations involving the field don't all resolve to null. It only takes one null in calculations for all additions, multiplications, etc to resolve to null; (which can have advantages and disadvantages). It is quite easy, of course, to write another function that does a ZeroToNull transposition if that were required. Division by Zero would always produce an error for example but division by null would resolve to null.

Writing your own specific functions is often a much better way of resolving encoding "problems" than having a lot of complicated code in one particular routine and the "logic" is often also then much easier to follow.

Specific validation for fields can be somewhat overcome by not allowing zero length strings in text fields or always ensuring that a default value is set. Once again that can have advantages and disadvantages. Another method is to correct a null entry with an after update routine: if isnull(xxx) then xxx equals something else or just exits the event or prompts for a correction. This tends to only work for bound controls because unbound controls don't have any underying data or fields to update - though giving an unbound control a default value will ensure that it is not null (to start with anyway).