Results 1 to 12 of 12
  1. #1
    bossysheryl is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    May 2019
    Posts
    8

    Wildcard Parameters and String fields

    I’ve used Access in various roles for a long time, but the role I’ve been in for the last couple of years has had me in a different headspace completely, it seems like I’ve forgotten two-thirds of what I used to know how to do. I’m hoping for some help with a strange database that I’m consolidating out of about a dozen different semi-related lists that other people and departments have created to track info about videos being used in for a huge online training player system that needs to be more centrally available.

    The best way to get the data to normalize into the correct parent/child relationships has been to turn all the data into plain text using Excel 2013, and then export the records into Database A; and then to append these string records into Database B, where I was able create the correct relationships in empty tables. This has worked a treat, but NOW I someone has requested that someone be able to query/report the data out according to their divisional “tagging” schemes. The trouble is….there are probably 200 different random tags that range from 2 characters long to probably 30 characters long. My understanding was that all they wanted was for the tags to be CAPTURED, not used for reporting.



    So, OK: I know I have to put in parameters….but what? I know I can’t just go to the query design grid and put Like "**"or Like "***" or Like "****" down to 25 wild cards. (I tried. I told you: I’ve forgotten most of what I used to do every day!)

    How do this? It’s all string data. Examples: one tag is IB, another is leadership_modular_series; I need one parameter query to pull any set of tags, for any group of characters from 2 to 30 odd.

    Here's what the sql window reads behind the query grid:


    SELECT tbl_ALL_Parent_Videos.ID, tbl_ALL_Parent_Videos.Tags, tbl_ALL_Parent_Videos.OD_Multi_Virtual_Cert, tbl_ALL_Parent_Videos.English_US_Master_BCID, tbl_ALL_Parent_Videos.BCID_Playlist_Ordinal, tbl_ALL_Parent_Videos.English_File_Name, tbl_ALL_Parent_Videos.English_Display_Name, tbl_ALL_Parent_Videos.English_Category, tbl_ALL_Parent_Videos.Product_handle, tbl_ALL_Parent_Videos.Original_Upload_Date, tbl_ALL_Parent_Videos.English_Brightcove_China_BCI D, tbl_ALL_Parent_Videos.Parent_Insight_Excelerator_i d, tbl_ALL_Parent_Videos.Insights, tbl_ALL_Parent_Videos.Excelerator, tbl_ALL_Parent_Videos.PPT, tbl_ALL_Parent_Videos.VC, tbl_ALL_Parent_VTT.OrgVersionSubtitleFileName, tbl_ALL_Parent_VTT.VTT_final_timestamp, tbl_ALL_Parent_VTT.Running_Time, tbl_ALL_Parent_VTT.Includes_embedded_video, tbl_ALL_Parent_VTT.Embed_Vid_starts, tbl_ALL_Parent_VTT.[Embed_ Vid_terminates], tbl_ALL_Parent_VTT.Embed_video_total_time, tbl_ALL_Parent_VTT.English_vtt_attached
    FROM tbl_ALL_Parent_Videos INNER JOIN tbl_ALL_Parent_VTT ON tbl_ALL_Parent_Videos.English_US_Master_BCID = tbl_ALL_Parent_VTT.English_US_Master_BCID;



    It's the ALL_Parent_Videos.Tags field that has the string data.

    I feel so DUMB. I know I solved the same kind of 'random text' problem in at least 3 other databases in other jobs. I just can’t remember how! Thanks for any help you can give. I'll have to take an Access class before I say I could return the favor.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The * wildcard represents any number of characters. If the criteria is going to point to a textbox on a form where the user will have entered a value this:

    Like "*" & Forms!FormName.TextboxName & "*"

    should find records with the input text anywhere in the field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    bossysheryl is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    May 2019
    Posts
    8
    I need it not to BE on a form, though. I need it to pull from the query, so that it spits out a snapshot datasheet.

  4. #4
    bossysheryl is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    May 2019
    Posts
    8
    Also, the wildcard pulls ALL of them, not whatever specific tag that's wanted. Is there...something about placeholders that let me call out 2 characters 3 characters, up to whatever 30 odd?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, unless I'm misunderstanding, the user is going to specify the tag they're looking for. In your example, this should work as a criteria:

    Like '*IB*'

    to find the tags with
    IB in them. If I'm still off track, perhaps you can post some data and an example of what you'd want to pull out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    bossysheryl is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    May 2019
    Posts
    8
    Right, for just ONE tag, but that would require a separate query for every tag. I need the criteria that that allows the input-er to type in IB, or ABC or leadership_modular_series, in the parameter pop up and have just the records with just IB, or ABC, or leadership_modular_series pull into the snapshot view.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Most of us would use a form to gather user input, you have a lot more control. To have the query prompt the user:

    Like "*" & [Enter tag] & "*"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    bossysheryl is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    May 2019
    Posts
    8
    For example, Like "*" returns all records without the parameter box popping up, but Like [*] returns nothing, even when I type in a tag I know is there.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you see post 7?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    bossysheryl is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    May 2019
    Posts
    8
    DAMMITT!!!! I KNEW it was something that simple!!!! I swear, it's Alzheimer's.

    This particular query will be attached to a report that will go on a choice form for the USERS to see, but there are 1-2 circumstances where the final database administrator may want to use the same query as the basis for some other object, so having it in the query was the issue.

    Thank you very much, Paul.

  11. #11
    bossysheryl is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    May 2019
    Posts
    8
    Do you mark this solved, or me?

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! Either of us can mark it solved. It's under Thread Tools at the top of the thread.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 5
    Last Post: 04-01-2019, 01:34 PM
  2. Replies: 5
    Last Post: 09-29-2015, 12:07 PM
  3. Replies: 9
    Last Post: 12-05-2013, 11:48 AM
  4. Search on text fields - Wildcard?
    By Harley Guy in forum Queries
    Replies: 3
    Last Post: 08-13-2013, 11:07 AM
  5. Split a string for use as query parameters.
    By Hobbes29 in forum Forms
    Replies: 3
    Last Post: 06-27-2010, 04:39 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