Null Values

[la1814]

Gentlemen and Ladies,

I am having issues with this one...

If there is no value in the Dim it will result in:
Me.NameLookup + " ^"

For instance, if I have not added a date for Me.PhysicalExpires yet when it gets to the If (Days<=14) Then it ends up giving me the 'Then' statement...however, I want it to go to the 'Else' for any fields that are not populated with dates yet.

At a loss...

 Private Sub Discrepancies_AfterUpdate()
    'updates Flags
    
    Dim Days As Integer
    Dim Days1 As Integer
    Dim Days2 As Integer
    Dim Days3 As Integer
    Dim Days4 As Integer
    Dim Days5 As Integer
    Dim Days6 As Integer
    Dim Days7 As Integer
    Dim Days8 As Integer
    Dim Days9 As Integer
    Dim Days10 As Integer
    Dim Days11 As Integer
    
    On Error Resume Next
    Days = DateDiff("d", Now, Me.PhysicalExpires)
    Days1 = DateDiff("d", Now, Me.EFExpires)
    Days2 = DateDiff("d", Now, Me.ADExpires)
    Days3 = DateDiff("d", Now, Me.34CExpires)
    Days4 = DateDiff("d", Now, Me.INSTExpires)
    Days5 = DateDiff("d", Now, Me.PhysExpires)
    Days6 = DateDiff("d", Now, Me.SeatExpires)
    Days7 = DateDiff("d", Now, Me.LabExpires)
    Days8 = DateDiff("d", Now, Me.AFExpires)
    Days9 = DateDiff("d", Now, Me.CExpires)
    Days10 = DateDiff("d", Now, Me.FireExpires)
    Days11 = DateDiff("d", Now, ReviewDateExpire)
    
    If (Me.Discrepancies = True) Or (AllRead = True) Then
        Dim Flagit As String
        Flagit = Me.NameLookup + " ^"
        Me.NewNameLookup = Flagit
        
    Else
        Dim Flagged As String
        Flagged = Me.NameLookup
        Me.NewNameLookup = Flagged
    
    If (Days <= 14) Or (Days1 <= 14) Or (Days2 <= 14) Or (Days3 <= 14) Or (Days4 <= 14) Or (Days5 < 14) Or (Days6 <= 14) Or (Days7 <= 14) Or (Days8 <= 14) Or (Days9 <= 14) Or (Days10 <= 14) Or (Days11 <= 14) Then
        Dim Flagging As String
        Flagging = Me.NameLookup + " ^"
        Me.NewNameLookup = Flagging
    Else
   
    End If
    End If
     Form.Refresh
    
End Sub

Thanks for helping this newbie.

 

[namliam]

The problem basicaly lies within the OR part of the if's... if any of those is true it will go into your "true" part.

 

[Old Man Devin]

If its the value not always being input that is a problem you might want to play with Nz functions. Like have

Nz(Me.PhysicalExpires,Date(Now())) which means if Me.PhysicalExpires is null, just use the current date.

 

[la1814]

Thanks Gents. Appreciate the help. Have a good evening.

 

[la1814]

Gentlemen,

Here is what I have come up with but it still does not seem to work? Note, for troubleshooting purposes Dim Days does have a date value and Days1 field is empty. Discrepancies and AllRead are set to False. Result is Me.NameLookup + " ^", however it should be Me.NameLookup. Thanks for any assistance.

The intent is to not Flag with "^" if their is no date value in EFExpires.

 Private Sub Sim_AfterUpdate()

     Dim Days As Integer
    Dim Days1 As Integer
    
    On Error Resume Next
    Days = DateDiff("d", Now, Me.PhysicalExpires)
    
    If IsNull(Me.EFExpires) Then
        Days1 = 20
        
        Else
        Days1 = DateDiff("d", Now, EFExpires)
     
    If (Me.Discrepancies = True) Or (AllRead = True) Then
        Dim Flagit As String
        Flagit = Me.NameLookup + " ^"
        Me.NewNameLookup = Flagit
        
    Else
        Dim Flagged As String
        Flagged = Me.NameLookup
        Me.NewNameLookup = Flagged
        
    If (Days <= 14) Or (Days1 <= 14) Then
        Dim Flagging As String
        Flagging = Me.NameLookup + " ^"
        Me.NewNameLookup = Flagging
    Else
        Dim Flaggert As String
        Flaggert = Me.NameLookup
        Me.NewNameLookup = Flaggert
   
    End If
     End If
    End If
  
     Form.Refresh
    
End Sub

 

[Old Man Devin]

Is the problem still caused by input dates being null? I don't think the code you have posted actually protects against them. E.g. is Me.PhysicalExpires is null, the date diff will give the number of days between now and the default 'zero date' which is in 1900. Probably not what you want.

Also, this may just be a formating thing, but the way you have lain it out suggests you intend for all three of those if blocks to be run seperately. But because you have the 'End If' lines all at the end, it will run them as nested functions. For example, the second IF is currently running inside the 'Else' part of the first IF.

If that is the problem, just put the end of one if before the start of the next. If that's not it, then I'll just say that it is traditional to indent blocks that are nested inside the blocks above them. e.g.

If thing = True Then
    MsgBox "Nothing"
Else
    If otherthing = True Then
       MsgBox "Absolutely Nothing"
    End If
End If

 

[jdraw]

la1814,


Is this the same topic as
http://www.access-programmers.co.uk/forums/showthread.php?t=259738.

If so, don't keep adding new threads. Don't make duplicate posts, or cross post in separate forums without advising the reader.
Focus on one thread. Ask more questions; review what has been given.

Here are a couple of links that you should become familiar with.

On Error Resume Next is a useless Error handling technique.

Debugging
Error Handling

Good luck

 

[la1814]

Jdraw...apologies on the second thread. Oversight.

Everyone, appreciate the guidance and help here. Steep learning curve. Have a good evening.