Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Like statement (MSACC2993)

    i am trying to add a like statement that will narrow down the items in a list. I have a form named Form11, it has a textbox on it named RM.
    Can someone tell me the syntax to add it it in, as the query grid is not available with the query. Many thanks

    MySql = "SELECT Athlete"
    MySql = MySql & " FROM MDA"
    MySql = MySql & " UNION SELECT Athlete"
    MySql = MySql & " FROM SDA"
    MySql = MySql & " UNION SELECT Athlete"
    MySql = MySql & " FROM SSDA"

  2. #2
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like statement (MSACC2993)

    Thanks Hans, got me out of a mess again. Many thanks

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like statement (MSACC2993)

    I seem to be missing records? I use the first code to fill a listbox with names from 3 tables, this seems to work okay.

    On the list click event I use the second code, however it works if a name is selected that comes from the table MDA, but if it comes from SDA or SSDA then it produces nothing in the second list?? Anyone see a reason, thanks

    Mysql = "SELECT Athlete FROM MDA"
    Mysql = Mysql & " UNION SELECT Athlete FROM SDA"
    Mysql = Mysql & " UNION SELECT Athlete FROM SSDA"
    Me.List121.RowSource = Mysql

    Mysql = "SELECT Athlete FROM MDA WHERE Athlete Like " & Chr(34) & Forms!Form2!List121 & "*" & Chr(34) & _
    " UNION SELECT Athlete FROM SDA WHERE Athlete Like " & Chr(34) & Forms!Form2!List121 & "&" & Chr(34) & _
    " UNION SELECT Athlete FROM SSDA WHERE Athlete Like " & Chr(34) & Forms!Form2!List121 & "*" & Chr(34)
    Me.List127.RowSource = Mysql

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Like statement (MSACC2993)

    Edited by HansV to correct error

    Does this do what you want?

    MySQL = "SELECT Athlete FROM MDA WHERE Athlete Like " & Chr(34) & Forms!Form11!RM & "*" & Chr(34) & _
    " UNION SELECT Athlete FROM SDA WHERE Athlete Like " & Chr(34) & Forms!Form11!RM & "*" & Chr(34) & _
    " UNION SELECT Athlete FROM SSDA WHERE Athlete Like " & Chr(34) & Forms!Form11!RM & "*" & Chr(34)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Like statement (MSACC2993)

    There was an error in my previous reply, I've corrected it.

    The line

    " UNION SELECT Athlete FROM SDA WHERE Athlete Like " & Chr(34) & Forms!Form2!List121 & "&" & Chr(34) & _

    should be

    " UNION SELECT Athlete FROM SDA WHERE Athlete Like " & Chr(34) & Forms!Form2!List121 & "*" & Chr(34) & _

    Apart from that, no idea without seeing the database.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like statement (MSACC2993)

    Thanks Hans, sorting the error cured my problem. Thanks again.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like statement (MSACC2993)

    In a union query, do all fields have to be the same in each select statement? and can you have mixed numbers of fields in each select statement. My first query works okay, but adding the second kills the output. I am wondering if I have to add false fields syntax to even the numbers of each query (only thinking) However it could be an error in my sql?

    Mysql = "SELECT tblDirectory.SD1, MainDirDocs.SD1, MDA.Athlete"
    Mysql = Mysql & " FROM (tblDirectory INNER JOIN MainDirDocs ON tblDirectory.ID1 = MainDirDocs.ID1) INNER"
    Mysql = Mysql & " JOIN MDA ON MainDirDocs.ID2 = MDA.ID2A"
    Mysql = Mysql & " WHERE MDA.Athlete Like " & Chr(34) & Forms!Form2!List121 & "*" & Chr(34)


    Mysql = Mysql & "UNION SELECT tblDirectory.SD1, tblSubDir.SD1, SubDirectoryDocs.SD1, SDA.Athlete"
    Mysql = Mysql & " FROM ((tblDirectory INNER JOIN tblSubDir ON tblDirectory.ID1 = tblSubDir.ID1) INNER"
    Mysql = Mysql & " JOIN SubDirectoryDocs ON tblSubDir.DirID = SubDirectoryDocs.DirID) INNER JOIN SDA ON SubDirectoryDocs.ID2 = SDA.ID2A"
    Mysql = Mysql & " WHERE SDA.Athlete Like " & Chr(34) & Forms!Form2!List121 & "*" & Chr(34)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Like statement (MSACC2993)

    in a union query, each of the contributing queries *must* have the same number of fields. Corresponding fields don't need to have the same name (the name in the first contributing query determines the name of the field in the union), but corresponding fields *must* be of the same type.
    If a field is missing in one of the contributing queries, you can use a dummy field instead, for example

    SELECT tblDirectory.SD1, MainDirDocs.SD1, Null AS Something, MDA.Athlete FROM ...
    UNION
    SELECT tblDirectory.SD1, tblSubDir.SD1, SubDirectoryDocs.SD1, SDA.Athlete FROM ...

  9. #9
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like statement (MSACC2993)

    Thanks again Hans, that should fix me up. Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •