Forum Discussion

jazzyelsie's avatar
jazzyelsie
Copper Contributor
Nov 06, 2024

MS Access Handling Nulls In Query Formula

Hi all,

 

I have this formula in Access: IIf([Init_Static_P] < 0 And (Nz([Adj_Static_P], -9999) < 0), "Resolved", "Not resolved" which keeps outputting an #Error when Adj_Static_P is null which will happen. I need the nulls in my dataset. Whenever Init_Static_P is < 0 and or Adj_Static_P is < 0, it works as it should outputting "Resolved" and vicer versa, however it does not work whenever there is any instance of Adj_Static_P as a blank value. Does anyone know how I can handle the nulls so that if the Init_Static_P is >0 and Adj_Static_P is null, then it would output "Unresolved", and if Init_Static_P is <0 and Adj_Static_P is null then it would output "Resolved" instead of #Error?

 

Output should be as follows: 

Init_Static_PAdj_Static_PResult
>0Null"Not resolved"
>0>0"Not resolved"
<0Null"Resolved"
<0 < 0"Resolved"

 

Thanks in advance! <3

2010
2013
2016
access
Access Web App
Access Web Database
  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    Nov 07, 2024

    maybe try:

    SELECT Init_Static_P, 
            Adj_Static_P, 
           Switch(Nz([Init_Static_P],0)>0,"Not Resolved",Nz([Init_Static_P],0)<=0,"Resolved") AS Result
FROM YourtTableName;

     

  • GustavBrock's avatar
    GustavBrock
    MVP
    Nov 07, 2024

    I guess your example output isn't representative or complete, as you can ignore Adj_Static_P:

    IIf([Init_Static_P] > 0, "Not resolved", "Resolved")

     

  • Ken_Sheridan's avatar
    Ken_Sheridan
    Brass Contributor
    Nov 08, 2024

    What about the following?

     

    >0        <0

    <0        >0

     

    As GustavBrock has pointed out, for the four criteria you have cited, the value of Adj_Static_P is immaterial if the ones you cite are are the only ones permissible.

     

    Also what if either/both = 0?

     

    If the possible values are constrained I’d suggest you enforce this by means of a table level Validation Rule, or by applying a CHECK CONSTRAINT to the table, preferably the former as the latter is not portable.

Resources