Update Query with custom function DataType Problem

[silentwolf]

Hi guys,
I am stuck with an update query which gives me an error datatype issue.

I have created a custom function to update phonenumbers

Function TelefonUpdate(ByVal strText As String) As String
    
    Select Case True
        Case strText Like "0*#"
            If strText Like "0####/#*#" Then
                TelefonUpdate = Replace(strText, "/", " ", 1, 1)
            ElseIf strText Like "0####-#*#" Then
                TelefonUpdate = Replace(strText, "-", " ", 1, 1)
            ElseIf strText Like "0 ## ##/#*#" Then
                TelefonUpdate = Replace(strText, " ", "", 1, 2)
            ElseIf strText Like "004#/###/#*#" Then
                TelefonUpdate = Replace(strText, "/", " ", 1, 2)
            End If
            
        Case strText Like "+##*"
            If strText Like "+4# ### / #*" Then
                TelefonUpdate = Replace(strText, " / ", " ", 1, 1)
            ElseIf strText Like "+4#/####/#*#" Then
                TelefonUpdate = Replace(strText, "/", " ", 1, 2)
            ElseIf strText Like "+4# (0) #*#" Then
                TelefonUpdate = Replace(strText, "(0)", "", 1, 1)
            ElseIf strText Like "+4# (0)####/#*#" Then
                TelefonUpdate = Replace(strText, "(0)", "", 1, 1)
            End If
            
        Case Else
            TelefonUpdate = strText
        End Select
        
End Function

A update Query with following syntax

UPDATE tblKunden SET tblKunden.kunTelefon = TelefonUpdate([kunTelefon]);

The field kunTelefon is a string DataType

Why do I get this error? Can anyone please help with this problem?

As always much appreciated!

 

[xenou]

Given the scenario above, you might want to write your function in Excel (I'm not sure why you wouldn't).
At any rate, the above function would work for real nulls but not empty strings.

Do you know if your problem is really NULL values, or blank/empty string values?

 

[xenou]

Another help might be to change your function to take variants (so it can take nulls). Not sure what best practice is but I have always used variant inputs to my Access functions because Null is weird.

i.e.,

Public Function MyFunc(ByVal arg as Variant) as String

 

[silentwolf]

Hi xenou,
in access I tried with a query

SELECT tblKunden.kunTelefon
FROM tblKunden
WHERE (((tblKunden.kunTelefon) Is Null));

it returns 162 records .. the amount of what the query has it #Error ..
Unfortunatelly my time today is limited on here.. but I try your suggestion and perhabs it is better to write a function in excel.. and do the update there..
However I will try with a variant.. many thanks and I'll keep you posted..

Cheers again..
,
Albert

 

[xenou]

okay, that was actually what james lankford had for a function signature also:

Function TelefonUpdate([B][COLOR="#FF0000"]ByVal strText As variant[/COLOR][/B]) As variant 
    
    if isnull( strText  ) then 
      TelefonUpdate = null
      exit function
    end if
    
    ' other stuff
    ...
    ...
    
end function

 

[silentwolf]

I am really sorry guys but I am still not getting the right answers..

Function TelefonUpdateIf(ByVal strText As Variant) As Variant
    
        If IsNull(strText) Then
            TelefonUpdateIf = Null
            Exit Function
        End If
         
        If strText Like "0*#" Then
            If strText Like "0####/#*#" Then
                TelefonUpdateIf = Replace(strText, "/", " ", 1, 1)
            ElseIf strText Like "0####-#*#" Then
                TelefonUpdateIf = Replace(strText, "-", " ", 1, 1)
            ElseIf strText Like "0 ## ##/#*#" Then
                TelefonUpdateIf = Replace(strText, " ", "", 1, 2)
            ElseIf strText Like "004#/###/#*#" Then
                TelefonUpdateIf = Replace(strText, "/", " ", 1, 2)
            End If
            
        If strText Like "+##*" Then
            If strText Like "+4# ### / #*" Then
                TelefonUpdateIf = Replace(strText, " / ", " ", 1, 1)
            ElseIf strText Like "+4#/####/#*#" Then
                TelefonUpdateIf = Replace(strText, "/", " ", 1, 2)
            ElseIf strText Like "+4# (0) #*#" Then
                TelefonUpdateIf = Replace(strText, "(0)", "", 1, 1)
            ElseIf strText Like "+4# (0)####/#*#" Then
                TelefonUpdateIf = Replace(strText, "(0)", "", 1, 1)
            End If
            
        
