DCount LookUp Issue

[mattc66 via AccessMonster.com]

I have the below code setup on a cboBox. It works great until the user types
in a Order Number with a dash. The OrderNum fld is Text. You can see in the
cboBox the number but when it's selected it doesn't move to this record.
However all other numbers work as expected.

Example: 2010230 works. 2010230-01 doesn't. Could it be in my code that is
causing this?


Private Sub LookUpOrder_AfterUpdate()
On Error GoTo Err_LookUpOrder_Click

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Me.Filter = ""

Set rsc = Me.RecordsetClone

SID = Me.LookUpOrder.Value
stLinkCriteria = "[OrderNum]=" & "'" & SID & "'"

'Check table for for item number.
If Me.LookUpOrder.ListIndex = -1 Then
'Data is not in the list
If DCount("OrderNum", "tblShip", stLinkCriteria) >= 1 Then

'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

Me.LookUpOrder.SetFocus
Else
'Message box warning that part number doesn't exsist.

Msgbox "Order Number" _
& SID & " is not valid order number," _
& vbCr & vbCr & "check the number and try again." _
& vbCr & vbCr & "If the order is still not found" _
& vbCr & vbCr & "contact order entry.", vbExclamation _
, "ORDER NOT FOUND"

Me.LookUpOrder.SetFocus
Exit Sub
End If
End If

If Me.LookUpOrder.Value = True Then
If DCount("OrderNum", "tblShip", stLinkCriteria) = 0 Then

DoCmd.GoToRecord , , acNewRec
Me.OrderNum = Me.LookUpOrder.Column(0)
Me.CustNo = Me.LookUpOrder.Column(1)
Me.CustomerPO = Me.LookUpOrder.Column(3)
Me.ShipToName = Me.LookUpOrder.Column(5)
Me.ShipToAddress1 = Me.LookUpOrder.Column(6)
Me.ShipToAddress2 = Me.LookUpOrder.Column(7)
Me.ShipToCity = Me.LookUpOrder.Column(8)
Me.ShipToState = Me.LookUpOrder.Column(9)
Me.ShipToZip = Me.LookUpOrder.Column(10)
Me.Phone = Me.LookUpOrder.Column(11)
Me.ContactName = Me.LookUpOrder.Column(12)
Me.cboCarrier = Me.LookUpOrder.Column(13)
Me.BILLOPT = Me.LookUpOrder.Column(14)
Me.COMPANY = Me.LookUpOrder.Column(19)
Me.Refresh


'If the item is not found the below code is run.
Me.TrackingNum.SetFocus
Else
If DCount("OrderNum", "tblShip", stLinkCriteria) >= 1 Then

'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

Exit_LookUpOrder_Click:
Exit Sub

Err_LookUpOrder_Click:
Msgbox Err.Description
Resume Exit_LookUpOrder_Click

End Sub

 

[Allen Browne]

This is a flaw in Access.

Dashes are handled inconsistently, depending on whether the field is indexed
or not.

Microsoft's reference:
http://support.microsoft.com/kb/271661/en-us

 

[mattc66 via AccessMonster.com]

I am running this database on a Windows 2003 terminal server session. I have
newer versions of all the suggested RESOLUTIONs. So now what do I do to fix
this one?

This is a flaw in Access.

Dashes are handled inconsistently, depending on whether the field is indexed
or not.

Microsoft's reference:
http://support.microsoft.com/kb/271661/en-us

I have the below code setup on a cboBox. It works great until the user
types

[quoted text clipped - 82 lines]

End Sub

 

[Allen Browne]

Microsoft doesn't have a fix for this. And since we have now had the problem
for 7 years, don't hold your breath waiting for one.

The solution might be to avoid using dashes in the the field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am running this database on a Windows 2003 terminal server session. I
have
newer versions of all the suggested RESOLUTIONs. So now what do I do to
fix
this one?

This is a flaw in Access.

Dashes are handled inconsistently, depending on whether the field is
indexed
or not.

Microsoft's reference:
http://support.microsoft.com/kb/271661/en-us

I have the below code setup on a cboBox. It works great until the user
types

[quoted text clipped - 82 lines]

End Sub

 

[mattc66 via AccessMonster.com]

That is easier said then done when the data that feeds my database is from an
accounting package that assign's backorders with -00 numbers.

Matt

Microsoft doesn't have a fix for this. And since we have now had the problem
for 7 years, don't hold your breath waiting for one.

The solution might be to avoid using dashes in the the field.

I am running this database on a Windows 2003 terminal server session. I
have

[quoted text clipped - 16 lines]

 

[Allen Browne]

Matt, the KB refers to the Like operator.
I can't recall if it applies only to the Like operator, but you might like
to test it.

If you can't avoid the the issue, you could search/match on the field:
Replace([MyField], "-", "")
and drop the dash from the criterion.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That is easier said then done when the data that feeds my database is from
an
accounting package that assign's backorders with -00 numbers.

Matt

Microsoft doesn't have a fix for this. And since we have now had the
problem
for 7 years, don't hold your breath waiting for one.

The solution might be to avoid using dashes in the the field.

I am running this database on a Windows 2003 terminal server session. I
have

[quoted text clipped - 16 lines]

End Sub