SQL Query: Having 'OR' clause causes problems

[rusco]

Hi guys!

I seem to be having problems with a SQL statement that I use to create a report in MS Access 2000. This speicific line in my sql statement: "sql = sql & "Or D.PayrollException = True " (refer to the complete SQL below) causes my Access to just say 'running query' in the bottom left screen of ACCESS (sort of like a status bar) but it doesn't seem to complete. It eats up my resources but it won't do anything and I have to manually close the application via the task manager. If I remove that line, everything works fine (the report shows up almost immedaitely).

Any suggestions would be really appreciated. Thanks!

rusco

    sql = "SELECT DISTINCTROW B.PeriodEnding, "
    sql = sql & "A.EmployeeNumber, "
    sql = sql & "A.LastName & ', ' & A.FirstName AS EmployeeName, "
    sql = sql & "E.Facility, "
    sql = sql & "A.WorkDept, "
    sql = sql & "B.ChargeNumber, "
    sql = sql & "D.ActivityDesc, "
    sql = sql & "B.Description, "
    sql = sql & "B.IsDemand, "
    sql = sql & "B.IsOvertime, "
    sql = sql & "D.PayrollException, "
    sql = sql & "B.CapitalProject, "
    sql = sql & "SUM(B.RegularHours) AS RegularHours, "
    sql = sql & "SUM(B.PayrollHours) AS PayrollHours, "
    sql = sql & "CCur(SUM(B.RegularHours) * C.RegularRate + ((SUM(B.PayrollHours) - SUM(B.RegularHours)) * C.OvertimeRate)) AS PayAmount "
    sql = sql & "FROM tblEmployee AS A, tblJobHistory AS B, tlkpLevel AS C, tlkpActivity D, tlkpFacility E "
    sql = sql & "WHERE A.EmployeeNumber = B.EmployeeNumber "
    sql = sql & "AND A.CurrentLevel = C.Level "
    sql = sql & "AND B.ActivityID = D.ActivityID "
    sql = sql & "AND A.FacilityID = E.FacilityID "
    sql = sql & "AND PeriodEnding BETWEEN #" & g_from_date & "# AND #" & g_to_date & "# "
    sql = sql & "AND B.IsOvertime = True "
    sql = sql & "Or D.PayrollException = True "
    sql = sql & "GROUP BY B.PeriodEnding, A.EmployeeNumber, A.LastName, A.FirstName, E.Facility, "
    sql = sql & "A.WorkDept, B.ChargeNumber, B.Description, C.RegularRate, C.OvertimeRate, D.ActivityDesc, "
    sql = sql & "B.IsDemand, B.IsOvertime, D.PayrollException, B.CapitalProject "
    sql = sql & "ORDER BY A.WorkDept, E.Facility, A.LastName;"

 

[ecniv]

Perhaps to brackets to check which bit has to be looked at first? Might help.
Also grab a smaller amount of data to test on.


Vince

 

[rusco]

Hi Vince!

Thanks! I got it working fine right now by placing brackets.

rusco