Results 1 to 5 of 5
  1. #1
    iubrownie is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    2

    Creating a Query based on certain text in the data

    Good day! First thank you for the help.



    I am working on a several queries where I need to pull data based on the last data in the line.

    For Example,
    U12 Report has data such as:

    U12 Boys/U12 Girls
    U12 Boys
    U12 Girls

    I need to combine this by using the U12

    Another example would be

    U12 Boys/U15 Boys
    U12 Boys/U15 Girls
    U12 Boys/U12 Girls/U15 Boys

    These would all go into a U15 division since the last division is using the U15

    Hopefully this is descriptive enough.

    I have also attached a document which shows the reports and the data used.


    Thank you in advance for the help.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in the form, the combo box would have: U12 Boys/U12 Girls
    thered be another text box to get the criteria, txtFind:

    parse out the division code when user picks the item in the combo:

    Code:
    sub cboBox_Afterupdate()
    sVal = mid(cboBox,instr(cboBox,"/")+1)
    i = instr(sVal," ")
    Code:
    
    txtFind = left(sVal,i-1)
    
    
    docmd.openquery "qsFindDivision"
    end sub
    


    then run a query that looks at the txtFind box:
    select * from table where [division]=forms!myForm!txtFind


  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.....

    You didn't provide field names, table name, form name or a dB, so I made up an example dB using my (not so good) names.

    Since you only wanted the last "U" number in an entry, the query would look something like
    Code:
    SELECT iubrownie.PossibleCombinations, IIf(IIf(InStrRev([PossibleCombinations],"U")>0,Mid([PossibleCombinations],InStrRev([PossibleCombinations],"U"),3),"x")="U" & [forms]![Form7].[text0],1,0) AS Expr1
    FROM iubrownie
    WHERE (((IIf(IIf(InStrRev([PossibleCombinations],"U")>0,Mid([PossibleCombinations],InStrRev([PossibleCombinations],"U"),3),"x")="U" & [forms]![Form7].[text0],1,0))>0))
    ORDER BY iubrownie.PossibleCombinations;
    if my testing was any good.



    You will have to change the field names to your names...
    Attached Files Attached Files

  4. #4
    iubrownie is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    2
    Ssanfu

    Thank you.

    The table name is Events and the field Name is DivisionHeld

    I am not using a form for this. I am linking the table from a Excel sheet since it is updates from an export daily and no data is manually entered.

    Let me know if this helps.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Nope, doesn't really help. Remember I don't know anything about what you are doing with the dB, What you want to use the query for, what the report designs are, didn't know you are linking to an Excel spreadsheet, what the column names are in the spreadsheet, how you are opening the reports,......

    You can see how I created the query: you should be able to modify the query to work with your reports...


    Or explain better and post your dB.

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

Similar Threads

  1. Replies: 3
    Last Post: 03-18-2020, 04:54 PM
  2. Define data type when creating a table based on a query
    By viniciusfmnogueira in forum Access
    Replies: 3
    Last Post: 10-31-2019, 09:38 AM
  3. Replies: 2
    Last Post: 09-27-2014, 09:48 AM
  4. Replies: 4
    Last Post: 01-28-2014, 01:14 PM
  5. Replies: 0
    Last Post: 08-31-2009, 10:50 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