Searching recordset using Criteria not working 1

[wdennis]

Hi<br><br>I've got a problem searching a recordset using a text field as criteria. The field I need to search on is a number appended by a dash to another number, like so:&nbsp;&nbsp;100238-11.&nbsp;&nbsp;When I use filter by form, the criteria appears as: &quot;100023811&quot; with the quotes and no dash.&nbsp;&nbsp;If I enter the number as 100238-11, as it appears in the table, it's not recognized and the search returns an empty recordset.&nbsp;&nbsp;If I enter it without the dash it works.&nbsp;&nbsp;What is happening here?&nbsp;&nbsp;Is there a way to work around this running a search on a RecordsetClone?&nbsp;&nbsp;I'm using the following code to run the search: <br>Const conQuote = &quot;&quot;&quot;&quot;<br>Dim ctlTextbox As Control<br>Set ctlTextbox = Forms!fdlgNumberChooser!txtInputNumber<br>Setdb = CurrentDb()<br>Set rst = Forms(strFrm).RecordsetClone <br>strCriteria = &quot;VendPartNum = &quot; & conQuote & ctlTextbox & conQuote<br>rst.FindFirst strCriteria<br>If rst.NoMatch Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;**MsgBox code goes here**<br>Else <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Forms(strFrm).Bookmark = rst.Bookmark<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Forms(strFrm).Filter = &quot;VendPartNum = &quot; & conQuote &<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ctlTextbox & conQuote<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Forms(strFrm).FilterOn = True<br>End If<br><br>Thanks for any help you can offer, wdennis

 

[DreewToo]

It appears that the dash is part of your field's format, but not saved as part of the data.&nbsp;&nbsp;Assuming your id field sizes are consistant, ie 6 digits, dash, 2 digits, spliting the string using Left(conQuote,6) & Right(conQuote,2) would match the formatting as it's being saved in the table.<br><br>HTH,<br>drew<br><br>

 

[wdennis]

Hi Drew,<br><br>Hadn't thought of the formatting issue.&nbsp;&nbsp;I played around with your idea and finally did get results with only minor modifications.&nbsp;&nbsp;Here's what works:<br>strCriteria = &quot;VendPartNum = &quot; & conQuote & Left(ctlTextbox, 6) & Right(ctlTextbox, 2) & conQuote<br><br>Thanks so much for your help, wdennis