Results 1 to 7 of 7
  1. #1
    crimedog is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Posts
    141

    I have Text ID I need to see if it is in a String

    Two separate tables: One is Equipment with ID (Primary key) and Serial (text) - 2nd Table Orders with OrderID and Note (text)
    I would like to create a query that returns the Equipment ID OrderID's where the note has the Serial somewhere - Note says Pick Up Serial: XXXX


    Can I start a query - show both tables and in the Criteria (under note) Like "Pick Up"&"*[Serial]*"
    So IF the [Serial] is Listed in the [Note] - then the [ID] and [OrderID] show on the query

  2. #2
    crimedog is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Posts
    141
    Is there a way to use Dlookup? like Dlookup("[OrderID]","Orders","[Note]="&"Pick Up"&"*[Serial]*")

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    <dupe post on my part, ignore>

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Is the Note always in this format "Pick Up Serial: XXXX"? Is each Serial number in your example XXXX in the Note have same length in each record that has this in the Note?

    So if I understand your OP, those 2 tables do not have a linking value so you need to get the Serial number from the Note int he 2nd table, to link to the Serial number in the first table?

  5. #5
    crimedog is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Posts
    141
    correct
    Always starts with Pick up - but the number of characters are not standard - sometimes the entry person adds something between pick up and serial

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try to use the built in InStr function, if it's return is > 0 then the serial number is present in the note.Use Instr([Notes],[Serial])>0 on the first row of the query building interface and True as the criteria.
    Cheers,
    Vlad

  7. #7
    crimedog is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Posts
    141
    This worked - Dlookup("[OrderID]","Orders","[Note] Like'"&"*"&[Serial]&"*")

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

Similar Threads

  1. Replies: 5
    Last Post: 02-20-2018, 07:25 PM
  2. Replies: 3
    Last Post: 08-04-2017, 08:28 AM
  3. Replies: 2
    Last Post: 01-10-2016, 06:47 PM
  4. Replies: 6
    Last Post: 12-07-2015, 02:09 PM
  5. Replies: 6
    Last Post: 05-29-2015, 10:21 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