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]

You should probably test with specific data and narrow down to the input that is causing problems. Also to verify it works at all of course. There's a lot of case statements in there with the Like tests so so hard to say if it's hitting a specific test or just something more general.

I don't think the select case True really does much for this function - might as well just use only if else ifs.

 

[silentwolf]

Hi Xenou,
thanks for your help I will try what you suggested and get back to you.

Cheers

 

[silentwolf]

Hi,
it is the amount of empty cells so I asume the empty cells are causing this issue..

I tried with
case strText = ""
TelefonUpdate=""

that did not help..

How could I change it so it might work for empty cells?

Thanks

 

[xenou]

Empty cells can be ambiguous (are the empty strings, or actual Null values?)

I would always test for length since it handles both cases the same (i.e., effectively "IsNullOrEmpty").

Example:

If len(someString) = 0 then
    '//handle empty or null
Else
    '//handle non-empty
End If

 

[james_lankford]

Function TelefonUpdate(ByVal strText As variant) As variant 
    
    if isnull( strText  ) then 
      TelefonUpdate = null
      exit function
    end if
    
    ' other stuff
    ...
    ...
    
end function

 

[silentwolf]

Hi guys,

thanks for your input I will try it out but will take a little time to get back to you not in the office for a while...

Cheers
Albert

 

[silentwolf]

Hi guys,
sorry still struggling with this query.. is there a way to sort this out bevor I used it in access..
I mean I got this file first as an excel file and is there a way of getting the blank cells updated so it will not cause a problem in access?

Thanks for your help or input!

 

[xenou]

1) What is your current code?
2) Provide some sample input data to test with.
3) also please clarify what you mean by you got this data originally in Excel. Is it imported data? A linked spreadsheet? Data that used to be in Excel and now is in Access (in which case it probably doesn't matter anymore about Excel - its what's in the database that is important).

Blank cells shouldn't by themselves cause a problem but it's hard to say without details.

 

[silentwolf]

Hi xenou,
well I have not much of a code .. just tried to test different things..

Function Test(ByVal strText As String) As String
    If IsNull(strText) Then
        Test = Null
        Exit Function
        End If
        Test = strText

End Function

I have a billing software and can export customer data which gets then opend in excel worksheet..with different Telefon Numbers... however as this has been entered rather messy some without an country code and so on..
The idea was to update those numbers so they are uniform and the save it and reimport it into the software...
I thought I update those Phonenumbers in access and then reimport the updated numbers back into the software..
The Select Case Statement are working well accept the values which are Null..
Because there are so many different numbers I thing a if then else statement is rather long and complex..

Thanks

Albert