Lookup closest match

  • Thread starter mattc66 via AccessMonster.com
  • Start date
M

mattc66 via AccessMonster.com

Hi All,

I have a form with a lookup box. The user types in the part number they want
to find. If they type in the wrong part number I want to be able to find the
next closest item. How would I do this?

This is the current code:

Private Sub LookUpItem_AfterUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.LookUpItem.Value
stLinkCriteria = "[Item]=" & "'" & SID & "'"


'Check table for for item number.
If DCount("Item", "qryICStock_Combined", stLinkCriteria) >= 1 Then
'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

'If the item is not found the below code is run.
Else
'Message box warning that part number doesn't exsist.

msgbox "Item Not Found" _
& SID & " is not a valid part." _
& vbCr & vbCr & "Contact System Admin.", vbExclamation _
, "ITEM NOT FOUND"

End If
Set rsc = Nothing
Me.LookUpItem = Null

End Sub
 
B

Baz

1. Doing a Dcount to find out if the record exists is totally unnecessary,
you can simply do the FindFirst and then check the recordset's NoMatch
property. It's also risky: what happens if the record was added to the
table *after* the form was last queried? Dcount will return 1, but the
FindFirst will fail.
2. You don't need to specify the Value property, it's always the default
property.
3. You probably want something like the following (assuming that the records
are already displayed in [Item] sequence):

Private Sub LookUpItem_AfterUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.LookUpItem
stLinkCriteria = "[Item]=" & "'" & SID & "'"

