We help IT Professionals succeed at work.

How can I check if a field is null and whether it is or not, still run the code

gdunn59
gdunn59 asked
on
308 Views
Last Modified: 2017-05-02
How can I check if a field is null and whether it is or not, still run the code?

The field "Fluid_Type" was null in one of the records of the recordset (rst), so it gave an error:


Here is my code:

Code:
Function GetSummaryFluid(Optional bNoTotal As Boolean = False) As String
  On Error GoTo ErrHandler
  Dim rst As New ADODB.Recordset
  Dim strOut As String
  Dim dSum As Double
  
  rst.CursorLocation = adUseClient
  rst.CursorType = adOpenForwardOnly
  rst.LockType = adLockReadOnly
  
  rst.Open "FRAC_FLUID_SUM", CurrentProject.Connection 'NOTE: need order?
  
  Do Until rst.EOF
    strOut = strOut & Round(rst.Fields("FLUID_PUMPED_SUM")) & " BBL " & rst.Fields("FLUID_TYPE") & ","
    dSum = dSum + rst.Fields("FLUID_PUMPED_SUM")
    
    rst.MoveNext
  Loop
  
  strOut = Left(strOut, Len(strOut) - 1) 'ditch extra ,
  If bNoTotal = False Then
    strOut = strOut & "," & Round(dSum) & " BBL TOTAL FLUID"
  End If
  
  GetSummaryFluid = strOut
  rst.Close
ErrHandler:
  Set rst = Nothing
  Call ErrorHandler(err, "GetSummaryFluid")
End Function

Open in new window

Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
If Not IsNull(rst.Fields("FLUID_TYPE")) Then
strOut = strOut & Round(rst.Fields("FLUID_PUMPED_SUM")) & " BBL " & rst.Fields("FLUID_TYPE") & ","
    dSum = dSum + rst.Fields("FLUID_PUMPED_SUM")
End If

Open in new window

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
When you need to do arithmetic on fields that can be null, you can use the Nz() function (Null to Zero) to resolve the problem without using an If statement or IIF() function.

strOut = strOut & Round(Nz(rst.Fields("FLUID_PUMPED_SUM"),0)) & " BBL " & rst.Fields("FLUID_TYPE") & ","
dSum = dSum + Nz(rst.Fields("FLUID_PUMPED_SUM"), 0)

Author

Commented:
Sorry, I was trying to add more to the question, but I guess you guys jumped on it.

Thanks for the speedy assistance.

gdunn59

Author

Commented:
I probably should have it check whether both fields that are doing the concatenation are null or not.

The error I was getting was as follows:

Error: 5

GetSummaryFluid
Invalid procedure call or argument

Thanks,
gdunn59

Author

Commented:
So how could I have it check both?

Thanks,
gdunn59
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015

Commented:
if not (rst!FLUID_TYPE is null or rst!FLUID_PUMPED_SUM is null) then
   ' statement(s) ... what you want to do only if there are values
end if

Open in new window

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Two other things to be aware of:

1. NZ() can also handle strings:  NZ(<some expression>, "")   would give you a zero length string

