Invalid use of Null???

P

pablotx

Registered User.
Local time
Today, 13:33
Joined
Feb 20, 2003
Messages
79
Accidentally posted this in the "General" forum first. Need some help with this error message. I am not very good at creating event procedures and hope that someone can fix this for me. Thanks!!
Take a look at the following:



Private Sub Current_LON_AfterUpdate()
Dim avglon As Long
Select Case [Current LON]
Case 1
avglon = 30062.45
Case 5
avglon = 33351.09
Case 8
avglon = 39770.3
Case 6
avglon = 50530.54
Case 9
avglon = 100790.65
End Select
[Current] = avglon
[LON Cost Difference] = [Current] - [Approved]



End Sub

Private Sub Approved_LON_AfterUpdate()
Dim avglon As Long
Select Case [Approved LON]
Case 1
avglon = 30062.45
Case 5
avglon = 33351.09
Case 8
avglon = 39770.3
Case 6
avglon = 50530.54
Case 9
avglon = 100790.65
End Select
[Approved] = avglon
[LON Cost Difference] = [Current] - [Approved]


End Sub

Private Sub Requested_LON_AfterUpdate()
Dim avglon As Long
Dim possave As Long

Select Case [Requested LON]
Case 1
avglon = 30062.45
Case 5
avglon = 33351.09
Case 8
avglon = 39770.3
Case 6
avglon = 50530.54
Case 9
avglon = 100790.65
End Select
[Requested] = avglon
possave = [Requested] - [Approved]
If possave >= 0 Then
[LON Prev Expend] = possave
Else
[LON Prev Expend] = 0
End If
End Sub

Private Sub Denial_Letter_Date_AfterUpdate()
Dim avglon As Long
Dim possave As Long

Select Case [Approved LON]
Case 1
avglon = 30062.45
Case 5
avglon = 33351.09
Case 8
avglon = 39770.3
Case 6
avglon = 50530.54
Case 9
avglon = 100790.65
End Select
[Approved] = avglon
possave = [Requested] - [Approved]
If possave >= 0 Then
[LON Prev Expend] = possave
Else
[LON Prev Expend] = 0
End If
End Sub
 
If you are getting an invalid use of null try this

I would try to set a defaul value for avglon, you don't have a default for your select statement. So if [Current LON] doesn't fall into one of your cases, then avglon is never initialized. Then you would be trying to subtract that from something else. Try adding this at the bottom of each case statement:

Case Else
msgbox "Current LON is = " & [Current LON]

I bet you see that message box pop up.
 
Please don't post the same question in more than one forum. It just gets confusing.
 
Hi pablotx,

Try this :


Code: 
Private Sub Current_LON_AfterUpdate() 
Dim avglon As Long 
  Select Case [Current LON] 
  Case 1 
      avglon = 30062.45 
  Case 5 
      avglon = 33351.09 
  Case 8 
      avglon = 39770.3 
  Case 6 
      avglon = 50530.54 
  Case 9 
      avglon = 100790.65
  Case Else 
      msgbox "Current LON is = " & [Current LON]  
  End Select 
  
  [Current] = avglon 
  [LON Cost Difference] = [Current] - [Approved] 



End Sub 

Private Sub Approved_LON_AfterUpdate() 
Dim avglon As Long 

  Select Case [Approved LON] 
  Case 1 
      avglon = 30062.45 
  Case 5 
      avglon = 33351.09 
  Case 8 
      avglon = 39770.3 
  Case 6 
      avglon = 50530.54 
  Case 9 
      avglon = 100790.65
  Case Else 
      msgbox "Approved LON is = " & [Approved LON]  
  End Select 
  
  [Approved] = avglon 
  [LON Cost Difference] = [Current] - [Approved] 


End Sub 

Private Sub Requested_LON_AfterUpdate() 
Dim avglon As Long 
Dim possave As Long 

  Select Case [Requested LON] 
  Case 1 
      avglon = 30062.45 
  Case 5 
      avglon = 33351.09 
  Case 8 
      avglon = 39770.3 
  Case 6 
      avglon = 50530.54 
  Case 9 
      avglon = 100790.65
  Case Else 
      msgbox "Requested LON is = " & [Requested LON]  
  End Select 

  [Requested] = avglon 
  possave = [Requested] - [Approved] 
  
  If possave >= 0 Then 
      [LON Prev Expend] = possave 
  Else 
      [LON Prev Expend] = 0 
  End If 

End Sub 

Private Sub Denial_Letter_Date_AfterUpdate() 
Dim avglon As Long 
Dim possave As Long 

  Select Case [Approved LON] 
  Case 1 
      avglon = 30062.45 
  Case 5 
      avglon = 33351.09 
  Case 8  
      avglon = 39770.3 
  Case 6 
       avglon = 50530.54 
  Case 9 
      avglon = 100790.65 
  Case Else 
      msgbox "Approved LON is = " & [Approved LON] 
  End Select 

  [Approved] = avglon 
  possave = [Requested] - [Approved] 

  If possave >= 0 Then 
      [LON Prev Expend] = possave 
  Else 
      [LON Prev Expend] = 0 
  End If 

