Nz in Custom Functio to Update Records (1 Viewer)

silentwolf

Active member
Local time
Today, 09:42
Joined
Jun 12, 2009
Messages
545
Hi guys,

got stuck again :(

I try with this code below to update a field called [Telefon Firma] with a custom function Calleds Clean_PhoneNumber

However when the field is empty I get an error Null not allowed or something down that line as it is in german. Sorry not sure what it is exactly in Access Terms.

I modified that code from CJ London I believe it was. :)

Sub TestPhoneNumberUpdate()
Dim rst As dao.Recordset
Set rst = CurrentDb.OpenRecordset("qryContactEmails", dbOpenDynaset)
With rst
Do While Not .EOF
.Edit
![Telefon Firma] = Nz(Clean_PhoneNumber(![Telefon Firma]), "")
.Update
.MoveNext
Loop
.Close
End With
Beep
MsgBox "done", vbInformation



End Sub

the issue is in the ![Telefon Firma] = Nz(Clean_PhoneNumber(![Telefon Firma]), "")

How to I need to change that line of code so it workes with the Nz Function?

Thanks for any help!

Cheers

Albert
 

silentwolf

Active member
Local time
Today, 09:42
Joined
Jun 12, 2009
Messages
545
Here is the custom function as well

Credit to someone on the net unfortunatelly I do not know the source anymore!

Code:
Public Function Clean_PhoneNumber(ByVal strWert As String) As String
    '** Dimensionierung der Variablen
    Dim i As Integer
    Const strSonderzeichen As String = "-.,:;#+ß'*?=)(/&%$§!~\}][{"
    
    '** Durchlaufen des übergebenen Strings
    For i = 1 To Len(strSonderzeichen)
        strWert = Replace(strWert, mID(strSonderzeichen, i, 1), "")
    Next i
    
    '** Bereinigter String der Funktion zurückgeben
    Clean_PhoneNumber = strWert

End Function
 

Minty

AWF VIP
Local time
Today, 16:42
Joined
Jul 26, 2013
Messages
10,354
Why not simply run an update query using the function, something like

Code:
UPDATE  tblYourTable SET [Telefon Firma] = Nz(Clean_PhoneNumber([Telefon Firma],"")
WHERE [Telefon Firma] is not null
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:42
Joined
May 7, 2009
Messages
19,169
you modify this part to:

...
...
.Edit
![Telefon Firma] = Clean_PhoneNumber(![Telefon Firma] & "")
.Update
...
...
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:42
Joined
Feb 19, 2013
Messages
16,553
Is your clean_phonenumber function returning a data type such as string?
 

silentwolf

Active member
Local time
Today, 09:42
Joined
Jun 12, 2009
Messages
545
Hi,
oh of course thanks that is the trick!

Cheers )
 

silentwolf

Active member
Local time
Today, 09:42
Joined
Jun 12, 2009
Messages
545
>Oh thanks many replies!

I changed as arnelgp said and that works great! Many thanks to that!!
But of course with an update Query it workes to.

Which would you prefer one over the other and why?

Update Query is faster I guess or?

Is your clean_phonenumber function returning a data type such as string?

Yes it does return a string


Chees
 

Minty

AWF VIP
Local time
Today, 16:42
Joined
Jul 26, 2013
Messages
10,354
But of course with an update Query it workes to.

Which would you prefer one over the other and why?

Update Query is faster I guess or?
A query will process the entire recordset without the need to loop through each record.
This will be significantly quicker on a larger dataset.

You should always try to use set based processing wherever possible, rather than looping one at a time.
 

silentwolf

Active member
Local time
Today, 09:42
Joined
Jun 12, 2009
Messages
545
Difference in Speed for just 400 Records

SQL=41 ms
VBA=2717 ms

thats a big difference hmm
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:42
Joined
Feb 19, 2013
Messages
16,553
FYI since you have a solution
Yes it does return a string
A function can never return a null as you cannot assign null to a string or a number function in this way as the function will take on a default value of a zls or 0 respectively. Only if you have defined the function as a variant can you assign a null

so you would apply the nz to the field you are passing as a parameter

