Ignore Empty Cells While Calculating and Returning Number Formatted Values

[ABC2023]

Hello!

I have a set of data in Access (in short text format) with <numbers, decimals numbers, whole numbers, and empty cells. I need that set of data in a new calculated query field where the <numbers no longer have the < sign and are halved (or averaged with themselves), where decimal and whole numbers stay the same, and where the empty cells stay empty while converting them all to a number format (except the empty cells).

I can do this in Excel using a VBA nested IF formula:

The problem is that I need to do it in Access, but the ISBLANK function does not exist in SQL. In Access I’m using the Expression: Val(IIf(Left([field_name],1)=”<”,Val(Mid([field_name],2,4))/2,[field_name])), but this places 0’s in the new column where I need them to remain empty cells:

How can I write an expression that does what I have it doing in Excel, but do it in Access?

The reason I need those cells to remain empty is because I will be preforming calculations later in Excel with the newly calculated query field of data (which needs to be in number format to do so) and having those 0’s in the cells will alter the calculations because they’ll be factored in when they shouldn’t be. I can't just find and replace the 0's in Excel with nothing either, because some of the data points are actually 0's which do need to be factored in.

I have tried the IsNull and IsEmpty functions, and they don't work. I am very new to Access and SQL, so perhaps there is another way that I am not aware of.

 

[CJ_London]

it may be the value is a zls which is not null or empty

try the nz function

Iif(nz(myvalue,’’)=‘’, 0,1)

then sum rather that count

 

[plog]

You should write a custom function in a module to handle this.

Function get_Value(in_String)
' takes a string value, returns the proper numeric value in it

ret = 0    ' return value, default is 0, will be replaced below

if (Mid(in_String, 1, 1) = "<") Then
  ' string contains < symbol, will get numeric value and halve it

  'relevant code here 

else
  ' string is numeric value, will just convert it to a number and return it

   ' relevant code here

end if


get_Value = ret

End Function

The above is just a rough sketch out and you need to fill in the blanks. Then, you will need to use it in a query like so:

NumericValue: Iif(isnull([YourFieldNameHere]]=False, get_Value([YourFieldNameHere])

 

[Pat Hartman]

When you don't know whether the string contains null or a ZLS, use the concatenation method in your IIf()

IIf(SomeField & "" <> "", field has some value code, field is "blank" code)

PS, unless you actually want to allow a Zero Length String in your text field, set the Allow ZLS to No at the table definition level. This will save a lot of work going forward. If the field is not null, it has an actual value and is NEVER = "". This enables you to only having to check for null regardless of whether you are working with a string or a numeric value. Dates are numeric. They are NOT strings.