Results 1 to 5 of 5

Error- Syntax Error in JOIN Operation

  1. #1
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    66

    Error- Syntax Error in JOIN Operation

    Hi,



    I am using Microsoft Access 2016. I am creating a database that will allow a user to create a schedule that tells employees to test specific orders that a customer places and also allows them to record the data. The schedules are ordered by date. Right now, I have an Intro Form that allows the user to choose where to navigate in the database. The schedules are under the "Schedules" button, as shown below:

    Click image for larger version. 

Name:	schedule intro.jpg 
Views:	7 
Size:	283.9 KB 
ID:	38732

    When clicked, a list of all of the available dates of schedules appear, as shown below.

    Click image for larger version. 

Name:	Choose date.jpg 
Views:	7 
Size:	77.8 KB 
ID:	38733

    I want the user to be able to select which date they want to view the schedule for and open up a form that shows the schedule for that date (the form should be filtered by that date). I have created similar filtered forms before, and I have never had any issues. With this form, though, when I click the "Select" button, I get an error that says "Syntax Error in JOIN Operation". For the button I used macros, and this is what I have in my macros for the button:

    Click image for larger version. 

Name:	open new scheudle form.jpg 
Views:	7 
Size:	63.6 KB 
ID:	38734

    To filter the next form based on the date, I would typically include the statement ="[Order_Date]="&[Order_Date] in the "Where Condition" section, but I have taken that out because I thought that might be where the error was coming from. It is not, butI've researched the error, and it seems like this is a typical issue with a query. I myself did not create a query manually, and I'm no expert on queries by any means, but I did use a form wizard to create my form, and in the Record Source of my form it shows which table the data is coming from, and behind that I believe a query is created (or something like that - again, I'm no expert, this is just what I have observed). In this query that seems to have been created by Access, this is what I see:
    Click image for larger version. 

Name:	query.jpg 
Views:	7 
Size:	91.1 KB 
ID:	38735 Click image for larger version. 

Name:	query select.jpg 
Views:	7 
Size:	18.0 KB 
ID:	38736

    I am at a loss for how to fix this. Any ideas?

  2. #2
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    66
    I figured out that it was not my Choose_Schedule_Date form that was causing the error, it was the form to which that the button was trying to navigate, and this is happening on all of my forms that have fields from more than 1 table. I cannot view these forms in form view because I get the same error as stated previously, but when I view them in Design Mode, there is a green triangle in the upper left corner of the text boxes for the fields, and the error message attached to them says "Invalid Control Property: Control Source". Here is a screenshot of my table relationships, and here is what it says in the control source of one of my forms: SELECT [Order_Table].[Spin_Number], [Order_Date].[Order_Date] FROM Order_Date_Table INNER JOIN Order_Table ON [Order_Date].[ID] =[Order_Table].[Date_ID];

    Click image for larger version. 

Name:	relationshipssss.jpg 
Views:	7 
Size:	64.4 KB 
ID:	38737


    Any idea why this might be happening?

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,162
    Need to pull fields into the grid so the SQL statement would be either of:

    SELECT Order_Date_Table.* FROM Order_Date_Table;

    or

    SELECT Order_Date_Table.ID, Order_Date_Table.Order_Date FROM Order_Date_Table;

    A form should do data entry/edit for only 1 table. Why are you including multiple tables in RecordSource? What purpose is a table of dates? Why not just save date instead of an ID?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    66
    I'm including multiple tables because I need to include data from multiple tables. My table relationships look like this:

    Click image for larger version. 

Name:	relationshipssss.jpg 
Views:	6 
Size:	64.4 KB 
ID:	38738

    Hence why I am including data from multiple tables

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,162
    Just re-read post2. You say "control source" - control source property cannot have SQL statement. Did you mean RecordSource property?

    A form should do data entry/edit for 1 table. Which table do you want to edit? If you want to include other tables to retrieve related info, don't allow edit of those fields. There are other ways to bring related info onto form.

    Probably should not use INNER JOIN, try RIGHT.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Syntax Error in Join Operation
    By R_Sam in forum Queries
    Replies: 1
    Last Post: 04-04-2016, 06:16 AM
  2. **Need help on Join Syntax error**
    By pradeepkumar089 in forum Queries
    Replies: 3
    Last Post: 12-23-2015, 11:55 PM
  3. Replies: 3
    Last Post: 01-23-2014, 07:49 AM
  4. Replies: 6
    Last Post: 05-30-2012, 12:32 PM
  5. Replies: 2
    Last Post: 06-13-2011, 06:04 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums