Problem when checking if memo field is empty

[tommaz]

Hello wise people,
maybe somebody can help me?
Here is the situation: I need to merge 2 table fields, one of which is "memo" data type and other is "text". The problem arises when I check if the memo data type field is empty. I tried checking if this field is equal to "Null" or "", but the function never find the field empty, even if it is. Does anybody have any suggestions? I would be very thankful, because the deadline is coming fast, and still there are many work to do

Here is my code:

Function sujungti()
    Dim kontrole As ADODB.Recordset
    Dim komentaras As ADODB.Recordset
    Dim naujas As ADODB.Recordset
    
    Set kontrole = New ADODB.Recordset
    Set komentaras = New ADODB.Recordset
    Set naujas = New ADODB.Recordset
     
    kontrole.Open "SELECT * FROM tblJungtine1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
    While Not kontrole.EOF
        If kontrole.Fields("Aprasymas") = "" Then   'Field Aprasymas is memo type
            kontrole!Bendra = kontrole.Fields("Kontroles")    'Field Kontroles is text type
        Else
            kontrole!Bendra = kontrole.Fields("Kontroles") & " (" & kontrole.Fields("Aprasymas") & ")"
        End If
        kontrole.Update
        kontrole.MoveNext
        apr = ""
    Wend
    
    kontrole.Close
    Set kontrole = Nothing
    
End Function

 

[ted.martin]

Have you tried using the If Len(MemoField.Value) > 0 then etc.

 

[tommaz]

Have you tried using the If Len(MemoField.Value) > 0 then etc.

Yes I have just tried:
if Len(kontrole.Fields("Aprasymas").Value) = 0 then
if Len(kontrole.Fields("Aprasymas").Value) = Null then
if kontrole.Fields("Aprasymas").Value) = Null then
if Len(kontrole!Aprasymas.Value) = Null then

And always function executes Else clause. And the strangest thing is that while debuging Len(kontrole.Fields("Aprasymas").Value) gets the value of Null, but it still goes to Else...

 

[tehNellie]

Off the top of my head
try

if isnull(Len(kontrole.Fields("Aprasymas").Value)) then

you might need

 = TRUE/FALSE

But I believe true is assumed by default.

a field/row/value etc can't equal NULL but isnull() can be true or false.

You should be able to drop the len() part as well and just test the value.

 

[Brianwarnock]

How about
If IsNull(kontrole.Fields("Aprasymas") Then



Brian

 

[tommaz]

Thank You tehNellie and Brian
problem solved

 

[tommaz]

and thanks for ted.martin

 

[Besom13]

Hi Tommaz,
I have a very simple database that has 3 fields;ID autonumeric;Word, Text; Meaning, Memo. I want to create a report via query that shows me the ID, and Word fields that have a Blank Memo field. How did you solve your problem 5 years ago! Please.
(My database is a list of words I have never heard of before I have read them in books. It is a list of 500 and getting bigger every day!)