Ignoring Blank fields

  • Thread starter Thread starter Beccy Howard
  • Start date Start date
B

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
 
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],
Click to expand...
3,
2)
dnadate2 = DateValue(dnadate)

cendate2 = DateValue(cendate)

bookdate2 = DateValue(bookdate)
!!! Here !!!
If LastDNAdate = "" Then
waittime = (cendate2 - bookdate2)
Else
waittime = (cendate2 - lastdnadate2)
End If
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
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



Beccy Howrd said:
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
.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top