CASE Statement not working

[skydivetom]

Good morning:

I need some assistance with updating a record set based on CASE statements (in a module). The attached ACCDB contains the following:

1. Table "tbl_PERSON" with 20 *example* records.
2. Form "F01_MainMenu"... containing a command button that calls function "Business Rules" (BR).


3. Module "mod_BusinessRules" which contains BR function.

Process:
- Open up form and click command button
- Based on the BR, I want to update field [PERS_INJURY_COST].

What I need some help with:
- First, I have NOT created a complete *sample* record set that is representative of *all* BR.
- At this time, I merely need to ensure I update the first two (2) records where [MSHP_CLASS_ID] = 1.

Issue:
- Record [ID] = 1 (with [MSHP_CLASS_ID] = 1) has [PERS_DATE_OF_DEATH] = Null. Based on the BR, I want [PERS_INJURY_COST] = $50,000 after executing the update routine.
- Record [ID] = 2 (with [MSHP_CLASS_ID] = 1) has [PERS_DATE_OF_DEATH] Not Null. Based on the BR, I want [PERS_INJURY_COST] = $100,000 after executing the update routine.
- At this time, however, the value in [PERS_INJURY_COST] does NOT get updated for neither 2 records.

Question:
- How do I need to modify the CASE statement (where CASE = 1) AND/OR the sub (nested) CASE statement so the either 50,000 or 100,000 will be applied to the 1st 2 records?

Thank you,
EEH

[June7]

Why is PERS_DATE_OF_DEATH field a text and not a date/time type? How could this field ever have 0 value?

0 is Access constant for False (or Not True because anything not -1 will be treated as False). So, your condition of Not 0 means NOT False, which evaluates to TRUE (-1).

However, if PERS_DATE_OF_DEATH is Null neither condition can be met because Null cannot be compared with anything since Null is nothing.

Instead, use:

Case Else

Or use IIf() instead of the inner Case.

With the data shown, both of those 2 records will match Case Else.

[skydivetom]

June:

Correct... "PERS_DATE_OF_DEATH" should be a date. However, source data type is set to "text" and won't be changed.

That said, I tried the IF version instead. Only the ELSE kicks in and updates the cost to $100k for both records. Not entirely certain as to why only the Else will be found.

My question: Given that one record has a value (i.e., although it's a date in text format, the record is not empty), why would only the Else "kick" in? How should I change the below that $50 will be added where a date (even though it's in text format) exist and the 2nd record receives a $100k value?

    Select Case rs!MSHP_CLASS_ID
    
        'Class A
        Case 1
                
            If rs!PERS_DATE_OF_DEATH = Null Then
            
                rs!PERS_INJURY_COST = 50000
                
            Else
                
                rs!PERS_INJURY_COST = 100000
            
            End If

[June7]

I said that would happen. The Else kicks in because Case 0 translates to Case False. Neither Null nor 4/22/2021 equate to False.

I also said cannot compare anything to Null. If s!PERS_DATE_OF_DEATH = Null is meaningless. Consider:

rs!PERS_INJURY_COST = IIf(Nz(rs!PERS_DATE_OF_DEATH, "") = "", 50000, 100000)

or

rs!PERS_INJURY_COST = IIf(IsDate(rs!PERS_DATE_OF_DEATH), 100000, 50000)

[skydivetom]

Thank you... that worked great! Appreciate the assistance.