How do I track down what's causing this "#Error"?

[cherold]

I have a query that calls a Function. It works on most records but on a couple instead of what I expect I get the text "#Error". I have zero idea how to track down mysterious errors, and am hoping someone can tell me how to debug this.

My query is:

SELECT ConstructName(FirstName, LastName, BandName) AS FullName FROM Artists

ConstructName just puts the name together with proper punctuation (like "Bach; J.S.").



Of over 600 names, 7 or 8 return #Error instead of the constructed name. I thought perhaps there were bad invisible characters from an import so I deleted and recreated one of the problem records and that didn't help.

I did put a watch on one of the bad names and the name doesn't appear to ever enter the function at all. That is, if I create a "break on true" watch on last_name = "Bach" it gets triggered, but my "break on true" for one of the problem names, "Prokofiev", is never triggered.

So how does one approach an issue like this?

[orange]

Can you post a copy of your database? zip format

Can you post a copy of the function?

Do you have Nulls in your data, and if so, do you handle NULL values?

[ArviLaanemets]

Open your function in VBA editor;

Set a marker (click on left border of VBA module window - a dot appears there) for 1st code row which is not declaring variables. Close VBA editor;

Run the operation which calls the function for some name you have a problem with. Tha VBA editor is opened with running code stopped at marked row;

Press F8 repeatedly to process the function code row-wise and find the code row where your problem happens.

[Bulzie]

Sounds like bad data in one of those fields or special characters?. Look at some that are failing and what is different then ones that work? Can you post the actual record (FirstName, LastName, BandName) that are causing the issue?

[cherold]

Yeah, I did that, but the problem name never showed up in the function at all! It's as though the function never gets called for certain names.

[cherold]

Actually, I think this is a null issue. As an experiment, I added text to the empty BandName field in the Prokofiev record and it worked. The strange thing is, 90% of the records have an empty BandName field and most work fine. So I guess that means that I have a handful of records that have a null in BandName and the rest are empty strings?

Is there a way to allow me to call ConstructName without error even if I'm passing a null value? Or do I need to do something like an UPDATE query that looks for null values and changes them to an empty string or something?

This is the function:

Public Function ConstructName(fFirst As String, lLast As String, bBand As String) As String

    ' make sure there are no spaces at the ends
    fFirst = Trim(fFirst)
    lLast = Trim(lLast)
    bBand = Trim(bBand)
    
    
    If (Len(fFirst & "") > 0) Then
        fFirst = "; " & fFirst
    End If
    
    If (Len(bBand & "") > 0) Then
        bBand = " - " & bBand
    End If
    
    ConstructName = lLast & fFirst & bBand


End Function

[cherold]

To answer my own question, it seems if I declare the arguments as Variants the null issue goes away. Thanks all.

[ArviLaanemets]

To answer my own question, it seems if I declare the arguments as Variants the null issue goes away. Thanks all.

When this was the reason, using Nz() when calling the function would help too
=ConstructName([FirstName], [LastName], Nz([Band],"")