Solved Filter data to left of character using Left and InStr functions

[phoust]

I have an invoice number that is a combination of a sale # , a "-" and a ID number. Example: 388-LA142. I am trying to select all the invoices that start with a specific sale#. I have an unbound text field where I specify the sale#. I keep getting a type mismatch and I can't figure out how to fix it.
This is the code:
Dim strAuction As String
strAuction = Me!txtAuction
DoCmd.Close
DoCmd.OpenForm "frmSaleInvoices", acNormal, , "Left([Invoice],(InStr([Invoice]," - ")-1)" '= & strAuction & "'"

I would appreciate any help you can offer.

 

[CJ_London]

You are passing this as the where parameter of openform? Looks to me like your single and double quotes are mixed up - try debug printing the string or stepping through the code to see what you have

I would suggest an alternative string

“Invoice like ‘“ & strauction & “-*”

Note this is posted from my phone which uses smart quotes so correct them if you copy/paste

 

[phoust]

That worked. Thanks!

 

[CJ_London]

Please mark as solved (see top of thread)