If...then...elseif... What am I doing wrong?

[bulbisi]

Hello,
I'm trying to extract information from a query and create a XML file. No problem with that. My script is working perfectly (and thanks to helpers here on this forum).

There is a bunch of fields that I don't want to show as there are empty of value. Fields are (0) or (1) or (2), (0) being the last solution if (1) and (2) are empty.
Basically here is my logic:

if (1) is empty then
if (2) is empty then
line to display value (0)
else
line to display value (2)
else
line to display value (1)
end if

(or = if...then...elseif...then...else...end if)

Here is my code:

              If rs.Fields(1).Value = "Null" Then 'check if RRN is empty
                Print #1, "<"; rs.Fields(2).Name; ">"; rs.Fields(2).Value; "</"; rs.Fields(2).Name; ">"
                ElseIf rs.Fields(2).Value = "Null" Then 'check if KBO is empty
                Print #1, "<"; rs.Fields(0).Name; ">"; rs.Fields(0).Value; "</"; rs.Fields(0).Name; ">"
                Else
                Print #1, "<"; rs.Fields(1).Name; ">"; rs.Fields(1).Value; "</"; rs.Fields(1).Name; ">"
                End If

Problem: it is sending to my XML only the lines Fields(2) (called RRN). Even if there are empty.

tip to understand: as you may know when a field value is empty/null, XML protocol convert the value as a "Null" value instead of skiping it.
Like this: "<DEBTOR_RRN_NUMBER>Null</DEBTOR_RRN_NUMBER>"

help help help :/
Many thanks in advance

Chris

 

[vbaInet]

You use the IsNull() function for checking for Null not = "Null".

 

[bulbisi]

something like this?

If isnull(rs.Fields(1).Value) then

 

[CBrighton]

something like this?

If isnull(rs.Fields(1).Value) then

Bingo.
10 character limit...

 

[bulbisi]

thanks for the tip
although now I have neither KBO nor RRN in the first records, and both of them (where one of them is Null) a bit further.
Will dig a bit more but any comment is very appreciated

 

[vbaInet]

See how you get on and let us know if you need further assistance.

I will just point out one thing.

If rs.Fields(1).Value = "Null" Then 'check if RRN is empty
    Print #1, "<"; rs.Fields(2).Name; ">"; [COLOR=red]rs.Fields(2).Value[/COLOR]; "</"; rs.Fields(2).Name; ">"
ElseIf [COLOR=red]rs.Fields(2).Value[/COLOR] = "Null" Then 'check if KBO is empty

You're printing KBO before checking whether it's Null. Printing KBO based on the premise that RRN is Null is illogical.

 

[bulbisi]

oh yeah, I noticed. that's why I changed a bit the script since then
Now it looks like this:

    Do While Not rs.EOF
              If IsNull(rs.Fields(1).Value) Then 'check if RRN is empty
                If IsNull(rs.Fields(2).Value) Then 'check if KBO is empty
    Writeout = Writeout & "<" & rs.Fields(0).Name & ">" & rs.Fields(0).Value & "</" & rs.Fields(0).Name & ">" & vbCrLf
                Else
    Writeout = Writeout & "<" & rs.Fields(2).Name & ">" & rs.Fields(2).Value & "</" & rs.Fields(2).Name & ">" & vbCrLf
                End If
    Writeout = Writeout & "<" & rs.Fields(1).Name & ">" & rs.Fields(1).Value & "</" & rs.Fields(1).Name & ">" & vbCrLf
              End If

but it still not working correctly :/

I'm now working on something else. Usually I find the solution when coming up later on it. This time unfortunately not.

Note that if I'm using the writeout string then printing ( print #i ) afterwards, all Null values in other fields provoke a weird behaviour. I will have to replace them. (loosing my hair here)

 

[bulbisi]

or the Print # version:

              If IsNull(rs.Fields(1).Value) Then 'check if RRN is empty
                If IsNull(rs.Fields(2).Value) Then 'check if KBO is empty
                    Print #1, "<"; rs.Fields(0).Name; ">"; rs.Fields(0).Value; "</"; rs.Fields(0).Name; ">"
                Else
                Print #1, "<"; rs.Fields(2).Name; ">"; rs.Fields(2).Value; "</"; rs.Fields(2).Name; ">"
                End If
              Print #1, "<"; rs.Fields(1).Name; ">"; rs.Fields(1).Value; "</"; rs.Fields(1).Name; ">"
              End If

same results, except that any other Null value is printed correctly (with "null " though). I wish the Null is translated as a zero value ( "" ). But I guess I can find that by myself.

 

[vbaInet]

Chris,

You will find that this is still illogical:

if (1) is empty then
if (2) is empty then
line to display value (0)

What guarantees that if 1 and 2 are Null, 0 will not be Null?

 

[bulbisi]

