Results 1 to 6 of 6
  1. #1
    P&MSupply is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    1

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

    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.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,045
    Why don't you use comboboxes to restrict user input?

    Try formatting the diameter parameter: Format([txtGrade], "0.0000")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,998
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,045
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,998
    Grade is formatted as Text (this has to be actually mean be 'Datatyped' as Text) and Diameter as a Double.

    Quote Originally Posted by P&MSupply View Post

    ...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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    IrogSinta is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    103
    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:
    Code:
    DoCmd.SetFilter "", "[Grade] Like '*" & txtGrade & "*' And Format([Diameter],'#,0000') Like '*" & txtDiameter & "*'"
    Ron

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Append doesn't work properly
    By fluppe in forum Access
    Replies: 3
    Last Post: 10-17-2014, 12:50 AM
  2. Onload event doesn't work for filter
    By tagteam in forum Forms
    Replies: 2
    Last Post: 02-12-2014, 11:54 AM
  3. Why doesn't RTF text display properly in report?
    By kenton.l.sparks@gmail.com in forum Access
    Replies: 1
    Last Post: 05-02-2012, 07:30 PM
  4. Combo box doesn't work properly
    By joe1987 in forum Forms
    Replies: 9
    Last Post: 11-10-2011, 04:34 PM
  5. Filter on subform doesn't work
    By Edwardo in forum Programming
    Replies: 6
    Last Post: 11-29-2010, 08:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums