0

On screen UI, use unbound field to enter mix of ASCII and Unicode character text string for searching. On the screen, it is correct - for example "White白色". But, on VBA code, the Unicode character of the unbound filed becomes to '?' and cannot be used for searching - "White??" for the above example. How to get the mixed ASCII and Unicode string as on the screen for VBA code?

Below is my code. Me.txName is unbound text field, fiterstr is subform filter. It works, if name is all ASCII. It will search '?', if Unicode is entered.

Dim filterstr As String
If Me.txName <> "" Then
    filterstr = "(Customer.Company LIKE '*" & Me.txName & "*')"
End If

Me.sfmCustomerList4Search.Form.Filter = filterstr
YellowLarry
  • 383
  • 1
  • 4
  • 16
  • Please add your code. VBA has unicode support, but a number of built-in functions (such as `Debug.Print`, printing to the immediate window and `MsgBox`) don't. I'm 99% sure that string does contain unicode characters, you're just passing it to functions that don't. – Erik A Mar 17 '19 at 15:29
  • Update question to include code. Thanks. – YellowLarry Mar 17 '19 at 16:50
  • And does that code malfunction? If so, how? Error messages? – Erik A Mar 17 '19 at 16:56
  • No, code is right, except the filter is not filtering Unicode while the result is filtered with '?". For example, if I have two records, one is White白色 and the other is White??. The result is White??, not White白色, if I entered White白色 to txName. – YellowLarry Mar 17 '19 at 17:03
  • That's odd. In a quick test, I can't reproduce that issue. It might be a collation issue. Try changing `filterstr` to `"InStr(Customer.Company, '" & Me.txName & "')"`. Note that it will do a case-sensitive comparison. – Erik A Mar 17 '19 at 17:13
  • Use InStr and result is still the same. Will it be possible I use English version Windows? My Windows 7 Home Premium display language is English. I do not install other language for menu display while I install Chinese keyboard to enter Chinese. – YellowLarry Mar 17 '19 at 17:29
  • Try printing the string to a message box to be sure it actually contains the characters you think. It should be independent of operating system afaik. I've written up an unicode-compatible messagebox-implementation [here](https://stackoverflow.com/a/55210316/7296893) – Erik A Mar 17 '19 at 18:16
  • Erik, Thank you so much for your help. Use MsgBoxW and confirm Me.txName has Unicode character. It shows up by MsgBoxW. – YellowLarry Mar 17 '19 at 20:18
  • Then the second step is checking that it's still there after setting the filter. Try using `MsgBoxW` with `Me.sfmCustomerList4Search.Form.Filter` after changing it. There might be a problem with storing the unicode string in the form property. – Erik A Mar 17 '19 at 20:25
  • MsgBoxW Me.sfmCustomerList4Search.Form.Filter after Me.sfmCustomerList4Search.Form.FilterOn = True code and still can see unicode character. Will it be SQL Server? I use SQL Server 2014 Express as backend. Access is front end UI. Access table is linked to SQL Server by ODBC. – YellowLarry Mar 17 '19 at 20:41
  • That's a relevant piece of information. If you're using SQL server, I'd use a form-valued parameter instead of string concatenation: `"(Customer.Company LIKE '*' & Forms!MyFormName!txName & '*')"`. That ensures the field value is passed as a parameter to SQL server, keeping proper encoding – Erik A Mar 17 '19 at 20:45
  • Data type in SQL is nvarchar(255) and it is ShortText on Access table. I can see Unicode shows on all form display. – YellowLarry Mar 17 '19 at 20:46
  • Yup, but you need to properly pass the string to SQL server. The way to do that is by using parameters, and not within the SQL statement. I've written a piece on using parameters in Access [here](https://stackoverflow.com/q/49509615/7296893), I think a form-valued parameter is by far the most simple way to do it. – Erik A Mar 17 '19 at 20:50
  • This is my code for subform filter. filterstr = " (Customer.Company LIKE '*" & Me.txName & "*')". It is same with your suggestion. Still no luck. – YellowLarry Mar 17 '19 at 20:53
  • That's clearly not the same as my suggestion, and not using form-valued parameters. Read it again, and follow the exact syntax (with `Forms!` and single quotes, not double quotes. You need to make sure the database engine concatenates the values, not VBA. – Erik A Mar 17 '19 at 20:56
  • 1
    Also, since you're using SQL server, you must make sure it uses a collation that's compatible with Chinese characters. See [Chinese collation for MS SQL](https://stackoverflow.com/questions/42253272/chinese-collation-for-ms-sql) – Erik A Mar 17 '19 at 21:01
  • Thank you so much. I update SQL collation and it works. Thank you again for your excellent help. – YellowLarry Mar 17 '19 at 21:25

1 Answers1

0

With Erik A. help, this question has been solved. On my question, there are two problems.

  1. Access Msgbox does not support unicode. Erik A. has written up an unicode-compatible messagebox-implementation MsgboxW here

  2. Data is stored ad SQL Server while Access is front end UI. SQL collation must be setup to compatible with Unicode language. Please see See Chinese collation for MS SQL.

YellowLarry
  • 383
  • 1
  • 4
  • 16