Results 1 to 11 of 11
  1. #1
    normie is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    53

    Filter by 3rd digit

    I am trying to filter my query by the third digit in the following ( "P" & "B")



    20P081A

    01B020A

    I have tried

    Like:P

    Like:**P

    no results

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Mid function?
    Where mid(fieldname,3,1) = "p"

  3. #3
    normie is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    53
    Click image for larger version. 

Name:	Capture.JPG 
Views:	5 
Size:	34.6 KB 
ID:	28770
    not sure what I ma doing wrong

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Tick the box

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Filter by 3rd digit

    Oh and just realised
    Remove the " " from around location.

    And is 20p081a a location?

    If not use the productID field

    Drag down productID, place the mid criteria in that column
    Last edited by andy49; 05-21-2017 at 01:52 PM. Reason: Extension

  6. #6
    normie is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    53
    Click image for larger version. 

Name:	Capture.JPG 
Views:	5 
Size:	25.2 KB 
ID:	28772

    I am getting no results

  7. #7
    normie is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    53
    Yes it is a location

  8. #8
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Is it location field which has those codes?

  9. #9
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Mid([location],3,1) = "p"

  10. #10
    normie is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    53
    They are actually locations in a warehouse
    P being a pick from location
    B being a reserve location

    I need to query both separately from the master inventory file

  11. #11
    normie is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    53
    Got It ! I was missing [] around the field name thanks for your help

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

Similar Threads

  1. How to set maximum 8 digit number in default?
    By Jeremy Sng in forum Access
    Replies: 8
    Last Post: 03-14-2017, 09:02 AM
  2. Replies: 6
    Last Post: 08-28-2014, 12:33 PM
  3. dates - 2 digit year
    By RCDAwebmaster in forum Queries
    Replies: 5
    Last Post: 05-20-2014, 08:14 AM
  4. Combo box is recognized as digit format
    By Gealeks in forum Forms
    Replies: 8
    Last Post: 11-27-2013, 03:08 PM
  5. display single digit
    By appiades in forum Access
    Replies: 2
    Last Post: 07-05-2010, 05:36 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