1

I got this problem:

Run-time error '3831': The Multi-valued field '[Type]' cannot be used on a WHERE or HAVING clause

Here is my code:

Private Sub Command379_Click       
    Dim strsearch as String 
    Dim strText as String

    strText = Me.TxtSearch.Value
    strsearch = "Select * from qryInfo where (([Supplier Name] LIKE ""*" & strText & "*"") OR ([Type] LIKE ""*" & strText & "*""))"

    Me.RecordSource = strsearch
End Sub

[Type] consists of a multi-valued list.

I tried to put [Type].Value but it didn't work too. It returned this message instead:

Runtime error 3126. Invalid bracketing of name '[qryInfo].[tblGeneralInfo].[Supplier Name]'

Can anyone please tell me how I can fix this please?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
T D
  • 27
  • 7
  • The .Value should work. Review https://support.office.com/en-us/article/Using-multivalued-fields-in-queries-6F64F92D-659F-411C-9503-B6624E1E323A#bm4_6 – June7 Jan 17 '19 at 10:24
  • 3
    In the long run, the way to fix this is to not use MVF. https://stackoverflow.com/questions/1461582/multivalued-fields-a-good-idea -- http://www.utteraccess.com/forum/Multi-Fields-Evils-Lo-t1991337.html – Andre Jan 17 '19 at 10:28
  • 2
    Also Type is a reserved word, so not a good choice. – Minty Jan 17 '19 at 10:45
  • @June7 For some reason, the .Value didn't even work for me. I got this message:Invalid bracketing of name '[qryInfo].[tblGeneralInfo].[Supplier Name]' – T D Jan 17 '19 at 14:20
  • if `[Type].Value` isn't working the problem maybe in how `qryInfo` is generated. Provide the SQL code for that as well as the structure of the original table(s). – SunKnight0 Jan 17 '19 at 16:04
  • I just tested and .Value works. The error message you show is odd because [tblGeneralInfo] is not in the SQL statement. Did you test this SQL in Access query object? Use literal text in place of the variables to test. – June7 Jan 17 '19 at 19:34

1 Answers1

0

Actually you do not need any parentheses in SQL statement. You can try following

strsearch = "SELECT * FROM [qryInfo] WHERE [Supplier Name] LIKE '*" & strText & "*' OR [Type] LIKE '*" & strText & "*'"
June7
  • 19,874
  • 8
  • 24
  • 34
Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • 4
    Removing parentheses won't make a difference. Issue is [Type] is a multi-value field. – June7 Jan 17 '19 at 10:25