UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Len() Function Returns No Value, Access 2016    
 
   
dg_moore
post Jul 11 2019, 01:52 PM
Post#1



Posts: 322
Joined: 22-April 11



Point of curiosity: I have a text field in a table used to store email addresses. A Len() function on that field returns 3 kinds of values: an integer value for a valid email address, 0 for a null field or empty string, and no value at all (returns nothing) for some other blank fields. What does the latter mean? Seems to me that a field is either empty or not, but Len() returns nothing at all for some apparently empty fields. Does anybody know what this is about?
Go to the top of the page
 
theDBguy
post Jul 11 2019, 01:57 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,713
Joined: 19-June 07
From: SunnySandyEggo


Hi. I just did a quick test. If I do the following: Len(Null), the result is Null. So, if your field is blank (Null), then the Len() function returns null or nothing.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dg_moore
post Jul 11 2019, 02:43 PM
Post#3



Posts: 322
Joined: 22-April 11



Ok - so if the field is null, Len( ) returns null. So what does it mean when Len() returns 0? A zero-length string? But the default field value is null, so where would a zero- length string come from in a new record? It's still a little puzzling, but not crucial in the large scheme of things.
Go to the top of the page
 
theDBguy
post Jul 11 2019, 02:47 PM
Post#4


Access Wiki and Forums Moderator
Posts: 75,713
Joined: 19-June 07
From: SunnySandyEggo


Hi. Yes, a result of zero (0) comes from a zero-length string. If you look at the properties of the field in the table's design view, does it allow zero-length strings? You can create a simple query like this to check your results:
CODE
SELECT IsNull(FieldName) As FieldIsNull, ""=FieldName As FieldIsZLS, Len(FieldName) As ResultOfLen FROM TableName

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
WildBird
post Jul 11 2019, 06:57 PM
Post#5


UtterAccess VIP
Posts: 3,576
Joined: 19-August 03
From: Auckland, Little Australia


I often use a empty string ("") with formulas to account for possible NULLS e.g. len("" & [FieldName]). Gives more consistent results.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
gemmathehusky
post Jul 15 2019, 06:45 AM
Post#6


UtterAccess VIP
Posts: 4,722
Joined: 5-June 07
From: UK


in some cases, there is no variable to work on.
It's not even null

Try a form based on a query returning no records, and with allow additions set to false.

I am not sure what the definition of controls on such a form would be, but I tend to refer to them as "undefined".


--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th July 2019 - 09:37 PM