Search code examples
ms-accessvbams-access-2007

Characters appended to a string all appear as question marks in VBA

I have been using the same code to get the fullname from the current user for a while now and it suddenly started behaving weird. I have a string variable named CurrentUser which I populate with my function GetFullNameOfLoggedUser(). This return in the immediate window (msgbox also works fine) "Lastname, Firstname (F)" which it is supposed to.

When I want to give the variable to another string variable called 'sql1', it starts to behave weird all of a sudden:

CurrentUser = GetFullNameOfLoggedUser()
Dim sql1 As String: sql1 = "UPDATE Tbl_Records SET Tbl_Records.[Added by] = """ & CurrentUser & """ WHERE Tbl_Records.[Added by] IS NULL;"

The value of sql1 suddenly becomes:

UPDATE Tbl_Records SET Tbl_Records.[Added by] = "Lastname, Firstname (F)? ????? ?????????????????? ??? ?? ?????

Does anybody have a clue where all the question marks come from?

Disclaimer Lastname and Firstname are obviously regular values, they are omitted for the sake of privacy.

Extra info:

To get the full network name, I am using this fine piece of code from Dev Ashish which makes use of windows API: http://access.mvps.org/access/api/api0066.htm

The function has been dimmed as string. I have added an "" at the end of the function to ensure the value is a string type:

Function GetFullNameOfLoggedUser(Optional strUserName As String) As String
...
GetFullNameOfLoggedUser = StrFromPtrW(pTmp.EUI_full_name) & ""
End Function

As seen in the locals window, it truly is a string. (This snap has been taken right before the end of the funcion, so no further changes will happen to the variable.

CurrentUser has also explicitly been defined as a string variable. Option Explicit is also active on every page.

Solution

  • There does seem to be something a bit strange about the string that fGetFullNameOfLoggedUser() returns. When I run the code

    Dim s As String
s = fGetFullNameOfLoggedUser()
Debug.Print Len(s)
Debug.Print s

    I get

     13
GORD THOMPSON

    which looks correct. However, if I change the last Debug.Print to

    Debug.Print "|" & s & "|"

    I get

    |GORD THOMPSON?

    with the final character being a question mark ? instead of a pipe |. However, if I Trim() the string

    Debug.Print "|" & Trim(s) & "|"

    then I get

    |GORD THOMPSON|