using wildcard to find records of a textbox linked to a query

Question

I have a Textbox that requires me to type a number and then click a button to find the related values. but now what i need it to do is if i type 3 numbers Ex."123" it will return all the numbers that are related to any record starting with "123". I'm not sure if it's possible. and i'm using the following code but when i run it it gives me a type missmatch.

Private Sub Command35_Click()
  If IsNull(Me.Text22.Value) = True Then
    MsgBox "You need to enter a reference number"
  ElseIf IsNull(Me.Text22.Value) = False Then
    Me.List28.RowSource = "SELECT dbo_Typesofmaterial.[MATERIAL NAME], dbo_Inventory.[REFF NUMBER], dbo_Whse.[NAME], dbo_Inventory.NO_IN, dbo_Inventory.[POSITION], dbo_Inventory.[PO NO], dbo_Inventory.[REF NO2], dbo_Suppliers.SUPPLIERID, dbo_Inventory.DATE, dbo_Inventory.MATTYPE FROM (dbo_OrderDetails INNER JOIN (((dbo_Inventory INNER JOIN dbo_PurchaseOrders ON dbo_Inventory.[PO NO] = dbo_PurchaseOrders.[PO NO]) INNER JOIN dbo_Suppliers ON dbo_PurchaseOrders.ID = dbo_Suppliers.ID) INNER JOIN dbo_Typesofmaterial ON dbo_Inventory.MATTYPE = dbo_Typesofaterial.ID) ON dbo_OrderDetails.[REFF NUMBER] = dbo_Inventory.[REFF NUMBER]) INNER JOIN dbo_Whse ON dbo_Inventory.[FINAL DESTN ] = dbo_Whse.[WHSE NO] WHERE (((dbo_Inventory.[REF NO2])= " * "  & [Forms]![Ref No Locator]![Text22] &  " * " ));"
  Else
  End If
End Sub

score 3 · Accepted Answer · answered Jan 10 '14 at 15:36

Your offending criteria is as follows:

WHERE (((dbo_Inventory.[REF NO2])= " * " & [Forms]![Ref No Locator]![Text22] & " * " ))

I think you have a couple of problems. First, you need to use Like rather than = for a wildcard search. Second, dbo_Inventory.[REF NO2] is a numeric field and you are trying to use a string as the criteria value. Hence you need to use CStr to convert the numeric field to a string to match the criteria being used. In addition you don't need the wildcard at the start as you are matching the first 3 characters.

WHERE (((CStr(dbo_Inventory.[REF NO2])) Like '" & [Forms]![Ref No Locator]![Text22] & "*'" ))

Thank you @Matt Weller That was exactly what i was missing! i didn't know that i had to use CStr to convert to a string. I'll definitely keep that in mind. Once again thank you for your Help. — Dre488, Jan 10 '14 at 15:49

using wildcard to find records of a textbox linked to a query

1 Answers1