Solved didnt update due to type conversion

[RichardMarbat]

Hello

can someone help me with this ... I'm new to access, not programming, please see the code below (i know it's full of rubbish etc, I'm just testing the framework...)

JoinName is the function I'm working on, test is just to fire it, eventually, I want to pass fields from a table and base the return on the parameters passed

the below seems to work fine... BUT when I use this as a query

UPDATE [Welcome-20211224] SET [Welcome-20211224].Fullname = joinname(IIf(IsEmpty([Welcome-20211224]![firstName])," ",[Welcome-20211224]![FirstName]),IIf(IsEmpty([Welcome-20211224]![lastname])," ",[Welcome-20211224]![lastname]));

all I'm doing is passing 2 fields and having them returned as one string (i want to do some logic on the values passed to determine the return value)
again the above works fine until one of the field values is empty then I get the message (x fields didn't update due to type conversion)

I just can't work it out, when I use the test function I can pass "" no problem, I tried to debug it and it would seem that access doesn't even pass the record to the function (I removed the first records field and the debug started at record 2) I added the IIF statement to try to pass a string where the value is empty, that didn't work... all my fields are strings, so I figure there is a difference between an empty string and an empty table field

any help would be greatly appreciated

thanks, Richard

---------------------------------------------------------------------------------------------------------------------------
Function joinname(aa1 As String, bb2 As String) As String
    ' joinname = Trim(name1) & " " & Trim(name2)
    Debug.Print VarType(aa1)
    Debug.Print VarType(bb2)
   
    aa1 = Trim(Nz(aa1))
    bb2 = Trim(Nz(bb2))
   
    If aa1 = "" Or bb2 = "" Then
        joinname = ""
    Else
        joinname = Trim(StrConv(aa1, vbProperCase)) & " " & Trim(StrConv(bb2, vbProperCase))
    End If
End Function
---------------------------------------------------------------------------------------------------------------------------
Sub test()
    Dim no1 As String
    Dim no2 As String
    Dim ret As String
    Dim aa As String
    no1 = "a"
    no2 = "b"
    ret = joinname(no1, no2)
    Debug.Print ret
End Sub

Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please read this for further information:-
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/
Please feel free to Remove this Comment

By the way I think your code was very well presented even before I added the code tags, but now look at it, it looks awesome!

 

[pbaldy]

I would try IsNull() rather than IsEmpty(), which is rarely used.

Null Empty etc

 

[plog]

I would use NZ:

MS Access: Nz Function

This MSAccess tutorial explains how to use the Access Nz function with syntax and examples. The Microsoft Access Nz function lets you return a value when a variant is null.

www.techonthenet.com

That way you just tell it what to use if it is NULL instead of doing any testing to capture the NULL. Also, you are not escaping your return value within the SQL:

...SET [Welcome-20211224].Fullname = joinnam(...

Whatever joinname() returns needs to be surrounded by single quotes within the SQL, otherwise it thinks its a field name.

Lastly, why? UPDATE queries always make me sniff for a poor table structure or lack of understanding of normalization. Add to it that you plan on passing joinname fields from another table and this really is setting off my sensor. What's the ultimate goal? Why are you building a function to move around data in your database?

 

[arnelgp]

you can also change the function to accept Variant instead of string:

Function joinname(Byval aa1 As Variant, Byval bb2 As Variant) As String
    ' joinname = Trim(name1) & " " & Trim(name2)
    Debug.Print VarType(aa1)
    Debug.Print VarType(bb2)
 
    aa1 = Trim(aa1 & "")
    bb2 = Trim(bb2 & "")
 
    If aa1 = "" Or bb2 = "" Then
        'default, it will return the zls
        'joinname = ""
    Else
        joinname = StrConv(aa1, vbProperCase) & " " & StrConv(bb2, vbProperCase)
    End If
End Function

with this new function you can simplify your Query:

UPDATE [Welcome-20211224] As T 
SET T.Fullname = 
joinname(T.[firstName], T.[lastname]);