Query w/Custom Function Works Sometimes 2

[Sandman83]

I have a query with a custom function that works fine as long as I have not opened up the VBA editor. If that is/has been opened the query loops indefinitely calling the function with new data over and over looping through all the records and starting over again.

The function is a simple string manipulation. It turns ###-###-#### to ##########.

Anyone know of any bugs or something that causes this to happen?

Details:
Query

DELETE tblTFN.TFN, *
FROM tblTFN
WHERE tblTFN.TFN Not In (SELECT fStripTFN(tblTempTFN.TFN) FROM tblTempTFN);

Function

Public Function fStripTFN(strTFN As String) As String
On Error GoTo Err_fStrip

    If Len(strTFN) = 12 Then
        fStripTFN = Left$(strTFN, 3) & Mid$(strTFN, 5, 3) & Right$(strTFN, 4)   'Formats ###-###-#### to ##########
    Else
        fStripTFN = Mid(strTFN, 2, 3) & Mid(strTFN, 7, 3) & Mid(strTFN, 11, 4)  'Formats (###) ###-#### to ##########
    End If
        
Exit_fStrip:
    Exit Function
    
Err_fStrip:
    MsgBox Err.Description, vbCritical, Err.Number
    Resume Exit_fStrip
End Function

 

[dhookom]

I believe if you have 100 records in tblTFN and 10 records in tblTempTFN then function will be called at least 100*10 times.

Duane
Hook'D on Access
MS Access MVP

 

[Sandman83]

Thx for the reply. That makes sense for why it takes so long while the VBA editor is open. (Your formula would make it about 800k iterations of the function.) I'm still not sure why it would go sooooo much faster if the VBA editor has not been opened.

 

[Andrzejek]

Wouldn't it be easier to use Replace?

Dim str As String

str = "(123)3425-56-456"
[green]
'str = Replace(str, "-", "")
'str = Replace(str, "(", "")
'str = Replace(str, ")", "")[/green]

str = Replace(Replace(Replace(str, "-", ""), "(", ""), ")", "")

MsgBox str

Have fun.

---- Andy

 

[Sandman83]

Easier? Sure, but wouldn't that be slower since it needs to check every character to see if it is one of the characters that needs replaced?

 

[Andrzejek]

Well, that's what your Function fStripTFN does anyway, right?

You may argue if Replace is slower/faster than combination of If/Mid/Left/Right/Len with some *magic numbers* in your function.

I am just a lazy guy and I like to have it simple. But that's just me

Have fun.

---- Andy

 

[MajP]

Inline functions are almost always faster than UDFs.
Andy's can be written in line.
Also subqueries are slow.

You should use Andy's function on a calculated field on the right side of the query and a left outer join to the field on the right side of the query and return all records where the value is null.

This will be way faster

 

[Andrzejek]

I ment it as a 'one-liner':

DELETE tblTFN.TFN, *
FROM tblTFN
WHERE tblTFN.TFN Not In (
SELECT Replace(Replace(Replace(tblTempTFN.TFN, "-", ""), "(", ""), ")", "") 
FROM tblTempTFN);

And I would use MajP's way of joining tables.

Have fun.

---- Andy

 

[Sandman83]

Thx Andy, I wasn't thinking about putting it directly in the query. I copy & pasted your query above to run a test. On the upside, it does work even if the VBA editor had been opened. On the downside, the UDF I posted above is about 900% faster. (6.2 seconds vs 58.6 seconds)

I'll try using a left outer join to see if that helps.

 

[Andrzejek]

Do you use this DELETE statement often?
It loks to me like a 'one shot' deal because why would you have a lot of antries in the field TFN in table tblTempTFN with '-' and '(' and ')'?

Have fun.

---- Andy

 

[Sandman83]

Nope, wouldn't be often. This is a side project for me so no hard time frame. In these cases I like to at least try to build it as well as I can, that way I can hopefully learn something useful in the process.

 

[dhookom]

You should use Andy's function on a calculated field on the right side of the query and a left outer join to the field on the right side of the query and return all records where the value is null.

I'm not sure this query would allow updates/deletion. You could probably use this method to append to a table with a primary key that would allow an outer join deletion.

Duane
Hook'D on Access
MS Access MVP

 

[Sandman83]

Oh the power of the Almighty JOIN!! I bow before thee.

I ended up using the following:

DELETE tblTFN.*
FROM tblTFN LEFT JOIN tblTempTFN ON (Replace(Replace(Replace(tblTempTFN.TFN, "-", ""), "(", ""), ")", ""))=tblTFN.TFN
WHERE tblTempTFN.TFN is Null;

The difference between using replace and left/mid/right was nonexistent once the LEFT JOIN was added as they both are virtually instantaneous. I agree with you Andy, the replace is easier (on both the eyes and readability) then the left/mid/right.

I did try the following as well:

DELETE tblTFN.*
FROM tblTFN LEFT JOIN tblTempTFN ON fstripTFN(tblTempTFN.TFN)=tblTFN.TFN
WHERE tblTempTFN.TFN is Null;

And while it was just as fast as the first statement if the VBA editor had never been opened, it took about 3 seconds if the VBA editor is/was open.

So this still leaves me with my original question though, which I may just have to leave unanswered. Why does having the VBA editor open, (or even if it was open and is now closed) have any affect on the performance of a UDF in a SQL statement?

Thanks everyone for your suggestions and examples. At least I have a method that is fast and efficient.

 

[PHV]

Anyway, why not simply this ?

DELETE tblTFN.*
FROM tblTFN
WHERE fstripTFN(TFN)<>TFN

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886