Results 1 to 11 of 11
  1. #1
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125

    Filter form by value including characters

    I have two filters for my quote list form:-

    1. Status
    2. Raised By



    I would like to introduce a third "Quote No." When the checkbox is used to filter the Status for e.g. "Lost" only records with the value of "Lost" are displayed, however, I need to a way to filter the list according to it's quote number. I have a revision system in place which increments the revision with a letter after the quote no. Ideally I am trying to ask if it's possible for a box to allow me to type "1048" for e.g. which would return two records only, 1) "1048a" and 2) "1048".

    Click image for larger version. 

Name:	QuoteList.jpg 
Views:	20 
Size:	96.0 KB 
ID:	45751

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    since you have added a revision letter, it is text, so use a criteria

    Like "1048*"

    indexing will still work since there is not an initial *

    A better method would be to keep your revision letter in a separate field and keep your quote number numeric - numbers process faster than text and take up less space in the file. An integer takes 2 bytes, a string 2 bytes plus 2 bytes per character so in your example 2 bytes v 12 bytes

  3. #3
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    Be wary though, 'Like "1048*" will return all 1048 revisions but also any quote which number starts with 1048 (for example 10480, 10481...). This is pretty useful to allow the user to search for data without entering the whole ID, but can be troublesome if that's not the intention. I don't recall any wildcard for a single or none alphabet characters but if you wanna make the user insert the whole ID and search just for that quote and all it's revisions you can go for something like this
    Code:
    where QuoteNo="1048" or QuoteNo like "1048[a-z]"
    , just substitute 1048 with your textbox value.

    Anyway, as Ajax said it'd be cleaner to keep the revision in a separate field. If 'Quote. no' is your PK just make it a composite PK with both fields QuoteNo and revision.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You would use ?

    Like ‘1234?’

    Would return 1234a but not 12345

    But would not return 1234ab for which you would need to use

    Like ‘1234?*’

    Another alternative would be to use the Val function to take the number part

    Val(code)=1234

    Note no ‘ as now a numeric comparison

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I tested LIKE "1234?".

    It returned both 1234a and 12345.
    Last edited by June7; 07-14-2021 at 11:25 AM.
    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.

  6. #6
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    Here's what I store in the table:
    Click image for larger version. 

Name:	tblProvisionalQuotes.JPG 
Views:	16 
Size:	16.3 KB 
ID:	45754

    Here's what the query does:

    Click image for larger version. 

Name:	qryQuoteList.JPG 
Views:	15 
Size:	31.2 KB 
ID:	45755

    The "QR" from qryQuoteList is what I have on the form which will return the quote no. including the revision letter if it's been revised.

    The dream is that if the user searches 1048 it returns 1048, 1048a & 1048b namely.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It return both 1234a and 12345
    hmm, was done on the fly. ? is supposed to match any alphabetic character, I don't think of numeric characters being alphabetic but there you go.

    would have to use

    Like '1234[a-z]'

  8. #8
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    Quote Originally Posted by Blings View Post
    Here's what I store in the table:
    Click image for larger version. 

Name:	tblProvisionalQuotes.JPG 
Views:	16 
Size:	16.3 KB 
ID:	45754

    Here's what the query does:

    Click image for larger version. 

Name:	qryQuoteList.JPG 
Views:	15 
Size:	31.2 KB 
ID:	45755

    The "QR" from qryQuoteList is what I have on the form which will return the quote no. including the revision letter if it's been revised.

    The dream is that if the user searches 1048 it returns 1048, 1048a & 1048b namely.
    Well, so you DO have separate fields. If you set that query as the form source you can always filter by the QuoteNo even if hidden. That'll return every record that fits the quote number regardless the revision.

    If you don't want to change anything, use the criteria I stated above. Like '1048[a-z]' will return every 1048 and any revision letter (assuming you never go double character for a revision, like revision aa) but not the number without any revision mark, so you need to include the number too. qr like '1048' or qr like '1048[a-z]' should work.

    And yes, ? states only alphabet characters but works for number too, weirdly.

  9. #9
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    Thanks for the help Lhoj, Ajax & June7. I spoke with mike60smart now who helped me implement this into the form which is working! Thank you very much.

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Greg
    To help others it is usual for the Code that solved the problem to be updated.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    Quote Originally Posted by mike60smart View Post
    Hi Greg
    To help others it is usual for the Code that solved the problem to be updated.
    Noted, with thanks.

    An unbound textbox on the form with the following on the Event: After Update was the solution.

    Code:
    20    Select Case Me.QN.Value
    
          Case "All"
    30            Me.FilterOn = False
    40    Case Else
    50            Me.Filter = "[QuoteNo] = " & Me.QN
    60            Me.FilterOn = True
    
    
    70    End Select

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

Similar Threads

  1. Replies: 2
    Last Post: 11-11-2018, 03:47 AM
  2. Replies: 5
    Last Post: 11-30-2014, 08:12 PM
  3. Filter fields including records with null
    By Ruegen in forum Programming
    Replies: 18
    Last Post: 01-28-2014, 11:23 PM
  4. Replies: 1
    Last Post: 01-21-2014, 11:35 AM
  5. Replies: 1
    Last Post: 04-17-2013, 04:38 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