Results 1 to 3 of 3
  1. #1
    Tyork is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    35

    SQL Inner Join where date = current date

    So I'm having an issue with a query. My code is as follows



    Code:
     
    SELECT TaskName
    FROM (BMS_Task INNER JOIN BMS_Occurrence
    ON BMS_Task.TaskID = BMS_Occurrence.TaskID)
    INNER JOIN BMS_Occurrence
    ON BMS_Occurrence.Day = date();
    The problem comes in where I include the second inner join. The query works as it should when it is only:

    Code:
     
    SELECT TaskName
    FROM (BMS_Task INNER JOIN BMS_Occurrence
    ON BMS_Task.TaskID = BMS_Occurrence.TaskID)
    Any help would be appreciated. Thanks!

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    You cannot use a JOIN as a criteria statement. what I think you want is:
    Code:
    SELECT TaskName
    FROM (BMS_Task INNER JOIN BMS_Occurrence
    ON BMS_Task.TaskID = BMS_Occurrence.TaskID)
    
    WHERE 
    
    BMS_Occurrence.Day = date();
    the other thing you should have checked is the "AutoJoin" feature that is located in the Access options. Most people, even developers do not write join statements manually. there is really no need to anymore.

  3. #3
    Tyork is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    35
    Thank you, that worked perfectly. I thought I had tried that but evidently I did not.

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

Similar Threads

  1. Current Date button on Report
    By Steven.Allman in forum Programming
    Replies: 8
    Last Post: 10-31-2010, 03:28 PM
  2. Comparing Date with current Date
    By ds_8805 in forum Forms
    Replies: 7
    Last Post: 03-31-2010, 09:31 PM
  3. Replies: 9
    Last Post: 03-19-2010, 10:37 AM
  4. Current Date Error
    By McFly in forum Database Design
    Replies: 4
    Last Post: 02-04-2010, 09:08 AM
  5. Changing a date to the current year
    By fdnyfish in forum Access
    Replies: 1
    Last Post: 03-01-2008, 08:34 AM

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