Public Function Help

[Holly]

Hi experts,

I'm new to VBA so please go easy. I used to post on the site as Holly_Associated but have had to change my user due to a change of job and so cannot upload an example of what I am doing. I shall try to explain below, without writing War & Peace!

I am using a public function to feed a variable string to a query. So far I have got:

Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String
     Dim cslStr1 As String, newStreet As String
              
   [COLOR=red]newStreet = Right(streetVar, Len(streetVar) - Len(newFL))[/COLOR]
              
    cslStr1 = IIf(firstLVar = "Small Client A-M" Or firstLVar = "Small Client N-Z", newStreet, streetVar)
        
    ClientStreetModule = cslStr1
    
End Function

This is something I have used from someone else and so I 'kind of' understand what I'm doing. The part that is highlighting as needing debugging is the red text.

The reason is that the instance of streetVar is not always a greater length than newFL. (I think!)

However, I only need to use newStreet as the true part of iif, in which instance all are longer. At least I think this is the problem. I realise I might need to use NZ but am not sure how. And can anyone explain why it is evaluating and giving errors for all records and not just when the iif criteria is true as I want it to?

Any help or pointers would be much appreciated, and I apologise if this is glaringly obvious!

If you need a better explanation please let me know. I usually include a little playDB but can't upload at the moment!

Thank you, Holly

 

[pr2-eugin]

Holly, can you show some sample data, of what is causing the trouble? The highlighted code will definitely throw out an error, if the difference of two length is a Negative number. Using Nz is easy, just use..

cslStr1 = [COLOR=Red][B]Nz([/B][/COLOR]IIf(firs.........etVar)[COLOR=Red][B], vbNullString)[/B][/COLOR]

 

[Holly]

Hi Paul,

I was hoping you'd be around!

I think I've attached a spreadsheet with some test data. The red line is the trouble, the other eventualities are OK. If it's unclear let me know! It's stumping me as I don't even want that line to change, it doesn't start with "Small Client...".

Thank you,

Holly

 

[pr2-eugin]

Just wondering, if this would not work for you?

ClientStreet: Trim(Mid(qP2.Street, InStr(qP2.Street, ",") + 1))

For the Test you gave,

? Trim(Mid("SC Construction, 1 Other Street", InStr("SC Construction, 1 Other Street", ",")+1))
1 Other Street

? Trim(Mid("SC Build, 2 Other Street", InStr("SC Build, 2 Other Street", ",")+1))
2 Other Street

? Trim(Mid("13 Short Road", InStr("13 Short Road", ",")+1))
13 Short Road

? Trim(Mid("24 VeryVeryVeryLong Road", InStr("24 VeryVeryVeryLong Road", ",")+1))
24 VeryVeryVeryLong Road

 

[Holly]

Hi Paul,

Sorry, I was concentrating so much on the error I have not given you all eventualities. Please see attached new test data with two extra lines added. Some of these include a comma but I still need all of the information, which is why I used iif on the "Small Client..." records. Again, if this doesn't make sense let me know.

I didn't imagine when I set out to do these address labels, pulling information from three different databases, that it would be so involved!

Thank you for your time,

Holly

Edit: With the actual data attached!

 

[pr2-eugin]

Sorry to show tricks, but that is what my mind is resorting to, how about?

ClientStreet: Trim(Replace(qP2.Street, qP2.MNFL & ",", ""))

Again for the Test,

? Trim(Replace("SC Construction, 1 Other Street", "SC Construction" & ",", ""))
1 Other Street

? Trim(Replace("SC Build, 2 Other Street", "SC Build" & ",", ""))
2 Other Street

? Trim(Replace("13 Short Road", "Construction Company 27 Ltd" & ",", ""))
13 Short Road

? Trim(Replace("24 VeryVeryVeryLong Road", "Build Co 15 Ltd" & ",", ""))
24 VeryVeryVeryLong Road

? Trim(Replace("Unit A, Small Ind. Est., Another Rd", "Plan Ltd" & ",", ""))
Unit A, Small Ind. Est., Another Rd

? Trim(Replace("Unit B, Big Industrial Estate", "Assembly plc" & ",", ""))
Unit B, Big Industrial Estate

 

[Holly]

Hi Paul,

Yes, it works of course! Flashy eh?

Thank you so much for helping again. That was a much easier solution!

Can you just explain, if you know off the top of your head why the public function method was giving a value of newStreet when it didn't need to be given a value until the iif truepart?

Thanks,
Holly

 

[pr2-eugin]

The problem was not IIF executing wrong, it was because the Right function got a wrong argument (negative value), so the function errored, thus your Query was acting bizzerk, if you re arranged your code a bit like,

Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String
    If firstLVar = "Small Client A-M" Or firstLVar = "Small Client N-Z" Then
        ClientStreetModule = Right(Nz(streetVar), Len(Nz(streetVar)) - Len(Nz(newFL)))
    Else
        ClientStreetModule = Nz(streetVar)
    End If    
End Function