'        Else
'            TelefonUpdateIf = strText
'
        End If
        End If
End Function

this is what I have tried.. now the thing is I am not getting any error anymore but .. I end up with 106 records instead of 294 which I should have..

 

[james_lankford]

put a breakpoint on this line

If strText Like "0*#" Then

then run the update query

it should open the function and you can step through it with F8 and see if its entering any of the "if" statements

it looks like you aren't entering any of the "if" statements
you're only hitting the blanks

 

[silentwolf]

Hi James,
I am just about stepping through the code...
if I am useing this code below..

Function TelefonUpdateIf(ByVal strText As Variant) As Variant
    
        If IsNull(strText) Then
            TelefonUpdateIf = Null
            Exit Function
        End If
         
        If strText Like "0*#" Then
            If strText Like "0####/#*#" Then
                TelefonUpdateIf = Replace(strText, "/", " ", 1, 1)
            ElseIf strText Like "0####-#*#" Then
                TelefonUpdateIf = Replace(strText, "-", " ", 1, 1)
            ElseIf strText Like "0 ## ##/#*#" Then
                TelefonUpdateIf = Replace(strText, " ", "", 1, 2)
            ElseIf strText Like "004#/###/#*#" Then
                TelefonUpdateIf = Replace(strText, "/", " ", 1, 2)
            End If
            
        If strText Like "+##*" Then
            If strText Like "+4# ### / #*" Then
                TelefonUpdateIf = Replace(strText, " / ", " ", 1, 1)
            ElseIf strText Like "+4#/####/#*#" Then
                TelefonUpdateIf = Replace(strText, "/", " ", 1, 2)
            ElseIf strText Like "+4# (0) #*#" Then
                TelefonUpdateIf = Replace(strText, "(0)", "", 1, 1)
            ElseIf strText Like "+4# (0)####/#*#" Then
                TelefonUpdateIf = Replace(strText, "(0)", "", 1, 1)
            End If
            
        
        Else
            TelefonUpdateIf = strText
'
        End If
        End If
End Function

then I am getting the same amout of phonenumbers back as I should do .. but it updates first the phonenumber but jumps into the line
else
TelefonUpdateIf=strText

so it changes it back to what it was... so no good

if I leave the line else out then I get not all phone numbers back..

So there must be a slight problem with the else statement I guess..

 

[silentwolf]

Hi guys,

I believe this should do it now..

Function TelefonUpdateIf_02(ByVal strText As Variant) As Variant
    
        If IsNull(strText) Then
            TelefonUpdateIf_02 = Null
            Exit Function
        End If
         
            If strText Like "0####/#*#" Then
                TelefonUpdateIf_02 = Replace(strText, "/", " ", 1, 1)
            ElseIf strText Like "0####-#*#" Then
                TelefonUpdateIf_02 = Replace(strText, "-", " ", 1, 1)
            ElseIf strText Like "0 ## ##/#*#" Then
                TelefonUpdateIf_02 = Replace(strText, " ", "", 1, 2)
            ElseIf strText Like "004#/###/#*#" Then
                TelefonUpdateIf_02 = Replace(strText, "/", " ", 1, 2)
            
            ElseIf strText Like "+4# ### / #*" Then
                TelefonUpdateIf_02 = Replace(strText, " / ", " ", 1, 1)
            ElseIf strText Like "+4#/####/#*#" Then
                TelefonUpdateIf_02 = Replace(strText, "/", " ", 1, 2)
            ElseIf strText Like "+4# (0) #*#" Then
                TelefonUpdateIf_02 = Replace(strText, "(0)", "", 1, 1)
            ElseIf strText Like "+4# (0)####/#*#" Then
                TelefonUpdateIf_02 = Replace(strText, "(0)", "", 1, 1)
        Else
            TelefonUpdateIf_02 = strText
        End If
End Function

maybe someone has a quick look over this function and let me know if there is a different way or better way of doing this..

Otherwise I thank everyone for their input!

Chees .-)