Results 1 to 7 of 7
  1. #1
    TOPSie is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    101

    BETWEEN text values

    I have a report with the following criteria in the RecordSource



    ((tblPurchaseAccounts.SupplierName) Between [Forms]![fdlgPurchaseLedgerSearch]![cboSupplierFrom] And [Forms]![fdlgPurchaseLedgerSearch]![cboSupplierTo])

    the combo boxes (From and To) contain a list of Supplier Names. The Supplier names are all text, but many contain spaces and punctuation characters like & ' . etc

    If I run the report with From and To containing the same value and that value is pure Alphabetic then the reports works.

    If I run the report with From and To containing some of the "odd" names then there is no output and no error message.

    If I was doing and OpenRecordSet I would probably try and encase the fields in quotes, but how do you change a Report RecordSource to do this??

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Between works as an ascending value check. This works really well for dates and numbers. Text however will give you some interesting results, as you have discovered.
    If you simply sort your table on the field you are using you will see how it thinks they are ordered when you use the between criteria.

    How are you currently setting the record source - what is the code?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    TOPSie is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    101
    The Between "fields" are taken from the selected value of two combo boxes - each has the same Row Source viz
    SELECT tblPurchaseAccounts.SupplierNameFROM tblPurchaseAccounts
    ORDER BY tblPurchaseAccounts.SupplierName;

    I have just sorted the underlying table (tblPurchaseAccounts) by the SupplierName field, but I get the same issue.

    It seems to work for all the values starting with A but as soon as I include the first B entry - which is "B.C.T. Ltd" then I get no report (and no warning)

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Apostrophes in names can play havoc with your design so there's that. One way around it would be to simply remove them with an update query. If you still need to see them in some sort of output, then having a display field (O'Brien) and a search field (OBrien) would allow you to work with the data but still display it as it really is.
    Then there's the problem of searching on text using Between. That seems like you have no numeric index such as an autonumber primary field. You should be using Between on numbers, not text, like Between 2 and 4

    CompanyIDpk CompanyName
    1 ACME
    2 Widgets Inc.
    3 Amazon
    4 Best Buy
    Last edited by Micron; 01-04-2022 at 10:32 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    TOPSie is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    101
    Thank you.
    I am coming to the conclusion that using BETWEEN ... AND on a text field leads to results which are "undefined" (unless the text is pure alphabetic)

    My table does have a numeric key field like your example, and like your example the numbers in ascending order do not correspond to the alphabetic order of the "name" field.

    So if I said between 1 and 3 I would not get all beginning with A as Widgets in number 2

    So I think it might be back to the user to try and see if his requirements can be met is some other way.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Then add a sort order field to the table and use that? Don't use 1,2,3... Use 10,20,30 or similar.
    Discussing issues with users is a good thing most of the time but sometimes they can be unrealistic or come up with wishes where the payback falls far short of the effort required.

    EDIT - FAYT (find as you type) might be an option, depending on what user is trying to do. If you're sorting the list by alpha and wanting to see all that begin with a set of characters, you type AC and the list should automatically filter on the input. Since I wouldn't try Between with text, I'm not sure what exactly the user process is here. I made that suggestion on the assumption that you have some sort of sorting arrangement and I pick Acme at the top of the list and then Amazon at the bottom, so I would in fact get Widgets Inc in my report but not Best Buy.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The other option would be to simply use the first letter of the select names, so Acme Ltd and Design Co simple became

    Between "A*" and "D*"

    As the criteria. This works and would give you a good initial hit at the names.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 6
    Last Post: 05-29-2015, 10:21 AM
  2. Replies: 19
    Last Post: 09-09-2014, 01:36 AM
  3. Replies: 2
    Last Post: 07-09-2014, 12:43 PM
  4. Replies: 5
    Last Post: 04-01-2013, 11:49 AM
  5. need to get a sum of values to a text box
    By cowboy in forum Programming
    Replies: 15
    Last Post: 02-28-2012, 05:45 AM

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