Ignoring Blank fields

[Beccy Howard]

As part of a module I have the following code:

The problem is when I run the code, I get an error message
of - Invalid use of NULL. this is to do with the
LastDNADate field having blank entries in it. How do I
get around this?


cendate = #8/31/2003#

bookdate = Right$(rkb![REFREQDATE], 2) & "/" & Mid$(rkb!
_[REFREQDATE], 5, 2) & "/" & Mid$(rkb![REFREQDATE], 3, 2)

dnadate = Right$(rkb![LastDNAdate], 2) & "/" & Mid$(rkb!
_[LastDNAdate], 5, 2) & "/" & Mid$(rkb![LastDNAdate], 3, 2)

dnadate2 = DateValue(dnadate)

cendate2 = DateValue(cendate)

bookdate2 = DateValue(bookdate)

If LastDNAdate = "" Then
waittime = (cendate2 - bookdate2)
Else
waittime = (cendate2 - lastdnadate2)
End If

 

[martin]

You can either make a varible ie temp=nz(lastdnadate,"")
then use temp thru the program. The other way is just
instead of lastdnadate use nz(lastdnadate,"") all the way
thru. I only see it in the last if if this is so I'd use
the second method and replace it with nz(lastdnadate,"").
I inserted "!!! Here !!!" where you should do it.

Martin

-----Original Message-----
How do I actually go about doing this?

-----Original Message-----
You can use nz(lastdnadate,"") that will get rid of nulls
and change them to '""'.

Martin

-----Original Message-----
As part of a module I have the following code:

The problem is when I run the code, I get an error message
of - Invalid use of NULL. this is to do with the
LastDNADate field having blank entries in it. How do I
get around this?


cendate = #8/31/2003#

bookdate = Right$(rkb![REFREQDATE], 2) & "/" & Mid$(rkb!
_[REFREQDATE], 5, 2) & "/" & Mid$(rkb![REFREQDATE], 3, 2)

dnadate = Right$(rkb![LastDNAdate], 2) & "/" & Mid$(rkb!
_[LastDNAdate], 5, 2) & "/" & Mid$(rkb![LastDNAdate],

3,
2)

dnadate2 = DateValue(dnadate)

cendate2 = DateValue(cendate)

bookdate2 = DateValue(bookdate)
!!! Here !!!
If LastDNAdate = "" Then
waittime = (cendate2 - bookdate2)
Else
waittime = (cendate2 - lastdnadate2)
End If
.

.

.

 

[Joe Fallon]

If your data didn't have Nulls this would not be a problem.
One idea would be to run an update query and change all Nulls to "" or " ".

The error comes from using the Right or Left function on a Null instead of a
string.
Thus it is "invalid".

You can use the NZ function to switch from Null to Zero (nz) on the fly
rather than fixing your data.
(I prefer the data fix myself.)

e.g.
Right$(nz(rkb![REFREQDATE],""), 2) & "/" &


--
Joe Fallon
Access MVP

How do I actually go about doing this?

-----Original Message-----
You can use nz(lastdnadate,"") that will get rid of nulls
and change them to '""'.

Martin

-----Original Message-----
As part of a module I have the following code:

The problem is when I run the code, I get an error message
of - Invalid use of NULL. this is to do with the
LastDNADate field having blank entries in it. How do I
get around this?


cendate = #8/31/2003#

bookdate = Right$(rkb![REFREQDATE], 2) & "/" & Mid$(rkb!
_[REFREQDATE], 5, 2) & "/" & Mid$(rkb![REFREQDATE], 3, 2)

dnadate = Right$(rkb![LastDNAdate], 2) & "/" & Mid$(rkb!
_[LastDNAdate], 5, 2) & "/" & Mid$(rkb![LastDNAdate], 3, 2)

dnadate2 = DateValue(dnadate)

cendate2 = DateValue(cendate)

bookdate2 = DateValue(bookdate)

If LastDNAdate = "" Then
waittime = (cendate2 - bookdate2)
Else
waittime = (cendate2 - lastdnadate2)
End If
.

.