Turn Null-errors off

  • Thread starter Thread starter Benny
  • Start date Start date
B

Benny

In a db-project (Access 2000) with VBA i've written code to examen
fieldvalues. I get a lot of "Unauthorised use of Null (Error 94)", it is
normal when I examen a field that is empty (has no value in it, what is
normal in a db with - by example telephonenumbers - ie not everyone has a
telefone). Can I turn this errors off in the entire project ?

now I bypass these errors with the following code :

Function fncGetPrefix(strTelNum As String) As String
Dim intStop As Integer
If Not IsNull(strTelNum) Then 'or: If txtPatEmail <>
vbNullString Then
intStop = InStr(1, strTelNum, ".")
fncGetPrefix = Left(strTelNum, intStop - 1)
Else
fncGetPrefix = ""
End If
End Function

a lot of writing for a normal value of "" in a field.

Thanks in advance,

Benny
 
Use the Nz function to substitute Null with a default value -- for your
example, the "." text string should work nicely.

Function fncGetPrefix(strTelNum As String) As String
Dim intStop As Integer
intStop = InStr(1, Nz(strTelNum, "."), ".")
fncGetPrefix = Left(strTelNum, intStop - 1)
End Function
 
Hi Ken,

Thanks for the quick answer, but my problmen is not solved for 100%. I cal
the function in a From_Open event and there the things going wrong:

Private Sub Form_Open(Cancel As Integer)
Me.txtPatTelPrfx = modMain.fncGetPrefix(PatTel) <<- -here I get
the error
End Sub

In my module "modMain" there's the function like you recommended

Function fncGetPrefix(strTelNum As String) As String
Dim intStop As Integer
intStop = InStr(1, Nz(strTelNum, "."), ".")
fncGetPrefix = Left(strTelNum, intStop - 1)
End Function

Thanks for you're time,

Benny
 
The problem is that fncGetPrefix is expecting a string, and you're not
passing it one. (A string variable cannot hold a Null value).

Your options are either to check before calling the function and not call it
if the value is Null

Private Sub Form_Open(Cancel As Integer)
If IsNull(PatTel) <> False Then
Me.txtPatTelPrfx = fncGetPrefix(PatTel)
End If
End Sub

or to add a null string ("") to your value, to ensure that it's acceptable
to the function:

Private Sub Form_Open(Cancel As Integer)
Me.txtPatTelPrfx = fncGetPrefix(PatTel & vbNullString)
End Sub

If you do the latter, you'll need to change fncGetPrefix properly, so that
it can handle zero-length strings:

Function fncGetPrefix(strTelNum As String) As String
Dim intStop As Integer
intStop = InStr(1, strTelNum, ".")
If intStop > 0 then
fncGetPrefix = Left(strTelNum, intStop - 1)
End If
End Function

(Actually, that change to fncGetPrefix is probably a good idea anyhow, just
in case the string being passed to it doesn't have a period in it)
 
Thanks Douglas, my problem is solved !!

Douglas J. Steele said:
The problem is that fncGetPrefix is expecting a string, and you're not
passing it one. (A string variable cannot hold a Null value).

Your options are either to check before calling the function and not call it
if the value is Null

Private Sub Form_Open(Cancel As Integer)
If IsNull(PatTel) <> False Then
Me.txtPatTelPrfx = fncGetPrefix(PatTel)
End If
End Sub

or to add a null string ("") to your value, to ensure that it's acceptable
to the function:

Private Sub Form_Open(Cancel As Integer)
Me.txtPatTelPrfx = fncGetPrefix(PatTel & vbNullString)
End Sub

If you do the latter, you'll need to change fncGetPrefix properly, so that
it can handle zero-length strings:

Function fncGetPrefix(strTelNum As String) As String
Dim intStop As Integer
intStop = InStr(1, strTelNum, ".")
If intStop > 0 then
fncGetPrefix = Left(strTelNum, intStop - 1)
End If
End Function

(Actually, that change to fncGetPrefix is probably a good idea anyhow, just
in case the string being passed to it doesn't have a period in it)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



it
Click to expand...
 
Back
Top