Still having Null issues

[la1814]

Gentlemen and Ladies,

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 there 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

 

[jdraw]

What are you trying to do in plain English? You have told us how you have done something that

still does not seem to work

 

[la1814]

If the date is within 14 days of its expiration it will place a '^' next to the individuals name. Also, if one of the other check boxes (AllRead and Discrepancies) is checked it will do the same. I have several dates in my database, some of which will not be required for some people so they naturally will be left blank. With past code I would always get the '^' if one of the date fields were blank...although all other factors did not meet requirements to flag '^' the name.. I am trying to prevent fields with no date from flagging '^' the name. Thanks so much for any assistance.

 

[namliam]

Perhaps it helps if you format your code properly...

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

When concatting strings in access you shouldnt use +, instead use the &

Also, if one of the other check boxes (AllRead and Discrepancies) is checked it will do the same.

The code as writen will add the ^ if either of the checkboxes is ticked, due to the OR in there.... If you only want the ^ if you have both ticked change OR to AND,
Same goes for the dates, if only one is <= 14 then it gets flagged....

Why in the hell do the use the variables by the way? They are quite useless to use this way....

If this doesnt help make a excel sheet for us... Make a column for each column in your database that has impact on this situation
Then write out all the combinations that you want to treat and what you want it to do.

 

[la1814]

The intent is to flag '^' if any one of the dates or yes/no fields are true (=yes). When I run this with a date outside of 14 (no flag) and the other date empty with both yes/no values false (no flag) it results in a flag. I believe the empty value is what results in the flag the way I have this written.

I am new to this...have been teaching myself over the last few months. How do I do away with the variables...thought they are needed to change the field values? Again, new to this and thank you for your help.

 

[namliam]

If this doesnt help make a excel sheet for us... Make a column for each column in your database that has impact on this situation
Then write out all the combinations that you want to treat and what you want it to do.

words only do so much... try making that spreadsheet.

 

[nanscombe]

I've attached a demo of what I think you are trying to achieve. Is this the sort of thing you are after?

Private Sub flagSim()
Dim blFlag As Boolean
Dim Days As Integer, Days1 As Integer
  
  ' Set flag to False
  blFlag = False
  
  ' Default date of 12/12/2099 for null value
  Days = DateDiff("d", Now, Nz(Me.PhysicalExpires, #12/12/2099#))
  
  ' Default date of 12/12/2099 for null value
  Days1 = DateDiff("d", Now, Nz(Me.EFExpires, #12/12/2099#))

  ' Set flag to True if Discrepancies is checked, default to False if null
  blFlag = blFlag Or Nz(Me.Discrepancies, False)
  
  ' Set flag to True if AllRead is checked, default to False if null
  blFlag = blFlag Or Nz(Me.AllRead, False)
  
  ' Set flag to True if Days >= 0 and Days < 14
  blFlag = blFlag Or ((Days >= 0) And (Days < 14))
  
  ' Set flag to True if Days1 >= 0 and Days1 < 14
  blFlag = blFlag Or ((Days1 >= 0) And (Days1 < 14))

  Me.NewNameLookup = Me.NameLookup
  
  ' Add ^ if flag is set to True
  If blFlag Then Me.NewNameLookup = Me.NewNameLookup & "^"
End Sub

 

[la1814]

Thanks so much for the help with this. Being as new as I am with this I had not looked at it in this way. Shows a much neater code as well. Great lessons. I am sure I will have many more trials and tribulations.

Nanscombe...you have gone above and beyond!!! I can't thank you enough.

Enjoy the rest of the weekend.