'Go to record of original Number
rsc.FindFirst stLinkCriteria
If rsc.NoMatch Then
rsc.FindFirst "[Item]>""" & SID & """"
If rsc.NoMatch Then
MsgBox "Nothing found"
Else
MsgBox "Nearest match found"
End If
Else
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
Me.LookUpItem = Null

End Sub
 
M

Marshall Barton

mattc66 said:
I have a form with a lookup box. The user types in the part number they want
to find. If they type in the wrong part number I want to be able to find the
next closest item. How would I do this?

This is the current code:

Private Sub LookUpItem_AfterUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.LookUpItem.Value
stLinkCriteria = "[Item]=" & "'" & SID & "'"


'Check table for for item number.
If DCount("Item", "qryICStock_Combined", stLinkCriteria) >= 1 Then
'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

'If the item is not found the below code is run.
Else
'Message box warning that part number doesn't exsist.

msgbox "Item Not Found" _
& SID & " is not a valid part." _
& vbCr & vbCr & "Contact System Admin.", vbExclamation _
, "ITEM NOT FOUND"

End If
Set rsc = Nothing
Me.LookUpItem = Null

End Sub
Click to expand...


Your question won't mean much until you define "closest" in
terms of the part number data.

If all you want is the first part of the part number to
match, then try using:

stLinkCriteria = "[Item]='" & SID & "*' "
 
G

George Nicholson

MsgBox "Nearest match found"

Actually, that would be "next highest", not necessarily "next closest". But
that would raise the question of what the "closest" value would be for a
text field...

All of this assumes RecordsetClone is Ordered on SID. If it isn't, it won't
return anything resembling correct results.

Set rsc = Me.RecordsetClone

if rsc.EOF then
msgbox "No Records, no nearest value"
Exit Sub
End if

SID = Me.LookUpItem
stLinkCriteria = "[Item] >=" & "'" & SID & "'"
rsc.FindFirst stLinkCriteria
If rsc.NoMatch Then
rsc.MoveLast
msgbox "Nearest (next lowest and last record in recordset) value
found"
Me.Bookmark = rsc.Bookmark
Else
If rsc!Item = SID then
Msgbox "Exact Match found"
Me.Bookmark = rsc.Bookmark
Else
' **Now, what to do?
' "Next Highest" value is not necessarily "next closest" value
If we are prepared to accept "next highest" Then
Msgbox "Next Highest value found"
Me.Bookmark = rsc.Bookmark
Else
' if SID/Item is a pure number, we could do something like
this to determine the "closest" value
lngTempSID = clng(rsc!Item)
' Check the previous record (next lowest) for comparison
rsc.MovePrevious
If rsc.BOF then
' No previous record. "next larger" wins by default (and
is the 1st record in the recordset)
rsc.MoveNext
Msgbox "Nearest (greater) value found"
Me.Bookmark = rsc.Bookmark
Else
' compare "next smaller" and "next larger" to SID to
determine which is closer
' however, clng won't coerce and subtraction won't work
unless SID has only numeric characters
' I don't have a simple alternative on hand if alpha
chars are involved
If abs(clng(SID) - clng(rsc!Item))< abs(lngTempSID -
clng(SID) Then
' next smaller wins
msgbox "Nearest (lesser) value found
Me.Bookmark = rsc.Bookmark
Else
' Next largest was closer. go back.
rsc.MoveNext
Msgbox "Nearest (greater) value found"
Me.Bookmark = rsc.Bookmark
End if "If previous SID is Closer than Next SID
End if 'If rsc.BOF then
End If 'If we are good with "next highest" Then
End if 'If rsc!Item = SID then
End if 'If rsc.NoMatch Then

set rsc = nothing

HTH


Baz said:
1. Doing a Dcount to find out if the record exists is totally unnecessary,
you can simply do the FindFirst and then check the recordset's NoMatch
property. It's also risky: what happens if the record was added to the
table *after* the form was last queried? Dcount will return 1, but the
FindFirst will fail.
2. You don't need to specify the Value property, it's always the default
property.
3. You probably want something like the following (assuming that the
records
are already displayed in [Item] sequence):

Private Sub LookUpItem_AfterUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.LookUpItem
stLinkCriteria = "[Item]=" & "'" & SID & "'"

'Go to record of original Number
rsc.FindFirst stLinkCriteria
If rsc.NoMatch Then
rsc.FindFirst "[Item]>""" & SID & """"
If rsc.NoMatch Then
MsgBox "Nothing found"
Else
MsgBox "Nearest match found"
End If
Else
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
Me.LookUpItem = Null

End Sub

mattc66 via AccessMonster.com said:
Hi All,

I have a form with a lookup box. The user types in the part number they want
to find. If they type in the wrong part number I want to be able to find the
next closest item. How would I do this?

This is the current code:

Private Sub LookUpItem_AfterUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.LookUpItem.Value
stLinkCriteria = "[Item]=" & "'" & SID & "'"


'Check table for for item number.
If DCount("Item", "qryICStock_Combined", stLinkCriteria) >= 1 Then
'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

'If the item is not found the below code is run.
Else
'Message box warning that part number doesn't exsist.

msgbox "Item Not Found" _
& SID & " is not a valid part." _
& vbCr & vbCr & "Contact System Admin.", vbExclamation _
, "ITEM NOT FOUND"

End If
Set rsc = Nothing
Me.LookUpItem = Null

End Sub
Click to expand...
Click to expand...
 
M

mattc66 via AccessMonster.com

Yes I would like to find the next closest match that resembles what was typed.


George said:
Actually, that would be "next highest", not necessarily "next closest". But
that would raise the question of what the "closest" value would be for a
text field...

All of this assumes RecordsetClone is Ordered on SID. If it isn't, it won't
return anything resembling correct results.

Set rsc = Me.RecordsetClone

if rsc.EOF then
msgbox "No Records, no nearest value"
Exit Sub
End if

SID = Me.LookUpItem
stLinkCriteria = "[Item] >=" & "'" & SID & "'"
rsc.FindFirst stLinkCriteria
If rsc.NoMatch Then
rsc.MoveLast
msgbox "Nearest (next lowest and last record in recordset) value
found"
Me.Bookmark = rsc.Bookmark
Else
If rsc!Item = SID then
Msgbox "Exact Match found"
Me.Bookmark = rsc.Bookmark
Else
' **Now, what to do?
' "Next Highest" value is not necessarily "next closest" value
If we are prepared to accept "next highest" Then
Msgbox "Next Highest value found"
Me.Bookmark = rsc.Bookmark
Else
' if SID/Item is a pure number, we could do something like
this to determine the "closest" value
lngTempSID = clng(rsc!Item)
' Check the previous record (next lowest) for comparison
rsc.MovePrevious
If rsc.BOF then
' No previous record. "next larger" wins by default (and
is the 1st record in the recordset)
rsc.MoveNext
Msgbox "Nearest (greater) value found"
Me.Bookmark = rsc.Bookmark
Else
' compare "next smaller" and "next larger" to SID to
determine which is closer
' however, clng won't coerce and subtraction won't work
unless SID has only numeric characters
' I don't have a simple alternative on hand if alpha
chars are involved
If abs(clng(SID) - clng(rsc!Item))< abs(lngTempSID -
clng(SID) Then
' next smaller wins
msgbox "Nearest (lesser) value found
Me.Bookmark = rsc.Bookmark
Else
' Next largest was closer. go back.
rsc.MoveNext
Msgbox "Nearest (greater) value found"
Me.Bookmark = rsc.Bookmark
End if "If previous SID is Closer than Next SID
End if 'If rsc.BOF then
End If 'If we are good with "next highest" Then
End if 'If rsc!Item = SID then
End if 'If rsc.NoMatch Then

set rsc = nothing

HTH
1. Doing a Dcount to find out if the record exists is totally unnecessary,
you can simply do the FindFirst and then check the recordset's NoMatch
Click to expand...
[quoted text clipped - 74 lines]
Click to expand...
 
M

mattc66 via AccessMonster.com

Syntax error on this line:
If abs(clng(SID) - clng(rsc!Item))< abs(lngTempSID - clng
(SID) Then

George said:
Actually, that would be "next highest", not necessarily "next closest". But
that would raise the question of what the "closest" value would be for a
text field...

All of this assumes RecordsetClone is Ordered on SID. If it isn't, it won't
return anything resembling correct results.

Set rsc = Me.RecordsetClone

if rsc.EOF then
msgbox "No Records, no nearest value"
Exit Sub
End if

SID = Me.LookUpItem
stLinkCriteria = "[Item] >=" & "'" & SID & "'"
rsc.FindFirst stLinkCriteria
If rsc.NoMatch Then
rsc.MoveLast
msgbox "Nearest (next lowest and last record in recordset) value
found"
Me.Bookmark = rsc.Bookmark
Else
If rsc!Item = SID then
Msgbox "Exact Match found"
Me.Bookmark = rsc.Bookmark
Else
' **Now, what to do?
' "Next Highest" value is not necessarily "next closest" value
If we are prepared to accept "next highest" Then
Msgbox "Next Highest value found"
Me.Bookmark = rsc.Bookmark
Else
' if SID/Item is a pure number, we could do something like
this to determine the "closest" value
lngTempSID = clng(rsc!Item)
' Check the previous record (next lowest) for comparison
rsc.MovePrevious
If rsc.BOF then
' No previous record. "next larger" wins by default (and
is the 1st record in the recordset)
rsc.MoveNext
Msgbox "Nearest (greater) value found"
Me.Bookmark = rsc.Bookmark
Else
' compare "next smaller" and "next larger" to SID to
determine which is closer
' however, clng won't coerce and subtraction won't work
unless SID has only numeric characters
' I don't have a simple alternative on hand if alpha
chars are involved
If abs(clng(SID) - clng(rsc!Item))< abs(lngTempSID -
clng(SID) Then
' next smaller wins
msgbox "Nearest (lesser) value found
Me.Bookmark = rsc.Bookmark
Else
' Next largest was closer. go back.
rsc.MoveNext
Msgbox "Nearest (greater) value found"
Me.Bookmark = rsc.Bookmark
End if "If previous SID is Closer than Next SID
End if 'If rsc.BOF then
End If 'If we are good with "next highest" Then
End if 'If rsc!Item = SID then
End if 'If rsc.NoMatch Then

set rsc = nothing

HTH
1. Doing a Dcount to find out if the record exists is totally unnecessary,
you can simply do the FindFirst and then check the recordset's NoMatch
Click to expand...
[quoted text clipped - 74 lines]
Click to expand...
 
J

John W. Vinson

Yes I would like to find the next closest match that resembles what was typed.
Click to expand...

Again... what's your definition of "close"?

Is A123B "close" to A12B? It's just missing one character.
Is A10000 "close" to A09999? Certainly not, it differs in five of the six
characters. Or certainly so, it's numerically off by just 1.


John W. Vinson [MVP]
 
B

Baz

George Nicholson said:
Actually, that would be "next highest", not necessarily "next closest".
Click to expand...

Y'know, I think I knew that. But given that the OP didn't define "closest"
but did state "next", it's about the only reasonable conclusion to draw.
Otherwise "next closest" could mean anything, only the OP knows (or maybe
even he hasn't thought about what he means by "closest").
 
D

Dale Fye

Please excuse my ignorance.

I've been using this forum for almost 10 years, and only recently have I
started seeing the term "OP" show up. Anybody want to enlighten me?
 
J

John Spencer

Original Poster ?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
M

mattc66 via AccessMonster.com

I will give you an example:

Our part numbers are something like 9900-100.

If a user types 9900-200 but that doesn't exsist, but 9900-100 does or 9900-
201 does I'd like it to go to 9900-201. The bottom line is it get the user in
the ball park range. From that point they could scrol up or down from there.

Thanks for all your help.
 
J

John W. Vinson

I will give you an example:

Our part numbers are something like 9900-100.

If a user types 9900-200 but that doesn't exsist, but 9900-100 does or 9900-
201 does I'd like it to go to 9900-201. The bottom line is it get the user in
the ball park range. From that point they could scrol up or down from there.

Thanks for all your help.
Click to expand...

How about

LIKE Left([Enter part number:], InStr([Enter part number:], "-")) & "*"


John W. Vinson [MVP]
 
B

Baz

Correct.

John Spencer said:
Original Poster ?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AfterUpdate help 5
Problem With Code 2
rebuild the cod to prevent dublicates in more than one record 8
Drop Down doesn't populate form 0
Data Type Mismatch in criteria 2
Run-time error 3075 3
Preventing Duplicates from being entered 7
Find the closest Matched Record 1

Top