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:
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
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_SU M"),0)) & " BBL " & rst.Fields("FLUID_TYPE") & ","
dSum = dSum + Nz(rst.Fields("FLUID_PUMPE D_SUM"), 0)
strOut = strOut & Round(Nz(rst.Fields("FLUID
dSum = dSum + Nz(rst.Fields("FLUID_PUMPE
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
Thanks for the speedy assistance.
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
The error I was getting was as follows:
Error: 5
GetSummaryFluid
Invalid procedure call or argument
Thanks,
gdunn59
ASKER
So how could I have it check both?
Thanks,
gdunn59
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
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.
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
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
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:
Thanks,
gdunn59
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
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.
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.
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
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
What line is raising the error?
ASKER
PatHartman,
Line 20 of the code is where the Run-time error '5': Invalid Procedure call or argument, is happening.
Thanks,
gdunn59
Line 20 of the code is where the Run-time error '5': Invalid Procedure call or argument, is happening.
Thanks,
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_SU M"), 0)) & " BBL " & rst.Fields("FLUID_TYPE") & ","
dSum = dSum + Nz(rst.Fields("FLUID_PUMPE D_SUM"), 0)
Thanks,
gdunn59
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
dSum = dSum + Nz(rst.Fields("FLUID_PUMPE
Thanks,
gdunn59
Does strOut have a value?
From any code module, open Tools/References. Is any reference marked as missing?
From any code module, open Tools/References. Is any reference marked as missing?
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
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
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:
Otherwise, the strOut concatenation needs to be this (excluding the Fluid_Type):
Thanks,
gdunn59
strOut = strOut & Round(rst.Fields("FLUID_PUMPED_SUM")) & " BBL " & rst.Fields("FLUID_TYPE") & ","
dSum = dSum + rst.Fields("FLUID_PUMPED_SUM")
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")
Thanks,
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
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)
ASKER
I tried that also, still get the run-time error 5.
Thanks,
gdunn59
Thanks,
gdunn59
Please post the code as it is now.
ASKER
ok. I'm in a meeting right now. I'll do it in about 30 minutes or so.
Thanks,
gdunn59
Thanks,
gdunn59
ASKER
PatHartman:
Here is the full Code:
Thanks,
gdunn59
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
Thanks,
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
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.
ASKER
PatHartman,
Because of proprietary reasons, I can't post the entire database.
Any other suggestions?
Thanks,
gdunn59
Because of proprietary reasons, I can't post the entire database.
Any other suggestions?
Thanks,
gdunn59
ASKER
PatHartman,
That particular Function isn't calling any other Functions.
Thanks,
gdunn59
That particular Function isn't calling any other Functions.
Thanks,
gdunn59
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
crystal,
That didn't work 100%. It isn't concatenating all the text like it was before.
Thanks,
gdunn59
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:
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
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),
vOut = (vOut + ", ") & nz(!FLUID_PUMPED_SUM,0) & " bbl"
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
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
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
ASKER
Crystal,
I think we were pushing Submit at the same time. I'll try your last suggestion.
Thanks,
gdunn59
I think we were pushing Submit at the same time. I'll try your last suggestion.
Thanks,
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
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:
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.#")
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
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
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:
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
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:
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
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
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:
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
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
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
Open in new window