hi Mark, good suggestion about using NZ. As a general rule, that's good, However, in this case, LEN and LEFT functions don't return numeric values if argument being tested is null.
I disagree about using the term "NULL values" (because one means NOT the other).
Jim, expressions to try depending on which result is desired in this specific case, since my response was cut:
===========================
1. show whatever is there if length of Paragraph2 < 554
MemoWithDate: iif( Len(tblMaxCompensatoryMemo.Paragraph2 & "") > 554, Left([tblMaxCompensatoryMemo].[Paragraph2] & "", Len([tblMaxCompensatoryMemo].[Paragraph2] & "")-554, tblMaxCompensatoryMemo.Paragraph2)
2. show nothing if length of Paragraph2 < 554
MemoWithDate: iif( Len( tblMaxCompensatoryMemo.Paragraph2 & "") > 554, Left([tblMaxCompensatoryMemo].[Paragraph2] & "", Len([tblMaxCompensatoryMemo].[Paragraph2] & "")-554, "")
===========================
while the data may be Null, "this page left intentionally blank" is Not
if a field doesn't have a value, it is Null meaning NO VALUE. Some variable types can have other descriptors like Empty.
NZ doesn't work if displaying a field where there isn't a record, so sometimes I use the 'old' & "" trick -- it always works. However, I do use NZ a lot! IIF evaluates both result expressions, not just the one that gets used, and if LEN and LEFT are in there, they need to be fed strings not nulls to avoid errors. That's when I might use & ""
As far as knowing if data is missing, having nothing there is a good clue -- and easier to see than a message, imo
kind regards,
crystal
On 10/10/2024 3:21 PM, Mark Burns via groups.io wrote:
Crystal,
That's a great point about handling NULL values, but I interpret the words "Null value" a little differently than you may.
All:
(for general discussion purposes) What NULL means in your database:
It COULD mean literally anything. It specifically means Nothing.
What I mean by that is this: have you ever read a book or document and come across a blank page with the words "this page left intentionally blank" on it? That is ONE of the possible semantic meanings for a NULL in your database data. It also CAN mean: "no entry yet exists for this value", "the value here was deleted", "this value is undefined or uninitialized", amongst other semantic meanings that can be applied to the presence of a NULL value in your data.
It's only truly inarguable MEANING of a NULL value is: "there is no data here"
Now as to how we developers THINK about and prepare to handle NULLs (especially unexpected NULLs), well, there are several schools of thought.
One is that it is simple to append an empty string everywhere that unexpected NULLs can be encountered (which is what Crystal did) and thereby eliminate any errors arising from unexpected NULL values in your data, preserving the effectiveness of your query expressions (and related code).
Another is that while that IS simple, it can also obfuscatory.
What if it was crucial to know when a null value unexpectedly arose? Using the ' & "" ' method can effectively obfuscate those cases from being discovered or handled appropriately (to fix the missing data, say).
For that reason, I'm generally more in favor of explicitly handling nulls by using NZ() functions within any expression where NULL is a tripping hazard of significance. For example, using an expression like NZ([tblMaxCompensatoryMemo].[Paragraph2], "*** Missing Paragraph 2 data! ***") would achieve the same expression-safety result, but it would add a textual alert to point out the missing data.
Is this always necessary? No. Is it perhaps the better coding habit to use than ' & "" '? I will leave that for you to decide.
Jim, Yup, that'd do it...the solution, if you want to keep doing that 554 character trimming, would be to wrap that expression in an iif() function.
(Warning: "air code" follows)
MemoWithDate: iif(Len([tblMaxCompensatoryMemo].[Paragraph2]) > 554, Left([tblMaxCompensatoryMemo].[Paragraph2],Len([tblMaxCompensatoryMemo].[Paragraph2])-554), [tblMaxCompensatoryMemo].[Paragraph2])
Duane,
242 characters with spaces
Jim Wagner
Thank You Duane. I will look
Jim Wagner
I would first look at the number of characters in Paragraph2 to make sure it's more than 554 characters.
Duane
Hello All,
Yesterday I started to get an error message for "Invalid Procedure call" when I tried to open a report. This report has been running for years with no issues. This morning after several hours, I deleted from the query this line
MemoWithDate: Left([tblMaxCompensatoryMemo].[Paragraph2],Len([tblMaxCompensatoryMemo].[Paragraph2])-554)
and the query ran successfully with no issues. So, I am assuming that either an office update or something is haywire with the database has recently occurred. I have checked vba references and there are no missing references.
I am not sure where to go from here. Can someone help me find a solution. My user is to the point of extreme frustration with me.
Thank You
Jim Wagner
