Chinese characters in Access SQL Query

After populating the recordsource the next action is clicking on one of the fields populated to “activate” the record. When clicking this, the goal is that the SEC_ID (A GUID, Number Data Type) is stored as a tempvar and used in future queries. This GUID is also placed in a text box just for a visual debug. However it doesn’t put the GUID, it puts random Chinese characters. I’ve tried to place it into a MsgBox just to also see and it spits out “???????”.

My code to populate the rowsource:

Dim componentListSQL As String
If FCSUtilities.AssessmentUoM = "Metric" Then
    componentListSQL = "SELECT DISTINCT [100b_Working].SEC_SYS_COMP_ID, [100b_Working].SEC_ID, [110b_RO_Material_Category].MAT_CAT_DESC, [110b_RO_Component_Type].COMP_TYPE_DESC, [110b_RO_Material_Category].MAT_CAT_ID, [110b_RO_Component_Type].COMP_TYPE_ID, [100b_Working].ID_Number, [100b_Working].Model, [100b_Working].Serial_Number, [100b_Working].Capacity, [100b_Working].Manufacturer, [100b_Working].SEC_YEAR_BUILT, ROUND([100b_Working].SEC_QTY, 0) AS SEC_QTY, [100b_Working].UOM_MET_UNIT_ABBR, [100b_Working].UOM_ENG_UNIT_ABBR, [100b_Working].Equipment_Make, [100b_Working].UOM_CONV " _
                     & "FROM (110b_RO_Units_Conversion INNER JOIN (110b_RO_Component_Type INNER JOIN (110b_RO_Material_Category INNER JOIN 110b_RO_CMC ON [110b_RO_Material_Category].MAT_CAT_ID = [110b_RO_CMC].CMC_MCAT_LINK) ON [110b_RO_Component_Type].COMP_TYPE_ID = [110b_RO_CMC].CMC_CTYPE_LINK) ON [110b_RO_Units_Conversion].UOM_ID = [110b_RO_CMC].CMC_UoM) INNER JOIN 100b_Working ON [110b_RO_CMC].CMC_ID = [100b_Working].SEC_CMC_LINK " _
                     & "WHERE ((([100b_Working].SEC_SYS_COMP_ID) = [Forms]![200a_MainWindow]![txtDebugCompSysID]) And (([100b_Working].SEC_ID) Is Not Null)) " _
                     & "ORDER BY [110b_RO_Component_Type].COMP_TYPE_DESC;"
                     
    Me![210_ComponentList].Form.RecordSource = componentListSQL
End If

The OnClick event:

Private Sub txtMaterialCategory_Click()
Me.txtActiveSecID.Value = Me.txtSecID.Value
End Sub

The txtSecID appears as a GUID as it should but it’s in the txtActiveSecID that it becomes Chinese characters even if I attempt to put it as a tempvar then set it into the txtActiveSecID.

I’m not exactly sure what is going on. Looking at different stacks, it points that it’s due to long/memo field but as I said previously, the SEC_ID field data type is Number.

Answer

Per MS documentation https://docs.microsoft.com/en-us/office/vba/api/Access.Application.StringFromGUID:

The Microsoft Access database engine stores GUIDs as arrays of type Byte. However, Access can’t return Byte data from a control on a form or report. To return the value of a GUID from a control, you must convert it to a string. To convert a GUID to a string, use the StringFromGUID function. To convert a string back to a GUID, use the GUIDFromString function.

StringFromGUID(Me.txtSecID.Value)

However that results in output like: {guid {2786C27B-CB7C-4DEA-8340-1338532742DE}}

That should still work as filter critera but could do further processing to extract GUID from that string. Use string manipulation functions to remove the {guid header and trailing }. Review Access – GUID value stored in textbox, can’t be used in SELECT statements

Advertisement

Answer