good question
so here is the answer:
(0) is the internal number = a key given by our database. And all our customers got one.
(1) is RRN = Population Register Number (mandatory in Belgium)
(2) is KBO = basically the same but for Companies (so = Company number)
but sometimes we have not one of them (1 or 2) so we use the internal number (0)

Note: no one is really existing, I'm working on a dummy database. With voluntary mistakes (blank field, bad format, ...).

So the logic I used sounds good. Well, I hope!

 

[vbaInet]

Would something like this work?

If 1 and 2 are Null Then
    Print 0
Else
    If 1 is Not Null then
        Print 1
    End if
 
    If 2 is Not Null
        Print 2
    End If
End If

 

[bulbisi]

For the first condition I tried If Isnull(rs.Fields(1).Value And rs.Fields(2).Value) and got an Overflow error, so changed it to the one herebelow.
For the second and third condition when attempting If rs.fields(1).value is not null but got an Object Required error then i'm changing it to If not rs.fields(1).value (VBA converted it automatically like this) and it blocks in an Overflow error. So finally I tried the If rs.fields(1).value <> Null and I get a result.

If IsNull(rs.Fields(1).Value) And IsNull(rs.Fields(2).Value) Then 'check if both RRN and KBO is empty
     Debug.Print "<" & rs.Fields(0).Name & ">" & rs.Fields(0).Value & "</" & rs.Fields(0).Name & ">"
Else
If rs.Fields(1).Value <> Null Then      'check if RRN is used
     Debug.Print "<" & rs.Fields(1).Name & ">" & rs.Fields(1).Value & "</" & rs.Fields(1).Name & ">"
End If
If rs.Fields(2).Value <> Null Then      'check if KBO is used
     Debug.Print "<" & rs.Fields(2).Name & ">" & rs.Fields(2).Value & "</" & rs.Fields(2).Name & ">"
End If
End If

Although the result is better, it is still not as expected. I get :
<DEBTOR_KBO_NUMBER>0878019551</DEBTOR_KBO_NUMBER>
<DEBTOR_KBO_NUMBER>0898896030</DEBTOR_KBO_NUMBER>
<DEBTOR_KBO_NUMBER>0714596426</DEBTOR_KBO_NUMBER>
and only this. I should have 6 lines with RRN before. I checked my table, and the Debug line per line and I get results when I'm moving the mouse over the variable.

I keep on, ...

Sorry this is only 2 months I started with vba, I'm still amuzed by these functions and sometimes remain stunned.

 

[bulbisi]

oops. No. It printed nothing. What I saw was a previous version.

 

[bulbisi]

found!
Result=
<DEBTOR_RRN_NUMBER>46000079376</DEBTOR_RRN_NUMBER>
<DEBTOR_RRN_NUMBER>56010141856</DEBTOR_RRN_NUMBER>
<DEBTOR_RRN_NUMBER>57060844965</DEBTOR_RRN_NUMBER>
<DEBTOR_RRN_NUMBER>72072455196</DEBTOR_RRN_NUMBER>
<DEBTOR_RRN_NUMBER>45041433722</DEBTOR_RRN_NUMBER>
<DEBTOR_RRN_NUMBER>63092817679</DEBTOR_RRN_NUMBER>
<DEBTOR_RRN_NUMBER>74101710737</DEBTOR_RRN_NUMBER>
<DEBTOR_RRN_NUMBER>54020632759</DEBTOR_RRN_NUMBER>
<DEBTOR_RRN_NUMBER>77091517288</DEBTOR_RRN_NUMBER>
<DEBTOR_RRN_NUMBER>63050205381</DEBTOR_RRN_NUMBER>
<DEBTOR_KBO_NUMBER>0878019551</DEBTOR_KBO_NUMBER>
<DEBTOR_KBO_NUMBER>0898896030</DEBTOR_KBO_NUMBER>
<DEBTOR_KBO_NUMBER>0714596426</DEBTOR_KBO_NUMBER>

Code used=

If Not IsNull(rs.Fields(1).Value) Then      'check if RRN is used
                    Debug.Print "<" & rs.Fields(1).Name & ">" & rs.Fields(1).Value & "</" & rs.Fields(1).Name & ">"
ElseIf Not IsNull(rs.Fields(2).Value) Then      'check if KBO is used
                    Debug.Print "<" & rs.Fields(2).Name & ">" & rs.Fields(2).Value & "</" & rs.Fields(2).Name & ">"
Else
                    Debug.Print "<" & rs.Fields(0).Name & ">" & rs.Fields(0).Value & "</" & rs.Fields(0).Name & ">"
End If

or simplified for visitors=

If Not IsNull(rs.Fields(1).Value) Then      'check if RRN is used
                    Debug.Print rs.Fields(1).Value
ElseIf Not IsNull(rs.Fields(2).Value) Then      'check if KBO is used
                    Debug.Print rs.Fields(2).Value
Else
                    Debug.Print rs.Fields(0).Value
End If

Again thank you mate for your guidance!

 

[vbaInet]

Glad to see you finally came to a solution.