Results 1 to 5 of 5
  1. #1
    CEROBalancing is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    3

    Question Date based search on two query date fields + Syntax Error / Too Complicated Error

    Hi All - I am a homegrown Access programmer so I apologize ahead of time for a potential obvious error question. I have a query [payroll] that is searching two queries for records based on a designated time frame and it should group them by the clinican ID. If I take off the date search, the query runs just fine. If I apply a date filter I receive the Expression typed incorrectly or is too complicated error.

    I would like the user to enter the date beginning/end only one time versus two times due to searching ptolog.date and searching summonthlylog.date



    Any assistance you could provide would be great. The sql coding is below. Thank you ahead of time!



    SELECT Clinician.ID
    , Clinician.[Clinician Name]
    , Clinician.Location
    , Clinician.ExpectedNorm
    , Clinician.FTE
    , SumMonthlyLog.Earnedpoints
    , Sum([pto log query].[PtoPoint]+[SumMonthlyLog].[EarnedPoints]) AS TotPoints
    , Sum(([expectednorm]*[workdays])*[clinician].[fte]) AS AdjExp
    , ([TotPoints]/[AdjExp]) AS performance
    , [summonthlylog].[date] Or [pto log query].[date] AS DateSearch


    FROM (Clinician INNER JOIN SumMonthlyLog ON Clinician.ID = SumMonthlyLog.[Provider ID]) INNER JOIN [PTO LOG Query] ON Clinician.ID = [PTO LOG Query].[Provider ID]


    GROUP BY Clinician.ID
    , Clinician.[Clinician Name]
    , Clinician.Location
    , Clinician.ExpectedNorm
    , Clinician.FTE
    , SumMonthlyLog.Earnedpoints
    , [pto log query].[date] Or [summonthlylog].[date]
    , [summonthlylog].[date] Or [pto log query].[date]


    HAVING ((([summonthlylog].[date] Or [pto log query].[date]) Between [forms]![Report Date Range]![Beginning Date] And [forms]![Report Date Range]![Ending Date]));

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,303
    I thnk it's you dates.
    , [pto log query].[date] Or [summonthlylog].[date]
    , [summonthlylog].[date] Or [pto log query].[date]


    Did. You build this in query builder?
    it should be...
    (tbl1.date between forms!frm!date and forms...date) or (tbl2.date between forms!frm!date and forms...date)

  3. #3
    CEROBalancing is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    3
    built with a combination. The field addition component was done on the SQL side. I pulled the date thread from a previous code.

  4. #4
    CEROBalancing is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    3
    Thank you for your response ranman256


    I made the adjustment and I am no longer receiving the error, but it is returning records that are not in the date range. Any thoughts on how that could be happening? I am running the search from 3/1/15 - 3/30/15 and my record for 4/7/15 is showing up

    GROUP BY
    Clinician.ID,
    Clinician.[Clinician Name],
    Clinician.Location,
    Clinician.ExpectedNorm,
    Clinician.FTE,
    SumMonthlyLog.Earnedpoints,
    [PTO LOG Query].Date,
    SumMonthlyLog.Date


    HAVING ((([PTO LOG Query].Date) Between [forms]![Report Date Range]![Beginning Date] And [forms]![Report Date Range]![Ending Date])) OR (((SumMonthlyLog.Date) Between [forms]![Report Date Range]![Beginning Date] And [forms]![Report Date Range]![Ending Date]));

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,303
    Are you sure? That OR condition could encompass a lot of dates.

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

Similar Threads

  1. Mysterious date syntax error message
    By Monterey_Manzer in forum Queries
    Replies: 12
    Last Post: 12-23-2013, 06:56 PM
  2. Replies: 9
    Last Post: 11-25-2013, 04:33 PM
  3. Replies: 3
    Last Post: 10-25-2012, 10:04 PM
  4. using FindFirst with a Date Criteria (syntax error)
    By ajetrumpet in forum Programming
    Replies: 2
    Last Post: 09-10-2010, 11:01 PM
  5. SQL INSERT INTO Date Syntax Error
    By tuna in forum Programming
    Replies: 5
    Last Post: 08-10-2010, 06:17 AM

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
  •  
Other Forums: Microsoft Office Forums