Form Filter based on text box doesn't work properly when filtering for numbers (1.0, 2.0, 3.0)

[P&MSupply]

Hi,

New user here. Been using access for a number a years and have a database built that stores inventory items. I have a form created that displays all available inventory in real time.



I would like to filter this info based on two text boxes: one for Grade ([txtGrade]) and one for Diameter (txtDiamter]). The data is contained within a table [Inventory] and the two fields are formatted as text for [Grade] and double, standard with 4 decimals for [Diameter]. The diameters range in size from 1.0000 to 3.1875.

The filter based on grade works fine and the one for diameter works for any diameter except for when the filter is for 1.0000 or 2.0000 or 3.0000.

I have used code behind the after update event property on both text boxes as shown below:

Private Sub txtGrade_AfterUpdate()
On Error GoTo txtGrade_AfterUpdate_Err


' The following filter allows the user to change one or both filters and update the form
DoCmd.SetFilter "", "[Grade] Like '*" & txtGrade & "*' And [Diameter] Like '*" & txtDiameter & "*'", ""


txtGrade_AfterUpdate_Exit:
Exit Sub


txtGrade_AfterUpdate_Err:
MsgBox Error$
Resume txtGrade_AfterUpdate_Exit


End Sub

Private Sub txtDiameter_AfterUpdate()
On Error GoTo txtDiameter_AfterUpdate_Err

' The following filter allows the user to change one or both filters and update the form
DoCmd.SetFilter "", "[Diameter] Like '*" & txtDiameter & "*' And [Grade] Like '*" & txtGrade & "*'", ""


txtDiameter_AfterUpdate_Exit:
Exit Sub


txtDiameter_AfterUpdate_Err:
MsgBox Error$
Resume txtDiameter_AfterUpdate_Exit


End Sub



If I type in the numeral 1, in the txtDiameter box, the form filters for all diameters having a "1" in them. If I type in 1. (a 1 plus the decimal point) yields the same result. If I type in 1.0, the form is blank.

Can anyone help me with why this would happen?

Thanks in advance.

[June7]

Why don't you use comboboxes to restrict user input?

Try formatting the diameter parameter: Format([txtGrade], "0.0000")

[Missinglinq]

I suspect that despite the DataType/FieldSize being set to Number/Double, the Access Gnomes are reading 1 and 1. as Text, but when faced with 1.0, are considering it to be Numeric, and the problem with that is that you cannot use the Like operator with Numbers!

Try Formatting it, as June7 suggested, and see what happens. The Format function always returns a String, regardless of the input involved, and this may convince the Gnomes that it is, in fact, a Text.

Of course, unless you have to do math with txtDiameter, you could simply change the Datatype for the underlying Field to Text.

Linq ;0)>

[June7]

When you say the fields are formatted as text - do you mean the field data type? If so, then the original filter criteria should work. If the diameter field is really a number type with format property setting, then my suggestion will likely fail. Calculate a field in form RecordSource that converts the number type value to a string with Format() function then apply my suggestion to that constructed field.

[Missinglinq]

Grade is formatted as Text (this has to be actually mean be 'Datatyped' as Text) and Diameter as a Double.

...the two fields are formatted as text for [Grade] and double, standard with 4 decimals for [Diameter].

And, unless my medication dosage needs to be lowered, as I said before, you can't use a Number (Double) with the 'Like' operator. I really think Diameter should simply be changed to the Text Datatype, so the Like construct would work; can't imagine any math that would need to be done with Diameter, in an inventory app.

Linq ;0)>

[IrogSinta]

Since Diameter is a Number data type, it ignores any ending zeros after the decimal point. To get your LIKE operator to work, change your filter expression to this:

DoCmd.SetFilter "", "[Grade] Like '*" & txtGrade & "*' And Format([Diameter],'#,0000') Like '*" & txtDiameter & "*'"

Ron