End Sub

This way, if your case doesn't match one of your selections, it will fall through and pop up a message box, and give you the actual value of what you are looking for. From here you should be able to figure out where to go... Good luck

Robert
 
Still not working

Tried your suggestion Robert and still received a Null statement.
I have changed the code a bit to try and remedy the null problem. It seems that I have been successful, but for some reason, my solution only worked once. I am trying to get 2 procedures to happen at close to the same time. If you take a look at the code, please tell me why it doesn't work. Can you have two procedures happen on the same field at the same time?

Code Below:

Private Sub Current_LON_AfterUpdate()
Dim avglon As Long
Select Case [Current LON]
Case 1
avglon = 30062.45
Case 5
avglon = 33351.09
Case 8
avglon = 39770.3
Case 6
avglon = 50530.54
Case 9
avglon = 100790.65
End Select
[Current] = avglon
[LON Cost Difference] = [Current] - [Approved]



End Sub

Private Sub Approved_LON_AfterUpdate()
Dim avglon As Long
Select Case [Approved LON]
Case 1
avglon = 30062.45
Case 5
avglon = 33351.09
Case 8
avglon = 39770.3
Case 6
avglon = 50530.54
Case 9
avglon = 100790.65
End Select
[Approved] = avglon
[LON Cost Difference] = [Current] - [Approved]


End Sub

Private Sub Requested_LON_AfterUpdate()
Dim avglon As Long
Dim possave As Long

Select Case [Requested LON]
Case 1
avglon = 30062.45
Case 5
avglon = 33351.09
Case 8
avglon = 39770.3
Case 6
avglon = 50530.54
Case 9
avglon = 100790.65
End Select
[Requested] = avglon
[Approved] = avglon
If IsNull(avglon) Then
[Requested] = 0
[Approved] = 0
End If
possave = [Requested] - [Approved]
If possave >= 0 Then
[LON Prev Expend] = possave
Else
[LON Prev Expend] = 0
End If
End Sub

Private Sub
Dim avglon As Long
Dim possave As Long

Select Case [Approved LON]
Case 1
avglon = 30062.45
Case 5
avglon = 33351.09
Case 8
avglon = 39770.3
Case 6
avglon = 50530.54
Case 9
avglon = 100790.65
End Select
[Approved] = avglon
If IsNull(avglon) Then
[Approved] = 0
End If
possave = [Requested] - [Approved]
If possave >= 0 Then
[LON Prev Expend] = possave
Else
[LON Prev Expend] = 0
End If
End Sub
[/B]
 
Re: Still not working

pablotx said:
Can you have two procedures happen on the same field at the same time?
Click to expand...
In spite of Intel's marketing, nothing happens at the same time in computers. If you want two things to happen on one event and want to keep the in separate routines, create a third routine that calls the other two and assign that to the On[whatever] event.

One glitch with the last version of the code posted is that the last sub has no label... from where is it supposed to be called?
Code: 
Private Sub 
Dim avglon As Long
Dim possave As Long

Select Case [Approved LON]
Case 1
avglon = 30062.45
Case 5
avglon = 33351.09
Case 8
avglon = 39770.3
Case 6
avglon = 50530.54
Case 9
avglon = 100790.65
End Select
[Approved] = avglon
If IsNull(avglon) Then
[Approved] = 0
End If
possave = [Requested] - [Approved]
If possave >= 0 Then
[LON Prev Expend] = possave
Else
[LON Prev Expend] = 0
End If
End Sub

--Nameless Mac
 
Sorry about that, the last sub is:
Private Sub Approved_LON_Got Focus()

The 1st two subs seem to work fine all the time, but unfortunately the last two don't. Thanks for any assistance.
 
pablotx said:
The 1st two subs seem to work fine all the time, but unfortunately the last two don't. Thanks for any assistance.
Click to expand...

Since the two that don't check for IsNull do work and the two that do check don't... I'd reiterate the suggestion of setting a default value of 0 for avgloan before the select statement.

Keep in mind that Access treats 0 values, blanks, and Nulls differently. Checking IsNull is not the same as saying if=0. Check Access help for info on the nz() function, and search these forums using the string "nz* AND null AND *0*" as criteria.

Keep us posted.

--IsNotNull Mac
 
You must log in or register to reply here.

Similar threads

K
Self-Healing Ribbon Pointer
Replies
0
Views
477
KevinBaker
K
A
Filter Records
Replies
1
Views
561
June7
June7
J
Solved Conditional formatting without conditional formatting
Replies
6
Views
530
John Sh
J
C
Change backcolor on account change
Replies
11
Views
831
ClaraBarton
C
zelarra821
Solved Protect VBA Project Programmatically with SendKeys
Replies
12
Views
1,637
zelarra821
zelarra821

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom