Results 1 to 4 of 4
  1. #1
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143

    Left Join not showing all fields. Once design closed, can't reopen. Join expression not supported

    The MainTasks are labor categories, Taskhours are man hours spent in each category. TaskWages is the cost of labor in each category.

    Weird things are happening. At first the following SQL was just an embedded query in a subreport. It would work when I was designing the query, I made sure to save it, but then when I ran the report the whole subreport was blank, and if I tried to get back into the query design view, I got an error "JOIN expression not supported". I cut the SQL from the Record Source and pasted it in design view again, but it had changed, getting rid of the parentheses after the ON statement.
    I then tried saving it as its own query and again it worked when I just ran the query by itself, but then when I tried running the report it was missing a MainTask that had no related records. Then I again try opening the query in design view and it had automatically deleted the parentheses again. I have tried typing them back in multiple times now but they keep getting deleted which stops the query from working.

    Any idea what is happening or how I can stop it?




    Code:
    SELECT qryPayrollTotals.startdate AS PayPeriod, tblTasks.MainTasks, Sum(qryPayrollTotals.SumOfTaskHours) AS TotalSumOfTaskHours, Sum(qryPayrollTotals.TaskWages) AS SumOfTaskWages
    FROM tblTasks
    LEFT JOIN qryPayrollTotals
    ON ((tblTasks.MainTasks = qryPayrollTotals.MainTasks) AND (qryPayrollTotals.startdate = [reports]![rptPayrollChart]![PayPeriod]))
    GROUP BY qryPayrollTotals.startdate, tblTasks.MainTasks
    ORDER BY tblTasks.MainTasks;

  2. #2
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Now I just went into design view again and it completely deleted the Left Join and On statements.
    I'm so confused.

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I don't believe you can join using variables, only on table/query fields. Change the report field to be a criteria.

  4. #4
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Ah hah! You are right! And I think it may even be more than that because when I was first trying it out, I was using the criteria #08/29/2017# since I didn't have the report open to get the date from, and it was still automatically deleting parentheses.
    But I thought I was just not writing the variable correctly.

    I do still need the variable, so I created a select query with all the same fields as the original query using the [reports]![rptPayrollChart]![PayPeriod] criteria to select the correct records first. Then I based the Left Join query off of the new one and got rid of the variable all together.

    Code:
    SELECT qryPayrollTotals.startdate, qryPayrollTotals.MainTasks, Sum(qryPayrollTotals.SumOfTaskHours) AS TotalSumOfTaskHours, Sum(qryPayrollTotals.TaskWages) AS SumOfTaskWagesFROM qryPayrollTotals
    GROUP BY qryPayrollTotals.startdate, qryPayrollTotals.MainTasks
    HAVING (((qryPayrollTotals.startdate)=[reports]![rptPayrollChart]![PayPeriod]))
    ORDER BY qryPayrollTotals.MainTasks;
    Code:
    SELECT tblTasks.MainTasks, qryTotalTaskWagesDate.TotalSumOfTaskHours, qryTotalTaskWagesDate.SumOfTaskWages
    FROM tblTasks
    LEFT JOIN qryTotalTaskWagesDate
    ON tblTasks.MainTasks = qryTotalTaskWagesDate.MainTasks
    ORDER BY tblTasks.MainTasks;

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

Similar Threads

  1. Replies: 6
    Last Post: 10-19-2016, 03:40 AM
  2. RIGHT join expression not supported?
    By ittechguy in forum Queries
    Replies: 3
    Last Post: 11-12-2015, 11:00 PM
  3. JOIN expression not supported
    By seageath in forum Access
    Replies: 2
    Last Post: 12-05-2011, 08:41 PM
  4. Join Expression not Supported
    By amegahed3 in forum Queries
    Replies: 6
    Last Post: 09-29-2010, 01:28 PM
  5. Join Expression Not Supported
    By ubsman in forum Queries
    Replies: 3
    Last Post: 04-30-2009, 08:36 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