2. With strings, when you concatenate, if you use a plus sign, nulls are propagated through.   If you use an &, you end up with a string.

  So you can do something like this:

  [FirstName] & " " & [LastName] & (", " + [Title])

  If [Title] is a null, the whole expression in the () is a Null (you don't get the comma).    But because of the last &, you still end up with a string with the name.

Jim.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
You might consider creating a message string (strMsg) that would identify the record where either of those values is NULL.  I mean, working in the oil industry (my assumption), I cannot imagine having a [Fluid_Type] column in a table or a query returning a NULL, and if it did, I would want to know what record it was so that I could resolve the issue.

I would continue to add to this message until my recordset loop is complete, then would provide a way to view and/or print out the message.

Dale

Author

Commented:
I just can't seem to get it to work.  When it gets to Line 13 of the Code it skips over everything and goes to the ErrorHandler and produces the following error:

Error: 424

GetSummaryFluid
Object required

Here is the code I'm trying now:
Function GetSummaryFluid(Optional bNoTotal As Boolean = False) As String
  On Error GoTo ErrHandler
  Dim rst As New ADODB.Recordset
  Dim strOut As String
  Dim dSum As Double
  
  rst.CursorLocation = adUseClient
  rst.CursorType = adOpenForwardOnly
  rst.LockType = adLockReadOnly
  
  rst.Open "FRAC_FLUID_SUM", CurrentProject.Connection 'NOTE: need order?

If Not (rst.Fields("FLUID_PUMPED_SUM") Is Null Or rst.Fields("FLUID_TYPE") Is Null) Then

  Do Until rst.EOF
    strOut = strOut & Round(rst.Fields("FLUID_PUMPED_SUM")) & " BBL " & rst.Fields("FLUID_TYPE") & ","
    dSum = dSum + rst.Fields("FLUID_PUMPED_SUM")
    
    rst.MoveNext
  Loop
  
End If
  
  strOut = Left(strOut, Len(strOut) - 1) 'ditch extra ,
  If bNoTotal = False Then
    strOut = strOut & "," & Round(dSum) & " BBL TOTAL FLUID"
  End If
  
  GetSummaryFluid = strOut
  rst.Close
ErrHandler:
  Set rst = Nothing
  Call ErrorHandler(err, "GetSummaryFluid")
End Function

Open in new window


Thanks,
gdunn59
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
"Is Null" is used in SQL.  In VBA, you would use the IsNull() function.  I'm surprised you are not getting a compile error.

I thought you said you wanted to run the code regardless of whether the field was null or not.  If that is still true, then you need to use my suggestion which fixes up the null value temporarily so the code will execute correctly even when the field in question is null.  If substituting zero for null doesn't solve your problem, then maybe you really want to skip the code when one or both of the fields is null.

Author

Commented:
PatHartman,

You're correct, I do want to run it regardless of whether the two fields are null or not.

I tried your suggestion (see code below), but I'm still getting the error:

Error: 5

 GetSummaryFluid
 Invalid procedure call or argument

Function GetSummaryFluid(Optional bNoTotal As Boolean = False) As String
  On Error GoTo ErrHandler
  Dim rst As New ADODB.Recordset
  Dim strOut As String
  Dim dSum As Double
  
  rst.CursorLocation = adUseClient
  rst.CursorType = adOpenForwardOnly
  rst.LockType = adLockReadOnly
  
  rst.Open "FRAC_FLUID_SUM", CurrentProject.Connection 'NOTE: need order?

  Do Until rst.EOF
    strOut = strOut & Round(Nz(rst.Fields("FLUID_PUMPED_SUM"), 0)) & " BBL " & rst.Fields("FLUID_TYPE") & ","
    dSum = dSum + Nz(rst.Fields("FLUID_PUMPED_SUM"), 0)
    
    rst.MoveNext
  Loop
  
  strOut = Left(strOut, Len(strOut) - 1) 'ditch extra ,
  If bNoTotal = False Then
    strOut = strOut & "," & Round(dSum) & " BBL TOTAL FLUID"
  End If
  
  GetSummaryFluid = strOut
  rst.Close
ErrHandler:
  Set rst = Nothing
  Call ErrorHandler(err, "GetSummaryFluid")
End Function

Open in new window

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
What line is raising the error?

Author

Commented:
PatHartman,

Line 20 of the code is where the Run-time error '5': Invalid Procedure call or argument, is happening.

Thanks,
gdunn59

Author

Commented:
PatHartman,

I tried your suggestion and I'm still getting the same error on the same line 20 of the code:

    strOut = strOut & Round(Nz(rst.Fields("FLUID_PUMPED_SUM"), 0)) & " BBL " & rst.Fields("FLUID_TYPE") & ","
    dSum = dSum + Nz(rst.Fields("FLUID_PUMPED_SUM"), 0)

Thanks,
gdunn59
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Does strOut have a value?

From any code module, open Tools/References.  Is any reference marked as missing?

Author

Commented:
No, strOut does not have a value.  It shows "".

Checked the References, nothing marked as missing.

If the rst.Fluid_Type has a value then strOut evaluates fine, but if the rst.Fluid_Type is null/empty, then I get the run-time error 5.

Thanks,
gdunn59
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
If strOut might be empty, check that first.
If strOut & "" = "" Then
Else
    strOut = Left(strOut, Len(strOut) - 1) 'ditch extra ,
End If

Open in new window

Author

Commented:
Basically, what I need it to do is if there is a value in the rst.Fluid_Type then the strOut concatenation needs to be as follows:

    strOut = strOut & Round(rst.Fields("FLUID_PUMPED_SUM")) & " BBL " & rst.Fields("FLUID_TYPE") & ","
    dSum = dSum + rst.Fields("FLUID_PUMPED_SUM")

Open in new window


Otherwise, the strOut concatenation needs to be this (excluding the Fluid_Type):
    strOut = strOut & Round(rst.Fields("FLUID_PUMPED_SUM")) & " BBL " & ","
    dSum = dSum + rst.Fields("FLUID_PUMPED_SUM")

Open in new window


Thanks,
gdunn59

Author

Commented:
It's not the entire string that is empty, it is just that if the rst!FLUID_TYPE is empty then I get the run-time error 5.

Thanks,
gdunn59
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
What happened to the Nz()?  Everytime you use a field that might be null in an arithmetic expression, you MUST use Nz() to make it a 0 (or some other default value)

Author

Commented:
I tried that also, still get the run-time error 5.

Thanks,
gdunn59
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Please post the code as it is now.

Author

Commented:
ok. I'm in a meeting right now.  I'll do it in about 30 minutes or so.

Thanks,
gdunn59

Author

Commented:
PatHartman:

Here is the full Code:
Function GetSummaryFluid(Optional bNoTotal As Boolean = False) As String
'  'On Error GoTo ErrHandler
  Dim rst As New ADODB.Recordset
  Dim strOut As String
  Dim dSum As Double
  
  rst.CursorLocation = adUseClient
  rst.CursorType = adOpenForwardOnly
  rst.LockType = adLockReadOnly
  
  rst.Open "FRAC_FLUID_SUM", CurrentProject.Connection 'NOTE: need order?
  
  Do Until rst.EOF
    strOut = strOut & Round(rst.Fields("FLUID_PUMPED_SUM")) & " BBL " & rst.Fields("FLUID_TYPE") & ","
    dSum = dSum + rst.Fields("FLUID_PUMPED_SUM")
    
    rst.MoveNext
  Loop
  
  strOut = Left(strOut, Len(strOut) - 1) 'ditch extra ,
  If bNoTotal = False Then
    strOut = strOut & "," & Round(dSum) & " BBL TOTAL FLUID"
  End If
  
  GetSummaryFluid = strOut
  rst.Close
ErrHandler:
  Set rst = Nothing
  Call ErrorHandler(err, "GetSummaryFluid")
End Function

Open in new window


Thanks,
gdunn59

Author

Commented:
PatHartman,

This version of the code doesn't have the NZ code you posted, but I did try it and still got the same run-time error 5.

Thanks,
gdunn59
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
I really can't debug code that I can't see.  Perhaps you can post the database.

Author

Commented:
PatHartman,

Because of proprietary reasons, I can't post the entire database.

Any other suggestions?

Thanks,
gdunn59

Author

Commented:
PatHartman,

That particular Function isn't calling any other Functions.

Thanks,
gdunn59
Remote Training and Programming
CERTIFIED EXPERT
Top Expert 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
crystal,

That didn't work 100%.  It isn't concatenating all the text like it was before.

Thanks,
gdunn59
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015

Commented:
"It isn't concatenating all the text"

yes that is the reason to test the value to see if it is not null.   If you want all values added even if they are null, then instead of testing not isnull(!FLUID_PUMPED_SUM), you can wrap the value with NZ, which will allow it to be added even if it not filled out:
vOut  = (vOut + ", ") &  nz(!FLUID_PUMPED_SUM,0) & " bbl"

Open in new window

you should also use this when you calculate dSum.

hopefully you can see where to make the changes ... if not, ask ~ but try it yourself first and post your code if it doesn't work

Author

Commented:
crystal,

To maybe get a better understanding of what I'm trying to accomplish, please look at my Posting ID: 42082349 above, which discusses what I'm trying to accomplish.

Thanks,
gdunn59

Author

Commented:
Crystal,

I think we were pushing Submit at the same time.  I'll try your last suggestion.

Thanks,
gdunn59

Author

Commented:
crystal,

Now I'm getting the error:

  Invalid Use of Null on this Line of Code in the Function GetSummaryFluid:

      GetSummaryField = vOut
      rst.Close

Thanks,
gdunn59
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015

Commented:
glad you are trying yourself ~ please incorporate my comment about "to specify the return value". It is possible that nothing was added. vOut is a variant and can be Null, so it is important to test that before you assign the return value for the function since it is defined to be a string (you could change this) ... besides, only if the concatenated string is not null, do you want to report the sum (no need to add this in if the concatenated vOut variable has no value).

Also, perhaps close rst as soon as you are done with it -- this would be right after the loop.  You could put .Close right before End With

you could also incorporate Format when you loop if desired. For example:
Format (nz(!FLUID_PUMPED_SUM,0) , "#,##0.#")

Open in new window

Author

Commented:
Crystal,

I'm out of the office now, and will be out of town until Tuesday, next week.  

I will try your other suggestions then, and will post the outcome and if I need further assistance.

Thanks much for all your assistance with this!

gdunn59
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015

Commented:
you're welcome ~ enjoy your time away

Author

Commented:
Crystal,

I'm back in the office, and here is what I have, but I'm still getting an error "Invalid Use of Null" on Line 31 of the Code:
Function GetSummaryFluid(Optional bNoTotal As Boolean = False) As String
'  'On Error GoTo ErrHandler
  Dim rst As New ADODB.Recordset
  Dim vOut As Variant
  Dim dSum As Double
  
  vOut = Null
  dSum = 0
    
  rst.CursorLocation = adUseClient
  rst.CursorType = adOpenForwardOnly
  rst.LockType = adLockReadOnly
  
  rst.Open "FRAC_FLUID_SUM", CurrentProject.Connection  
  
With rst
   Do While Not .EOF
      If Not IsNull(!FLUID_PUMPED_SUM) Then
        dSum = dSum + !FLUID_PUMPED_SUM
          vOut = (vOut + ", ") & !FLUID_PUMPED_SUM & " BBL " & ","
          If Not IsNull(!FLUID_TYPE) Then
             vOut = vOut & " " & !FLUID_TYPE
          End If
      End If
     .MoveNext
   Loop
   rst.Close
End With
  
  vOut = Nz(Left(vOut , Len(vOut ) - 1), 0) 'ditch extra ,
  If bNoTotal = False Then
    vOut = vOut & "," & Round(dSum) & " BBL TOTAL FLUID"
  End If
   
   If IsNull(vOut ) Then
      GetSummaryFluid = ""
   Else
      GetSummaryFluid = vOut & "," & Round(dSum) & " BBL TOTAL FLUID"
   End If

  rst.Close
ErrHandler:
  Set rst = Nothing
  Call ErrorHandler(err, "GetSummaryFluid")
End Function

Open in new window

Author

Commented:
Hey Crystal,

I believe I finally got the code to work.  

I changed the dSum variable from a "Double" to a "Variant".  I didn't rename my strOut variable to vOut.

Here is my final code:
Function GetSummaryFluid(Optional bNoTotal As Boolean = False) As String
  On Error GoTo ErrHandler
  Dim rst As New ADODB.Recordset
  Dim strOut As Variant
  Dim dSum As Variant
    
  strOut = Null
  dSum = 0
    
rst.CursorLocation = adUseClient
  rst.CursorType = adOpenForwardOnly
  rst.LockType = adLockReadOnly
  
  rst.Open "FRAC_FLUID_SUM", CurrentProject.Connection
  
With rst
   Do While Not .EOF
      If Not IsNull(!FLUID_PUMPED_SUM) Then
        dSum = dSum + !FLUID_PUMPED_SUM
        strOut = strOut & Round(!FLUID_PUMPED_SUM) & " BBL "
          If Not IsNull(!FLUID_TYPE) Then
            strOut = strOut & "" & !FLUID_TYPE & ","
          End If
      End If
     .MoveNext
   Loop
   rst.Close
End With
  
  strOut = Nz(Left(strOut, Len(strOut) - 1), 0)   'ditch extra ,
  
   If IsNull(strOut) Then
      GetSummaryFluid = ""
   Else
      GetSummaryFluid = strOut & ", " & Round(dSum) & " TOTAL FLUID"
   End If

ErrHandler:
  Set rst = Nothing
  Call ErrorHandler(err, "GetSummaryFluid")
End Function

Open in new window


I'll have to wait until the user returns to this office to see if it's doing what they want.  Not sure if the user will be back in the office tomorrow or not.

Thanks,
gdunn59
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015

Commented:
great, thanks for sharing

you're welcome

instead of this:
Dim strOut As Variant
call the variable vOut since it is a variant, not a string (a variant can still hold a string; it can also be Null)

then find strOut and replace with vOut

and change:
          If Not IsNull(!FLUID_TYPE) Then
            strOut = strOut & "" & !FLUID_TYPE & ","
          End If
to:
vOut = (vOut +  "," ) & !FLUID_TYPE 

Open in new window

that way, the first time through, no comma and space will be added to the end if what is already there.

and delete this:
strOut = Nz(Left(strOut, Len(strOut) - 1), 0)   'ditch extra ,

the error handler can be improved  ... for instance, all object variables should be closed, if they were opened (or Quit) and released in the exit code. Here is a short video on error handling. There are 2  other videos in this series if you wish to know more.

basic error handling code for VBA (3:48)
https://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.