The value you entered is not valid for this field?????? Please Help!!!

[jsdanie2]

I have created a form (used Allen Browne’s search criteria form) that has unbound text boxes used for the search criteria (Requestor, Item Num,Date, Material Thickness. With the search criteria you can fill in one, two, three, …., or all of the unbound boxes. It then list the findings in some bound text boxes I have on the same form ( Requestor, ItemNum, Date, Shear #, Reason Code, Quantity Material Thickness, Part Complete).

The Fields in the table are: ( Requestor, ItemNum, Date, Shear #, Reason Code, Quantity Material Thickness, Part Complete. The format of those respective fields are Text, Text, Date/Time, Text, Number, Number, Text, Text. The field length is set to 50 and there are no default values.

For the field ItemNum it is sometime necessary to name an item in alpha characters instead of numeric. When I use my filter command on my form with ItemNum criteria in numeric characters it works perfect. But when I use alpha characters it comes up with the error message “The value you enter isn’t valid for this field”.

Thanks in advance!

Below is the code used for the form:

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "#mm\/dd\/yyyy#"

If Not IsNull(Me.txtFilterRequestor) Then
strWhere = strWhere & "([Requestor] Like ""*" & Me.txtFilterRequestor & "*"") AND "
End If

If Not IsNull(Me.txtFilterItemNum) Then
strWhere = strWhere & "([ItemNum] Like ""*" & Me.txtFilterItemNum & "*"") AND "
End If

If Not IsNull(Me.txtFilterMaterialThickness) Then
strWhere = strWhere & "([MaterialThickness] = " & Me.txtFilterMaterialThickness & ") AND "
End If

If Not IsNull(Me.txtFilterFrom) Then
strWhere = strWhere & "([Date] >= " & Format(Me.txtFilterFrom, conJetDate) & ") AND "
End If



lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If




End Sub

Private Sub cmdReset_Click()
Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

Me.FilterOn = False
End Sub



Private Sub Form_BeforeInsert(Cancel As Integer)

Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)

End Sub

 

[Johnny Drama]

Sounds like ItemNum has a data type of number, but you're using numbers and text. Try changing the data type to text.