Finding Closest Match

[John]

I have been hunting for this and can't seem to find it. I would like to put
a command button on my form that finds the closest match to what the user
types in even if it's not found as an exact match. The field I am finding is
a text field, 10 charicters long and has a right justified numeric string in
it. The field is ActID and a sample data would be " 20100" (five leading
spaces). If the user types in "20000" (which doesn't exist) I would like the
bookmark to stop on "20100", the closest match.

 

[Marshall Barton]

I have been hunting for this and can't seem to find it. I would like to put
a command button on my form that finds the closest match to what the user
types in even if it's not found as an exact match. The field I am finding is
a text field, 10 charicters long and has a right justified numeric string in
it. The field is ActID and a sample data would be " 20100" (five leading
spaces). If the user types in "20000" (which doesn't exist) I would like the
bookmark to stop on "20100", the closest match.

Need a better definition of "closest".

You can deal with your example using code like:

With Me.RecordsetClone
,FindFirst "Val(Mid(ActID, 6) >= " & Me.thetextbox
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

You should also verify that the ActID field *always* has the
5 leading spaces.

 

[John]

Thanks Marshall... This was not exactly it, but close enough so I could
figure it out. The trick was to convert the ActID to a value, then use the
find " >= ", so it finds the next value greater than the one the user types
in...