1

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?

braX
  • 11,506
  • 5
  • 20
  • 33
Nick
  • 81
  • 6
  • 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 https://social.msdn.microsoft.com/Forums/en-US/78eeab51-f39e-423c-8afc-ebd218d658c3/how-to-make-vba-to-read-and-understand-characters-in-other-keyboard-language – June7 Dec 23 '19 at 09:13

1 Answers1

0

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thanks for helping out Eric. Sorry, I didn’t explain clearly. My data is in Amazon RDS which I linked it to MS SQL. I tried running the queries without N prefix and nothing were found. However, with N prefix, it works just fine. Is there a workaround for this that you know of? – Nick Dec 23 '19 at 10:29
  • Of course if you write T-SQL you need the `N`. If you write Access SQL, you don't. You need to figure out what the actual reason is your `DLookUp` isn't working, because as said, it's not the `N`. Try the query in Access, not at the backend. You will notice the variant with the `N` fails because of a syntax error, and the one without it succeeds. – Erik A Dec 23 '19 at 10:36
  • I, ll try and figure it out. Thanks for helping me out. Before this I used local data and everything worked perfectly until I switched my data to SQL. May be it’s worth noting that when “CustName” string is in English, DLookUp function is able to find the right CustID, but when the string is in Thai it couldn’t find anything. – Nick Dec 23 '19 at 10:45
  • There might be collation issues, you can perform matching in VBA by using `StrComp(FullName," & "'" & CustName & "'", 0) = 0`. However, performance will suffer. You should also check capitalization/collation issues, Access is generally more permissive than SQL server – Erik A Dec 23 '19 at 11:05