Solved Multiple If Statements Problem

[silentwolf]

Hi guys thanks again!

Yes it works as arnelgp has provided and yes your rigth Minty I need to put comments into it just need to understand it first lol )
Very complex for me his function but it works like a charm

@Josef P.
thanks also I will try yours as well later on to see if I can make it work where it fails!

Cheers for all your help very much appreciated!

 

[Isaac]

Might want to make sure you're not limiting yourself to a specific Case, if the passed in value can come in as multiple possible cases.
(Instr)

 

[silentwolf]

Yes I guess so but it is still a bit much for me to handle all propabillities.

For one statement over a year it is working as intended need to look for other years and check there will be for sure more to do or do update different functions.

That is why RegEx comes handy but I am still very very week on that subject unfortunatelly.

 

[Isaac]

I'm not sure how robust Access's LIKE is, but if it's as robust as SQL Server's LIKE, you may not need regex.
But I see what you mean.
I just wanted to point out that , in most circumstances, Instr() is case-sensitive unless you somehow accomodate for it, is all

 

[silentwolf]

I understand what you mean.. well I hope it is robust enough for what I am doing.

But many thanks for pointing out those possible issues!!

 

[Josef P.]

Note:
Function InStr([Start], [String1], [String2], [Compare As VbCompareMethod = vbBinaryCompare])
=> Compare = vbTextCompare => case insensitiv

And for the extra confusion: If the parameter is not set, the setting of the module is used, although vbBinaryCompare should be set as the default.

Option Compare Text
' Option Compare Binary
' Option Compare Database

Private Sub Test()
    Debug.Print InStr(1, "abc", "B") ' => Option Compare **** is used!
    Debug.Print InStr(1, "abc", "B", vbBinaryCompare)
    Debug.Print InStr(1, "abc", "B", vbTextCompare)
End Sub

 

[silentwolf]

Hi Josef,

And for the extra confusion: If the parameter is not set, the setting of the module is used, although vbBinaryCompare should be set as the default.

you making me looking bad very bad lol
I have no clue what you are saying at this point oh my gosh

 

[Josef P.]

According to the definition (Object Browser), Instr has the default value vbCompareBinary for the Compare parameter.
=> try this code:
Debug.Print InStr(1, "abc", "B", vbBinaryCompare)
Expected: 0 because b <> B with vbBinaryCompare

Now try this code in a standard module with Option Compare Text:
Debug.Print InStr(1, "abc", "B")
Expected(?): 0 because the default parameter value of Compare is vbBinaryCompare
But: Instr(1, "abc", "B") will return 2. .. => b = B

 

[silentwolf]

Oh I see says the blind man )

I will do that and let you know how I went with it!

Cheers for making it more clear to me )

 

[The_Doc_Man]

I want to show you a trick that will help you with nested IF/THEN sequences. Watch for my annotations!

Public Function ReturnUMS01(strText As String) As String

    If strText Like "IBAN*" Then                                         <<<<  Start Block level 1
        If strText Like "*Zahlungsreferenz*" Then                <<<<  Start Block level 2
            mStartPos = Ibanlength(strText) + 2
            mEndPos = InStr(strText, "Zahlungsreferenz:") - 1
            mIntLength = mEndPos - mStartPos
    
            ReturnUMS01 = Mid(strText, mStartPos, mIntLength)
            
        If strText Like "*Auftraggeberreferenz:*" Then           <<<< Start Block level 3
            mStartPos = Ibanlength(strText) + 2
            mEndPos = InStr(strText, "Auftraggeberreferenz:") - 1
            mIntLength = mEndPos - mStartPos
    
            ReturnUMS01 = Mid(strText, mStartPos, mIntLength)
        End If                                                                          <<<< End block level 3 (now back in block level 2)
        
        If strText Like "*IBAN*Auftraggeberreferenz*" Then    <<<< Start ANOTHER block level 3
            mStartPos = Ibanlength(strText) + 2
            mEndPos = InStr(strText, "Auftraggeberreferenz:") - 1
            mIntLength = mEndPos - mStartPos
    
            ReturnUMS01 = Mid(strText, mStartPos, mIntLength)       
        
        End If                                                                            <<<< End block level 3 (now back in block level 2)

End Function

At that END FUNCTION statement, you have two pending blocks. Your logic does not take flow of the blocks into account.

 

[silentwolf]

According to the definition (Object Browser), Instr has the default value vbCompareBinary for the Compare parameter.
=> try this code:
Debug.Print InStr(1, "abc", "B", vbBinaryCompare)
Expected: 0 because b <> B with vbBinaryCompare

Now try this code in a standard module with Option Compare Text:
Debug.Print InStr(1, "abc", "B")
Expected(?): 0 because the default parameter value of Compare is vbBinaryCompare
But: Instr(1, "abc", "B") will return 2. .. => b = B

I tried with option compare Text.. getting an Error sayin it is used serveral times ??

Does this statement not come on top of the Module like

Option Compare Database
Option Explicit
Option Compbare Text

I have nowhere a option compare Text in my database so not sure what this error means and why I am getting it

 

[cheekybuddha]

I tried with option compare Text.. getting an Error sayin it is used serveral times ??

Does this statement not come on top of the Module like

Option Compare Database
Option Explicit
Option Compbare Text

I have nowhere a option compare Text in my database so not sure what this error means and why I am getting it

You are confusing things - Josef is not talking about Option Compare ....

Restore the top of all your code modules to:

Option Compare Database
Option Explicit

(You can only have one Option Compare statement per module - you can choose Database/Text/Binary - just stick with Database)

Josef was talking about the Compare argument/parameter of the InStr() function. Pass in the constant vbBinaryCompare and it will use case-sensitive matching to determine whether the needle string is found within the haystack string.

 

[silentwolf]

hmm ok..

What I did try was in one new module using Compare Text and it was working as Josef discribed..

But still very confused about this lol (

 

[cheekybuddha]

See the reference here.

Generally you should never need to mess with the Option Compare statements

 

[Josef P.]

Generally you should never need to mess with the Option Compare statements

But you should definitely know what they do.
If, for example, it is not set, binary automatically applies and then the expression If "a" = "A" or If "abc" like "*A*" will end up in the False block.

And as already described above, this setting has an effect on the compare argument of string functions (instr, replace) that is not explicitly set.

 

[silentwolf]

Ok thanks I will need to look into it a bit more )

 

[Isaac]

Sorry to open up a can of worms with Instr and case sensitivity, but as you can see @silentwolf it's worth understanding because testing for Instr is just one of the 'gotchas' on this subject

 

[silentwolf]

Hi Isaac,

all good just did not know and it is nice that you are pointing this issues out!