Clean_PhoneNumber(nz(![Telefon Firma],""))
 
Last edited:

silentwolf

Active member
Local time
Today, 09:42
Joined
Jun 12, 2009
Messages
545
Oh ok thanks CJ_Londan!

will keep that in mind I hope :)

Cheers
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:42
Joined
Feb 28, 2001
Messages
26,999
As to the OTHER factor - speed - the issue is that VBA is not compiled to "true" machine code. It is compiled to a pseudo-code that is then INTERPRETED rather than executed. It can be written as efficient as you are able but you still will always have overhead due to the fact that the code is being executed via a program rather than directly via hardware. Whereas a query, once it sets up what it is going to do, does it via compiled code AND there is no explicit loop to build because the query has an implicit "FOR EACH record-matching-the-WHERE-clause..." loop built in, but again executed in machine code.

The only time VBA is comparably fast is when the number of records in the table is so short that the query setup overhead is much slower than the execution phase of the query. OR when the query involves calling a public function written in VBA.
 

silentwolf

Active member
Local time
Today, 09:42
Joined
Jun 12, 2009
Messages
545
So this code is not really correct or

Code:
    Dim db As DAO.Database
    Dim tbd As DAO.TableDef
    
    Dim TableName As String
    TableName = "Contacts"
    
    Dim strSQL As String
    
    Set db = CurrentDb
    Set tbd = db.TableDefs(TableName)
      
    strSQL = "UPDATE " & TableName & " SET [TelCompany] = Clean_PhoneNumber([TelCompany])" & _
            "WHERE [TelCompany] Is Not Null"
        
    db.Execute strSQL
    
    Set tbd = Nothing
    Set db = Nothing

it does the update ok so far but not sure if that strSQL is correct or should be done better?

Cheers
 

silentwolf

Active member
Local time
Today, 09:42
Joined
Jun 12, 2009
Messages
545
I think it should be like this or

Code:
    strSQL = "UPDATE " & TableName & " SET [Mobile] = Clean_PhoneNumber([Mobile])" & _
            "WHERE [Mobile] <>"""""
 

Minty

AWF VIP
Local time
Today, 16:42
Joined
Jul 26, 2013
Messages
10,354
You can do that slightly more elegantly with

strSQL = "UPDATE " & TableName & " SET [Mobile] = Clean_PhoneNumber([Mobile])" & _
" WHERE Len( [Mobile] & '') > 0 "

The Len() adds a zero length string (ZLS) to the existing string so you are checking for both nulls and ZLS content at the same time.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:42
Joined
Jan 20, 2009
Messages
12,849
You can do that slightly more elegantly with

strSQL = "UPDATE " & TableName & " SET [Mobile] = Clean_PhoneNumber([Mobile])" & _
" WHERE Len( [Mobile] & '') > 0 "
Depends what you mean by "elegance". A short expression may be pretty to look at but unfortunately pretty inefficient.

It is probably not terribly relevant in the particular situation in this thread, especially if most of the records need to be processed anyway but it should be considered a general principle in queries where a significant proportion of Nulls or ZLS are likely to be encountered.

The down side of concatenating a ZLS is that the value of every record must be processed before it can be tested for SELECT. Plus the engine cannot use any index on the field. Similarly using Nz() for the same kind of purpose, which is doubly inefficient because it is not an engine function but a VBA function.

It is a lot more efficient to use a longer expression like this:
Code:
WHERE field Is Not Null AND field <> ""

This is a SARGable query condition. All database developers should familiarise themselves with this concept because it is fundamentally important for performance.
 

silentwolf

Active member
Local time
Today, 09:42
Joined
Jun 12, 2009
Messages
545
Sorry guys,
somehow I dont get the notification to see the responses.
Just saw it when I got into my posts!

@Minty, cheers did not know that function at all! But will look into it and many thanks for pointing it out!

@Galaxiom, thanks I appreciate learning what is the best solution so great to have that pointed out as well!

Cheers,
and again sorry did not mean to be rude for not answering when someone replies to my thread!

Will check in the future more on my threads so I don't oversee the replies!
 

Users who are viewing this thread

Top Bottom