Sum and grouping between two user entered dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have posted a question similar to this before but I have lost track of the
thread. I want to be able to group the number of hours worked by individuals
between two user inputted dates.

The following sql will generate all the records between the two user entered
dates

SELECT * FROM Results WHERE PerDate >='::StartDate::' and PerDate
<='::EndDate::'

This sql query works but obviously doesn't group the records.

The following sql query will group the records between two hardcoded dates.

SELECT TaskOwner, SUM(Hours) as SumOfHours FROM Results WHERE PerDate
=#01/01/2005# and PerDate <= #12/12/2005# GROUP BY TaskOwner
Click to expand...

This works well but I want the user to be able to enter the start and end
dates.

The following sql queries should group between two user entered dates.

SELECT TaskOwner, SUM(Hours) as SumOfHours FROM Results WHERE PerDate
=#::StartDate::# and PerDate <= #::EndDate::# GROUP BY TaskOwner
Click to expand...

OR

SELECT TaskOwner, SUM(Hours) as SumOfHours FROM Results WHERE PerDate
='::StartDate::' and PerDate <='::EndDate::' GROUP BY TaskOwner
Click to expand...

Both of those two queries do not produce the desired results. They do not
return "No records found." They return a message in a yellow box saying
Database "Results Wizard Error
The operation failed. If this continues, please contact your server
administrator."
The sql checker built into the DBRW approves the sql segment with
apostrophes but not the one with #. Is there a way to remedy this situation?
 
To temporarily see the true error, open the hidden folder /_fpclass/ and edit the fpdbrgn1.inc file
At about line 19, change :
fp_DEBUG = False
to :
fp_DEBUG = True

Test it step by step (1st just 1 condition, then the second , then the group by)

Have you tried Not entering a custom sql qry but just using the Wizard (Criteria), then editing the gray s-sql= code
--




|I have posted a question similar to this before but I have lost track of the
| thread. I want to be able to group the number of hours worked by individuals
| between two user inputted dates.
|
| The following sql will generate all the records between the two user entered
| dates
|
| SELECT * FROM Results WHERE PerDate >='::StartDate::' and PerDate
| <='::EndDate::'
|
| This sql query works but obviously doesn't group the records.
|
| The following sql query will group the records between two hardcoded dates.
|
| SELECT TaskOwner, SUM(Hours) as SumOfHours FROM Results WHERE PerDate
| >=#01/01/2005# and PerDate <= #12/12/2005# GROUP BY TaskOwner
|
| This works well but I want the user to be able to enter the start and end
| dates.
|
| The following sql queries should group between two user entered dates.
|
| SELECT TaskOwner, SUM(Hours) as SumOfHours FROM Results WHERE PerDate
| >=#::StartDate::# and PerDate <= #::EndDate::# GROUP BY TaskOwner
|
| OR
|
| SELECT TaskOwner, SUM(Hours) as SumOfHours FROM Results WHERE PerDate
| >='::StartDate::' and PerDate <='::EndDate::' GROUP BY TaskOwner
|
| Both of those two queries do not produce the desired results. They do not
| return "No records found." They return a message in a yellow box saying
| Database "Results Wizard Error
| The operation failed. If this continues, please contact your server
| administrator."
| The sql checker built into the DBRW approves the sql segment with
| apostrophes but not the one with #. Is there a way to remedy this situation?
|
|
|
|
 
This is the error message that is produced:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name
or ordinal.

/slug/_fpclass/fpdblib.inc, line 48
 
Back
Top