Link to home
Start Free TrialLog in
Avatar of gdunn59
gdunn59

asked on

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

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

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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

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)
Avatar of gdunn59
gdunn59

ASKER

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

Thanks for the speedy assistance.

gdunn59
Avatar of gdunn59

ASKER

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
Avatar of gdunn59

ASKER

So how could I have it check both?

Thanks,
gdunn59
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

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.
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
Avatar of gdunn59

ASKER

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
"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.
Avatar of gdunn59

ASKER

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

What line is raising the error?
Avatar of gdunn59

ASKER

PatHartman,

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

Thanks,
gdunn59
Avatar of gdunn59

ASKER

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
Does strOut have a value?

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

ASKER

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

Avatar of gdunn59

ASKER

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
Avatar of gdunn59

ASKER

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
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)
Avatar of gdunn59

ASKER

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

Thanks,
gdunn59
Please post the code as it is now.
Avatar of gdunn59

ASKER

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

Thanks,
gdunn59
Avatar of gdunn59

ASKER

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
Avatar of gdunn59

ASKER

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
I really can't debug code that I can't see.  Perhaps you can post the database.
Avatar of gdunn59

ASKER

PatHartman,

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

Any other suggestions?

Thanks,
gdunn59
Avatar of gdunn59

ASKER

PatHartman,

That particular Function isn't calling any other Functions.

Thanks,
gdunn59
ASKER CERTIFIED SOLUTION
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gdunn59

ASKER

crystal,

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

Thanks,
gdunn59
"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
Avatar of gdunn59

ASKER

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
Avatar of gdunn59

ASKER

Crystal,

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

Thanks,
gdunn59
Avatar of gdunn59

ASKER

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

Avatar of gdunn59

ASKER

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
you're welcome ~ enjoy your time away
Avatar of gdunn59

ASKER

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

Avatar of gdunn59

ASKER

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