continually getting a parameter box "error" when I use an SQL query to grab a value?

Question

I'm trying to create a button inside of a Microsoft Access 2016 database for inventory tracking that, on click, displays an InputBox asking for the Serial Number, UDID, and/or IMEI of the device in question. I have an SQL query embedded in the private sub, code below:

Private Sub cmdInventoryItems_Click()
On Error Resume Next

Dim vItem As String
vItem = InputBox("Type the Serial Number/UDID/IMEI of your device.", "Text 
Search")

Dim StrSQL As String
StrSQL = "Select * from tbl_Inventory where [Serial Number/UDID/IMEI] like 
[%" & vItem & "%]"
Me.RecordSource = StrSQL

End Sub

The percentage signs inside the code are meant to allow the string to be compared to any piece of the Serial Number/UDID/IMEI field.

Whenever I click cmdInventoryItems it throws an InputBox, as planned, but when a value is input into the textbox, access asks for a parameter value with the prompt being the value I just entered surrounded by percentage symbols. If I type nothing into the "Enter parameter value" box everything disappears on my main form.

Can anyone explain what's going on, and how to fix it? Thanks for any help I can get.

Andy G Andy G · Accepted Answer · 2018-02-28T15:03:53

Access expects square brackets to denote a field (or table) name or a parameter. If you tested with somefield = [anything] you will see a parameter box appearing containing the text anything.

Get rid of the square brackets and replace them with single quotes, which you need to surround a textual expression, '%" & vItem & "%'".

However, the wildcard character for MS Access is an asterisk * not a percent sign.

continually getting a parameter box "error" when I use an SQL query to grab a value?

2 Answers