Can you use DLookUp function with N prefix?

Question

I'm trying to search for a specific value on a table linked with sql using DLookUp function. However, the string that I'm searching for is in Thai language. Here's my code.

Dim cust_Id1 As Variant: cust_Id1 = DLookup("[CustID]", "dbo_TblCustomer", _
                                        "FullName=" & "'" & CustName & "'")
CustID.Value = cust_Id1

What I want to do is find a customer's id by customer's name and store it in a variable. I know that I can query the SQL like the code below.

SELECT CustID
  FROM [aTable].[dbo].[TblCustomer]
  WHERE FullName LIKE N'นามสมมุติ'

Is there a way to do it using DLookUp function? If not, what are other ways to get CustID value?

Is customer name displayed in a control such as a combobx named CustName? Is CustID a column of combobox? Grab that ID by referencing that combobox column by index. Otherwise maybe the following will help social.msdn.microsoft.com/Forums/en-US/… — June7

Erik A Erik A · Accepted Answer · 2019-12-23T10:13:42

You don't need the N' in Access SQL. All strings are unicode (UTF-16) strings.

Note, though, that the VBE doesn't support unicode strings, and MsgBox doesn't support it either, so it might display incorrectly when debugging. But the lookup should just work.

However, your code should just work, and if it doesn't, it's probably not due to the unicode string.

Also note your code is wide open to SQL injection. You probably want to use parameters.

Can you use DLookUp function with N prefix?

1 Answers