The value you entered isn't valid for this field

[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

[June7]

Is the ItemNum field a text or numeric datatype? If you need to use alpha characters the field must be text datatype.

What datatype are Requestor and MaterialThickness?

Just as the date value requires # delimiters, string values require apostrophes.

[jsdanie2]

The ItemNum field is a text datatype. Requestor field is a text datatype. Material Thickness is a numeric datatype.

ItemNum and Requestor are setup the exact same way in the form and the table. ItemNum will search numerics just fine, Requestor will search alpha characters just fine. But if I try to search for alpha characters in the ItemNum or numeric characters in Requestor it gives me the error message The value you entered isn't valid for this field.

Also the material thickness search works just fine.

[June7]

I am baffled that any of the text criteria work.

Here are examples from my project building an SQL filter with text data.

Note use of apostrophe where you have quote mark.

strSQL = strSQL & IIf(IsNull(.cbxTest), "", IIf(strSQL = "", "", " AND ") & "Tests Like '*" & .cbxTest & "*'")

This one not using a variable but does show inclusion of the apostrophes.

"AND (Tests Not Like '*80*' And Tests Not Like '*90*' And Not IsNull(Tests)) " & _

[jsdanie2]

SORRY to waste you guys time, it was a newbie mistake. The whole time I was looking over the fact that I had a general number format on the search criteria text field for ItemNum.

Thanks again for your help.

Scott