Results 1 to 5 of 5
  1. #1
    virencm is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Jun 2019
    Posts
    7

    Query multiple ranges

    Hi,

    I need help querying a table using multiple range options.

    please take a look at the attachment. I was unable to attach the database as it was beyond the allowable size.so i uploaded the table and a picture of the form.



    On the main form, I have a table that i wish to query for chemical compositions. so I want to enter a range for Fe and Cr and Ni and the table should filter to the range of values being updated by the user. i.e for FE between 95 and 98; for Ni between 3 and 7 etc and hitting "search" it should filter the table.

    I need the query to work even if i don't put values in some of the ranges.

    Can someone please help with this query? let me know if you require further information.

    Cheers
    V
    Attached Thumbnails Attached Thumbnails Main form .PNG  
    Attached Files Attached Files

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    best to build this in vba

    Code:
    dim filterStr as string
    
    if not isnull(FEStart) then
        filterstr="Fe BETWEEN " & FEStart & " AND " & FEEnd
    end if
    
    if not isnull(NIStart) then
       if filterStr<>"" then filterStr=filterStr & " AND "
       filterstr="Ni BETWEEN " & NiStart & " AND " & NiEnd
    end if
    
    if not isnull(NIStart) then
       if filterStr<>"" then filterStr=filterStr & " AND "
       filterstr="Cr BETWEEN " & CrStart & " AND " & CrEnd
    end if
    
    me.filter=filterstr
    me.filteron=true
    your db only contains tables so cannot determine the structure of your form, but if the table is in a subform then change 'me.' to 'subformcontrolname.form.' Similarly, don't know the names of your search fields so change to suit

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Because the "Element values" are Text type fields, you will need to delimit the variables with single quotes in Ajax's code.


    To attach files, see the top header "How to attach files" (in the green)


    Also, you might consider modifying your table design. Right now your design is what is termed "Committing spreadsheet": it is a spreadsheet that has been imported into an Access table.

    You have "Fe, C, Ni, etc", as field names but they are actually data. Then you have the actual values (mole weights?).

    Consider:
    tblMaterials (or maybe Samples)
    ===========================
    MaterialID_PK (Autonumber)
    Materials (Text)
    Spec (Text)
    LOCATION (Text)
    UNS (Text)
    OTHER_SPECS (Text)
    MaterialType (Text) {"TYPE" is a reserved word}
    OTHER (Text)
    NOTES (Text)
    SOURCE
    MajorElements


    tblElementNames
    ================
    ElementNameID_PK (Autonumber)
    ElementName


    tblElements
    ================
    ElementID_PK (Autonumber)
    MaterialID_FK (Number - Long {Foreign Key to tblMaterials)
    ElementID_FK (Number - Long {Foreign Key to tblElementNames)
    MolWeight (Number - Double)

  4. #4
    virencm is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Jun 2019
    Posts
    7
    Thanks Ajax and SSanfu.

    This is an inherited database and i just want to make use of whats there at the moment.

    Can you please help with "you will need to delimit the variables with single quotes "

    I don't understand what you mean?

    Appreciate your help.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Regret I did not look at your table design, just assumed that numbers were stored as numbers. Steve, being more thorough, looked at the table design and points out that the numbers are stored as text. Text is treated differently to numbers when ordering which is required when using between.

    with numbers, you do not need single quotes - e.g. myNumberfield=1
    with text you do need single quotes - myTextfield='A'

    so this
    filterstr="Fe BETWEEN " & FEStart & " AND " & FEEnd
    becomes
    filterstr="Fe BETWEEN '" & FEStart & "' AND '" & FEEnd & "'"

    But that is not the end of your problems because text such as

    '1.3'
    '10.15'
    '1.57'

    will all be between '1.0' and '2.0'

    so now you need to format all the numbers as text with the same number of characters

    '01.30'
    '10.15'
    '01.57'

    between '01.00' and '02.00'

    be much easier just to change your text fields to numbers

    but even then you have problems because of your excelitus. not all your numbers are numbers - you have <1 and * characters in there. Even they will not be recognised as numbers if this was filtered in excel

    Access is not a bigger excel, it is a completely different animal. You need to apply the principles of normalisation and good database design if you want any to have any chance of success

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

Similar Threads

  1. Part Number Log with Multiple Ranges
    By abublitz in forum Database Design
    Replies: 9
    Last Post: 05-17-2017, 09:52 AM
  2. Query by date for multiple ranges
    By Xarkath in forum Queries
    Replies: 2
    Last Post: 06-11-2013, 01:47 PM
  3. Replies: 1
    Last Post: 04-05-2013, 01:31 PM
  4. Multiple date ranges on report
    By AAA in forum Reports
    Replies: 1
    Last Post: 06-18-2012, 10:18 PM
  5. Replies: 1
    Last Post: 01-10-2012, 10:12 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