Results 1 to 13 of 13
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Using Allen Brown ConcatRelated() In Query

    I am trying to use this wonderful function in a query, but I am getting the error:


    Error 3075: Syntax error (missing operator) in query expression '[Company Name] = All 4 One, LLC'.

    This is how my query is set-up - only thing I see different is that I have brackets, but some fields have spaces so they are needed.

    What is incorrect in this element?
    Code:
    cID: ConcatRelated("[cID]", "[Contractual Obligations]", "[Contract ID] = " & [Contract ID])

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    post the rest of the sql? The message seems to be related to a portion that you're not showing: [Company Name]
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Which is it, company name or contract id? From Allen's site:

    If the foreign key field is Text (not Number), include quote marks as delimiters, e.g.:
    "[ForeignKeyFieldName] = """ & [PrimaryKeyFieldName] & """"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by Micron View Post
    post the rest of the sql? The message seems to be related to a portion that you're not showing: [Company Name]
    This is SQL of my query
    Code:
        Select [Company Name], cID: ConcatRelated("[cID]", "[Contractual Obligations]", "[Contract ID] = " & [Contract ID]) As cID FROM [Contractual Obligations]
        WHERE (((ConcatRelated("[cID]", "[Contractual Obligations]", "[Contract ID] = " & [Contract ID])="I"))
        ORDER BY [Company Name];

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by pbaldy View Post
    Which is it, company name or contract id? From Allen's site:

    If the foreign key field is Text (not Number), include quote marks as delimiters, e.g.:
    "[ForeignKeyFieldName] = """ & [PrimaryKeyFieldName] & """"
    The table only holds 3 fields - Company Name, cID, sID

    Company Name is of course the PK.

    And they are all short text fields

  6. #6
    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 adding the delimiters as in Allen's link?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by pbaldy View Post
    Did you adding the delimiters as in Allen's link?
    Quote Originally Posted by pbaldy View Post
    Did you adding the delimiters as in Allen's link?
    If I change the last piece to "[Contract ID] = """ & [Contract ID] & """")

    Then I get an error of
    The expression you entered has an invalid string.

    A string can be up to 2048 characters long, including opening and closing quotes

    (and this error displays when I try to leave the field in the query, I am unable to even run it)

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is the SQL? You have to use your field names.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by pbaldy View Post
    What is the SQL? You have to use your field names.
    This is my sql
    [code]
    Select [Company Name], cID: ConcatRelated("[cID]", "[Contractual Obligations]", "[Contract ID] = " & [Contract ID]) As cID FROM [Contractual Obligations]
    WHERE (((ConcatRelated("[cID]", "[Contractual Obligations]", "[Contract ID] = " & [Contract ID])="I"))
    ORDER BY [Company Name];

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    2 things are suspect to me

    a) cID: ConcatRelated("[cID]",...AS CID...
    suggest you drop the cID: as it looks to me like 2 aliases for the same thing 1)cID and 2)"As cID
    My guess is that cID: is being interpreted as an expression
    I would venture to guess that you didn't build this query in query design view, nor paste it into query sql view and trying to run it from there.

    b)
    And they are all short text fields
    all fields with ID in their name are text? Then
    [Contract ID] has to be delimited - if not the way you tried, then via "[Contract ID] = '" & [Contract ID]) & "'") The fact that this raised the new error suggests maybe it's not text.

  11. #11
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    I need my glasses strengthened, I read that as being "" and """ (two double quotes and three double quotes) and not '" and "'" (single, double and double, single, double)

    Thanks for the patience!

  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
    Allen used 3 and 4 double quotes, but I use Micron's method. I don't think you want the criteria either.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Dont overlook the cID: thing. It doesn't look right to me.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 8
    Last Post: 09-28-2018, 02:55 PM
  2. Allen Brown's - FindAsUType - combo box order
    By Homegrownandy in forum Programming
    Replies: 6
    Last Post: 01-31-2018, 03:38 AM
  3. Classic Allen B ConcatRelated()
    By Forbes in forum Modules
    Replies: 22
    Last Post: 05-08-2017, 08:44 AM
  4. Replies: 2
    Last Post: 07-12-2013, 06:55 AM
  5. Filter to a date range ala Allen Brown
    By kekawaka in forum Forms
    Replies: 2
    Last Post: 10-10-2011, 12:19 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