LEN function (1 Viewer)

[radek225]

I want to find out that, that fields [kolory p] and [kolory t] in my table are filled or not.
So I built the code:

Private Sub Naklad_skladki_AfterUpdate()
Dim rst As Recordset
Dim strSQL As String
Dim przod As Boolean
Dim tyl As Boolean

strSQL = "SELECT [tblGoraZleceniaNowa].[id_gora_zlecenia], [tblGoraZleceniaNowa].[kolory P], [tblGoraZleceniaNowa].[kolory T] FROM tblGoraZleceniaNowa where tblGoraZleceniaNowa.id_zlecenia=" & Me!ID_Zlecenia

Set rst = CurrentDb.OpenRecordset(strSQL)

Do While Not rst.EOF
    If Len(Nz(rst![Kolory P], 0)) <> 0 Then
        przod = True
    End If
    If Len(Nz(rst![Kolory T], 0)) <> 0 Then
        tyl = True
    End If
    
    rst.MoveNext
    Loop
rst.Close
   MsgBox przod
   MsgBox tyl
   
End Sub

Don't know why, but even if rst![kolory P] or rst![kolory t] are empty, "przod" and "tyl" are true

I've checked it, and ms access show me number 1 when fields are empty from expression Len(Nz(rst![Kolory P], 0)) or Len(Nz(rst![Kolory T], 0)). Why? There should be "0" and "false" in my code.

 

[pr2-eugin]

Just logically thinking Len(0) is 1 not 0, so even if it Null your condition will never fail. Try,

If Len(rst![Kolory T][COLOR=Red][B] & vbNullString)[/B][/COLOR] <> 0 Then

 

[CazB]

Sorry if this doesn't make sense, but:

if you are using Nz (expression, 0) then the length will never be 0, will it? If the 'expression' is blank, won't it put in 0 anyway, which means the length will be 1?

so

If Len(rst![Kolory P]) <> 0 Then
        przod = True
    End If

etc

 

[pr2-eugin]

if you are using Nz (expression, 0) then the length will never be 0, will it?

Totally agree ! However, this code,

If Len(rst![Kolory P]) <> 0 Then
        przod = True
    End If

Will throw an error. As Len function cannot deal with Null, so the OP's intention to use an Nz function. You could do this.

If Len([COLOR=Red][B]Nz([/B][/COLOR]rst![Kolory P][COLOR=Red][B], vbNullString)[/B][/COLOR]) <> 0 Then
    przod = True
End If

 

[CazB]

sorry, yes... forgot that! serves me right for answering whilst also being on the phone... thanks for pointing out my schoolgirl error, lol....

 

[smig]

If rst![kolony p] & "" = "" then....

This will work both for null and for empty string

Dont put spaces and special characters into names so you can omit the square brackets

 

[MarkK]

Also, you don't need "If". When the expression is True, the variable is True. When the expression is False, the variable is False. So just assign the expression directly to the variable . . .

przod = Nz(rst![Kolory P], "") <> ""

. . . but also, in the original code you posted, each loop overwrites the value of the variable in the previous loop . . .

Do While Not rst.EOF
[COLOR="Green"]    'get the values[/COLOR]
    przod = Nz(rst!KoloryP, "") <> ""
    tyl = Nz(rst!KoloryT, "") <> ""

[COLOR="Green"]    'show the values[/COLOR]
    msgbox przod & ", " & tyl

[COLOR="Green"]    'move next and loop, erases the previous values[/COLOR]
    rst.MoveNext
Loop

Your code only shows you the values for the last record.

 

[smig]

Also, you don't need "If". When the expression is True, the variable is True. When the expression is False, the variable is False. So just assign the expression directly to the variable . . .

przod = Nz(rst![Kolory P], "") <> ""

Markk,
Don't you think this one is better as it's not using a function ?

przod = rst![kolory p] & "" <> ""

 

[MarkK]

I think Nz() makes it more clear at a glance that we are expecting Nulls, and in this case our code is largely designed to convert nulls to booleans, so to me it contributes to clarity. But this kind of thing too, to me, is in the eye of the beholder.

Some people argue that keeping the If...End If construct . . .

If someExpression = True then 
    someVariable = True
End If

. . . is also more clear than . . .

someVariable = someExpression

