Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: Error in Syntax?

    Ok, so in the attached DB, I'm trying to put the subrptIncInfo into the main report on a page break. I can do this. However, I've put the code in as a source for the subreport to pull information so that I can populate the listboxes with the actual Agent Names, Primary Squad, Primary Topic-of-Discussion but the code doesn't work and I can't figure it out.

    If you view the subreport by itself, it displays the information the way that I want / need it to be but inserting the subreport shows blank information **ONLY** if I leave the code below OUT of the subreport source.

    Any ideas?

    Code:

    Code:
    SELECT tblTopic.TopicID, tblTopic.ToDID, tblTopic.Occurred_On, tblTopic.PoCID, tblTopic.Attendees, 
    tblTopic.AgentID, tblTopic.Other_Agents, tblTopic.Primary_Squad, tblTopic.Other_Squads, tblTopic.Notes,
     tblToD.ToDID, tblToD.Topic, tblAgent.AgentID, tblAgent.FirstNM, tblAgent.LastNM, tblSquad.SquadID, 
    tblSquad.SquadNM FROM tblToD LEFT JOIN (tblAgent LEFT JOIN tblTopic ON tblAgent.AgentID=tblTopic.AgentID) 
    ON tblSquad.SquadID=tblTopic.Primary_Squad) ON tblToD.ToDID=tblTopic.ToDID;
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'll have to look at this later... better not do it while "at work" ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You have 3 on clauses for 2 joins..?
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by georgev
    You have 3 on clauses for 2 joins..?
    Splain? I don't follow... Sorry...

    **OH! Duh! When I SLOWLY read it, I understand it. I read 'clauses' and thought of like Santa Claus! Duh! I'm so dumb! I'll check it out again and see what's what! Thanks...
    Last edited by Grafixx01; 10-03-08 at 11:17.

  5. #5
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by georgev
    You have 3 on clauses for 2 joins..?
    Ok, just looked at it again and still just don't see what you see George.

    I'm trying to pull the following:

    AgentID, AgentFN, AgentLN
    SquadID, SquadNB, SquadNM
    ToDID, ToD

    This is done so that on the subreport in the listboxes of:

    Primary Topic of Discussion reads , in words, what the Primary Topic of Discussion of the Topic was.
    Primary Agent reads, in words, what the First Name and Last Name of the primary contact agent is.
    Primary Squad reads, in words, what the Squad Number and actual Squad Name are.

    The thing that I'm perplexed about is if you open the subreport, the information is pulled correctly, just the way I want it.

    BUT

    If you insert the subreport in the main report, it doesn't pull those pieces of information into the listboxes. HOWEVER, it does pull the actual ID numbers into the textbox fields.

    That's what's getting to me more than anything so I figured it was in the way that I wrote the listbox rowsource and data information?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Re-write your query in a readable fashion and you might just spot it...
    Code:
    FROM tblToD
     LEFT
      JOIN tblAgent
     LEFT
      JOIN tblTopic
        ON tblAgent.AgentID=tblTopic.AgentID
        ON tblSquad.SquadID=tblTopic.Primary_Squad
        ON tblToD.ToDID=tblTopic.ToDID
    2 joins... 3 ONs...
    George
    Home | Blog

  7. #7
    Join Date
    Aug 2006
    Posts
    559
    I used the Query Designer and did the whole drag-and-drop deal. It produced the below SELECT statement which worked when I ran it but if I put it into the record source for the subreport, it still isn't showing unless I view the subreport by itself.

    Code:
    SELECT tblTopic.TopicID, tblTopic.ToDID, tblTopic.Occurred_On, 
    tblTopic.PoCID, tblTopic.Attendees, tblTopic.AgentID, tblTopic.Other_Agents,
     tblTopic.Primary_Squad, tblTopic.Other_Squads, tblTopic.Notes, tblToD.ToD, 
    tblAgent.FirstNM, tblAgent.LastNM, tblSquad.SquadNB, tblSquad.SquadNM
    FROM tblToD RIGHT JOIN (tblTopic INNER JOIN (tblSquad INNER JOIN tblAgent 
    ON tblSquad.SquadID=tblAgent.SquadID) ON 
    tblTopic.AgentID=tblAgent.AgentID) ON tblToD.ToDID=tblTopic.ToDID;

  8. #8
    Join Date
    Aug 2006
    Posts
    559
    Well, I got it to work but not the way that I want it to work! I deleted the list boxes and just put in regular text boxes and it displays fine. Not what I really wanted cause I think it looks a little tacky. Oh well!

    If anyone can figure out what I did wrong and let me know, so I can make it the way I really want to see it, please let me know.

    Thanks a great deal!

Posting Permissions

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