Null variable values?

[Fredrated]

I am using access 2002.

I have created a class module which looks up information from tables for
other modules as needed. When told to look up information, it fetches the
data and stores it in variables until asked what the values are. The problem
is, if a field is null, access will not allow it to be stored in a variable.
If this was a form I would create a hidden text box and store it there, but a
class module has no controls.

Any suggestions on how to indicate that the field looked up is null?

One approach I often us is:
variable = IIF(IsNull(field),"",field)
The problem in this case is, the field I want to look up is a date, and
setting the date variable to "" results in a type mismatch error.

Any ideas? For instance, is there any such thing as a 'blank' or 'empty'
date value, like "" is an empty string value?
I have wanted to use a blank date at other times, but have not figured out
how.

(foxpro allows a programmer to set a date to " / / ")

Thanks in advance for any help.

Fred

 

[Albert D. Kallal]

I am using access 2002.

I have created a class module which looks up information from tables for
other modules as needed. When told to look up information, it fetches the
data and stores it in variables until asked what the values are.

Any particular reason you don't just dump (keep) the data into a record set
in code? A record set will also remove from you the burden of having to
write/define all of those variables. You really don't want to go back to the
old days of programming languages were you have to define a bunch of
variables to hold "data".

We now have this high performance data engine and you simply should just
load up that data to a eocrdset. Not only will this save coding time, a
recordset also supports null values....

The problem
is, if a field is null, access will not allow it to be stored in a
variable.

Not true, if you need to store null values, then simply declare the variable
as variant, and then you can stuff any value want in there, and it will
support null values also.

As I said it's sounds to me you should be using a reocrdset here anyway.....

 

[Fredrated]

Thanks for the info about variants, I will try that.
I seldom if ever use variants because I prefer strong-typing my data to
exactly what the system expects.

The reason I am not creating a recordset is because often many modules want
to look up the same data for various reasons. Instead of creating recordsets
everywhere, I create one class module that does the work, then every module
that needs the data just creates an instance of the lookup class and then
asks that object to get the data and pass it back. It results in pretty
clean code, as I only need to create the lookup code once but can use it in
many places.
Also, if I change the way I store the data, I only need to change the lookup
object, all the modules that use it can stay the same.

Thanks again.

Fred

 

[Jim Burke in Novi]

You could use the Nz function for your purposes. For a string variable you
would use something like

x = Nz(field,vbnullstring)

For a date field I use a constant with a 'dummy' date value that I know
doesn't represent a genuine date that I would be using, e.g. #1/1/1000#. Then
I check for that value rather than for a null value

Public const dummyDate as DAte = #1/1/1000# - declared in a module, then

x = Nz(dateField, dummyDate)

 

[Michel Walsh]

If you need a special value for a "blank" date, use Null. You see, Null
will possibly always be present (outer join, or otherwise), so you will
always have to deal with them. Next, if you ALSO have ANOTHER special value
which is not the Null value, you just end up with twice the amount of work
checking for something 'special'. As far as OUTPUT in a report, you can
still Format the value appropriately. As example:


? Format( 109, "#;(#);zero;N.A."), Format( -110, "#;(#);zero;N.A"),
Format( null, "#;(#);zero;N.A")
109 (110)
N.A


Sure, to handle a nullable value, in code, in VBA, you either keep the value
in a recordset, either use a VARIANT.

You can ASSERT to be sure the data type is what you intended:


Dim x AS variant
...
Debug.Assert (VarType(x) = vbDate) Or (x Is Null)


Vanderghast, Access MVP

 

[Michel Walsh]

better with IsNull(x), in VBA.