. . . and that's a fair point too. These are somewhat religious arguments, or issues of style--not meaning, so I would not defend my own preference as anything other than a preference.

To me, the ugliest thing here . . .

przod = rst![kolory p] & "" <> ""

. . . is the space in the fieldname and the square brackets.

Inside a With block it could look like this . . .

przod = Nz(!koloryP, "") <> ""
przod = !koloryP & "" <> ""
[COLOR="Green"]'or even[/COLOR]
przod = Len(!koloryP & "")

. . . but, at a glance, which one checks for Null? Right, all of them.

 

[Galaxiom]

Your code only shows you the values for the last record.

Neglecting the issue with the Nz, the code doesn't show the last record but rather it will end with the variable as True if any of the records are Not Null because there is nothing to return them to False.

 

[smig]

I think Nz() makes it more clear at a glance that we are expecting Nulls, and in this case our code is largely designed to convert nulls to booleans, so to me it contributes to clarity. But this kind of thing too, to me, is in the eye of the beholder.

Some people argue that keeping the If...End If construct . . .

If someExpression = True then 
    someVariable = True
End If

. . . is also more clear than . . .

someVariable = someExpression

. . . and that's a fair point too. These are somewhat religious arguments, or issues of style--not meaning, so I would not defend my own preference as anything other than a preference.

To me, the ugliest thing here . . .

przod = rst![kolory p] & "" <> ""

. . . is the space in the fieldname and the square brackets.

Inside a With block it could look like this . . .

przod = Nz(!koloryP, "") <> ""
przod = !koloryP & "" <> ""
[COLOR="Green"]'or even[/COLOR]
przod = Len(!koloryP & "")

. . . but, at a glance, which one checks for Null? Right, all of them.

I also commented about the space in the name
Something should be avoided at all cases.

I rarely use Nz() as it will only catch Null strings and not an empty "" ones
I mostly consider spaces only fields as empty too so ill mostly use
Trim(MyField & "") = ""

 

[Galaxiom]

Some people argue that keeping the If...End If construct . . .

If someExpression = True then 
    someVariable = True
End If

. . . is also more clear than . . .

someVariable = someExpression

Except the two expressions are not equivalent.

The verbose version of the single line expression is:

If someExpression = True Then 
   someVariable = True
Else
   somevariable = False
End If

 

[MarkK]

Yes, this is more accurately what I was talking about, If...Else...End If . . .

If someExpression = True Then 
   someVariable = True
Else
   somevariable = False
End If

I prefer this in code . . .

someVariable = someExpression

. . . but I have heard people argue against the latter on the grounds that it is confusing, or not clear enough.

 

[Galaxiom]

I prefer this in code . . .

someVariable = someExpression

. . . but I have heard people argue against the latter on the grounds that it is confusing, or not clear enough.

I much prefer it too. There is no question of clarity to anyone who understands logical expressions.

Concise code is readable code.

Using an elaborate structure is suggestive that there is something more complex that is going to be coded and, as such, it is misleading.

 

[smig]

As someone never used the single line expression (Never knew it's possible before ) I do agree it's a bit confusing.
An equal sign in the someExpression part can give the impression that both first Vars will get the value of the 3rd. one.
I think I understands logical expressions, but I can't see the question part here .. Is it ? ...

 

[radek225]

Neglecting the issue with the Nz, the code doesn't show the last record but rather it will end with the variable as True if any of the records are Not Null because there is nothing to return them to False.

Right

THX Guys. I just want to say, that first version code of pr2-eugin working good. Thanks for insight!

 

[Galaxiom]

An equal sign in the someExpression part can give the impression that both first Vars will get the value of the 3rd. one.
.

You clearly don't understand how to read logical expressions.

 

[smig]

You clearly don't understand how to read logical expressions.

In math and for most people writing:
A = B = 3
Is the same as:
A = B and B = 3, result A and B = 3

for you it's only make sense it's like saying:
If B = 3 then A is True Else A is False


So maybe I don't understand how to read logical expressions, but I'm happy to be on the normal side of the globe

 

[Galaxiom]

In math and for most people writing:
A = B = 3
Is the same as:
A = B and B = 3, result A and B = 3

Only if you read it backwards.

 

[smig]

Only if you read it backwards.

You are in the computers business for too many years, so you forgot a basic math
I'm Hebrew speaker so I normally read backward