Sort Form/Subform by Date

[RandyH]

TIA All,...

I have a form (operations) that has unbound text fields of employees
first and last name as well as ID numbers on it. In the header of the form,
is a combo box (employeeID) which sorts the form and subform (operations
sub) to that employees information. The subform contains employee payroll
information (account#, account description, labor date, labor hours and pay
date). Account# and account description are from the 'Accounts' table, and
labor date, hours and pay date are from the 'Time' table.
All of that information falls within a given pay period (my 'Time'
database table also has two more fields, Pay period begin and Pay period
end) Here is my desired end result, when opening the form (operations), I
would like the user to select the beginning and ending pay period (should
this be put in the appropriate fields in the 'Time' table as well?). This
way the form and subform will at any given moment only show work done for
that pay period.

Randy

 

[John Vinson]

TIA All,...

I have a form (operations) that has unbound text fields of employees
first and last name as well as ID numbers on it. In the header of the form,
is a combo box (employeeID) which sorts the form and subform (operations
sub) to that employees information.

Jargon suggestion:

Many people use the word "sort" indescriminately like this. To Sort
data is to put selected records into a particular sequence; if you
start with 100 records and sort them, you end up with 100 records!

If you're using the combo to cut the display from 100 records down to
1, a better term is "filter"; if you're using it to find a particular
record, then it's better to say "find" or "search". It can get
confusing otherwise!

The subform contains employee payroll
information (account#, account description, labor date, labor hours and pay
date). Account# and account description are from the 'Accounts' table, and
labor date, hours and pay date are from the 'Time' table.
All of that information falls within a given pay period (my 'Time'
database table also has two more fields, Pay period begin and Pay period
end) Here is my desired end result, when opening the form (operations), I
would like the user to select the beginning and ending pay period (should
this be put in the appropriate fields in the 'Time' table as well?). This
way the form and subform will at any given moment only show work done for
that pay period.

You can have two unbound textboxes txtStart and txtEnd on your
Operations form, and base the Subform on a Query including criteria
such as

=[Forms]![Operations]![txtStart]

or (perhaps safer for time points)

BETWEEN [Forms]![Operations]![txtStart] AND
[Forms]![Operations]![txtEnd]

to limit display to date/time values within that range.

 

[RandyH]

TIA All,...

I have a form (operations) that has unbound text fields of employees
first and last name as well as ID numbers on it. In the header of the form,
is a combo box (employeeID) which sorts the form and subform (operations
sub) to that employees information.

Jargon suggestion:

Many people use the word "sort" indescriminately like this. To Sort
data is to put selected records into a particular sequence; if you
start with 100 records and sort them, you end up with 100 records!

If you're using the combo to cut the display from 100 records down to
1, a better term is "filter"; if you're using it to find a particular
record, then it's better to say "find" or "search". It can get
confusing otherwise!

The subform contains employee payroll
information (account#, account description, labor date, labor hours and pay
date). Account# and account description are from the 'Accounts' table, and
labor date, hours and pay date are from the 'Time' table.
All of that information falls within a given pay period (my 'Time'
database table also has two more fields, Pay period begin and Pay period
end) Here is my desired end result, when opening the form (operations), I
would like the user to select the beginning and ending pay period (should
this be put in the appropriate fields in the 'Time' table as well?). This
way the form and subform will at any given moment only show work done for
that pay period.

You can have two unbound textboxes txtStart and txtEnd on your
Operations form, and base the Subform on a Query including criteria
such as

=[Forms]![Operations]![txtStart]

or (perhaps safer for time points)

BETWEEN [Forms]![Operations]![txtStart] AND
[Forms]![Operations]![txtEnd]

to limit display to date/time values within that range.

Ok, I'll give you a nod at the confusion statement; problem is, most of us
are confused when asking the questions. In the future, I will try to clarify
better Question: my subform is currently based off of a query, how or
where would I incorporate your BETWEEN statement?

 

[John Vinson]

Question: my subform is currently based off of a query, how or
where would I incorporate your BETWEEN statement?

In the Criteria of the query. Bear in mind I don't know the structure
of your tables, or the nature of the query! Please post the SQL view
of the query, and some indication of what field or fields you would
like the date range to be applied to.

 

[RandyH]

In the Criteria of the query. Bear in mind I don't know the structure
of your tables, or the nature of the query! Please post the SQL view
of the query, and some indication of what field or fields you would
like the date range to be applied to.

John, here is the query from my form 'Operations':
SELECT Employees.EmployeeID, Employees.[Last Name], Employees.[First
Name], Time.PayPeriodBegin, Time.PayPeriodEnd
FROM Employees LEFT JOIN [Time] ON Employees.EmployeeID = Time.[Record
Number];
Here is the query for the subform 'Operations Sub Form':
SELECT Time.LaborDate, Time.LaborHours, Time.PayDate, Time.EmployeeID,
Time.AccountNumber, Accounts.[Account Description]
FROM Accounts INNER JOIN [Time] ON Accounts.AccountNumber =
Time.AccountNumber;

In my table 'Time' I have two other fields called, PayPeriodBegin and
PayPeriodEnd. These are the two other fields I would like to use to further
filter my form. Note: I do have these fields in my main form query, but that
didn't seem to work; they are still in the query but the fields have not
been placed on my form or subform yet.

 

[John Vinson]

John, here is the query from my form 'Operations':
SELECT Employees.EmployeeID, Employees.[Last Name], Employees.[First
Name], Time.PayPeriodBegin, Time.PayPeriodEnd
FROM Employees LEFT JOIN [Time] ON Employees.EmployeeID = Time.[Record
Number];
Here is the query for the subform 'Operations Sub Form':
SELECT Time.LaborDate, Time.LaborHours, Time.PayDate, Time.EmployeeID,
Time.AccountNumber, Accounts.[Account Description]
FROM Accounts INNER JOIN [Time] ON Accounts.AccountNumber =
Time.AccountNumber;

In my table 'Time' I have two other fields called, PayPeriodBegin and
PayPeriodEnd. These are the two other fields I would like to use to further
filter my form. Note: I do have these fields in my main form query, but that
didn't seem to work; they are still in the query but the fields have not
been placed on my form or subform yet.

This is rather odd. Why do you have the Time table in both the
mainform and the subform at all? Typically each Form would use its own
table; is the issue that you want the Form to display only those
employees who have Time records within the range? If so, a JOIN isn't
going to work as you expect. Each mainform record will refer to ALL
that employee's time records, so you'll see each employee listed
multiple times.

Also, you have two time fields (PayPeriodBegin and PayPeriodEnd), and
you apparently want to search for a range of times from StartTime to
EndTime. Which field do you want to search? What if StartTime is after
PayPeriodBegin but before PayPeriodEnd, for example?

ASSUMING that you want the form to show all employees, but the subform
to show only those records with PayPeriodBegin between the date/time
values in two form controls named txtStartTime and txtEndTime, you
could use:

SELECT Employees.EmployeeID, Employees.[Last Name],
Employees.[First Name] FROM Employees;

on the mainform and

SELECT Time.LaborDate, Time.LaborHours, Time.PayDate,
Time.EmployeeID, Time.AccountNumber, Accounts.[Account Description]
FROM Accounts INNER JOIN [Time] ON Accounts.AccountNumber =
Time.AccountNumber
WHERE [Time].[PayPeriodBegin] BETWEEN
[Forms]![formname]![txtStartTime] AND Forms![formname]![txtEndTime];

If you want to limit the mainform to those employees with Time records
within the range, you'll need a more specialized Subquery:

SELECT Employees.EmployeeID, Employees.[Last Name],
Employees.[First Name] FROM Employees
WHERE EmployeeID IN(Select EmployeeID From [Time] WHERE
[Time].[PayPeriodBegin] BETWEEN [Forms]![formname]![txtStartTime] AND
Forms![formname]![txtEndTime])


on the mainform, and your original query on the subform.

 

[RandyH]

John, here is the query from my form 'Operations':
SELECT Employees.EmployeeID, Employees.[Last Name], Employees.[First
Name], Time.PayPeriodBegin, Time.PayPeriodEnd
FROM Employees LEFT JOIN [Time] ON Employees.EmployeeID = Time.[Record
Number];
Here is the query for the subform 'Operations Sub Form':
SELECT Time.LaborDate, Time.LaborHours, Time.PayDate, Time.EmployeeID,
Time.AccountNumber, Accounts.[Account Description]
FROM Accounts INNER JOIN [Time] ON Accounts.AccountNumber =
Time.AccountNumber;

In my table 'Time' I have two other fields called, PayPeriodBegin and
PayPeriodEnd. These are the two other fields I would like to use to further
filter my form. Note: I do have these fields in my main form query, but that
didn't seem to work; they are still in the query but the fields have not
been placed on my form or subform yet.

This is rather odd. Why do you have the Time table in both the
mainform and the subform at all? Typically each Form would use its own
table; is the issue that you want the Form to display only those
employees who have Time records within the range? If so, a JOIN isn't
going to work as you expect. Each mainform record will refer to ALL
that employee's time records, so you'll see each employee listed
multiple times.

Also, you have two time fields (PayPeriodBegin and PayPeriodEnd), and
you apparently want to search for a range of times from StartTime to
EndTime. Which field do you want to search? What if StartTime is after
PayPeriodBegin but before PayPeriodEnd, for example?

ASSUMING that you want the form to show all employees, but the subform
to show only those records with PayPeriodBegin between the date/time
values in two form controls named txtStartTime and txtEndTime, you
could use:

SELECT Employees.EmployeeID, Employees.[Last Name],
Employees.[First Name] FROM Employees;

on the mainform and

SELECT Time.LaborDate, Time.LaborHours, Time.PayDate,
Time.EmployeeID, Time.AccountNumber, Accounts.[Account Description]
FROM Accounts INNER JOIN [Time] ON Accounts.AccountNumber =
Time.AccountNumber
WHERE [Time].[PayPeriodBegin] BETWEEN
[Forms]![formname]![txtStartTime] AND Forms![formname]![txtEndTime];

If you want to limit the mainform to those employees with Time records
within the range, you'll need a more specialized Subquery:

SELECT Employees.EmployeeID, Employees.[Last Name],
Employees.[First Name] FROM Employees
WHERE EmployeeID IN(Select EmployeeID From [Time] WHERE
[Time].[PayPeriodBegin] BETWEEN [Forms]![formname]![txtStartTime] AND
Forms![formname]![txtEndTime])


on the mainform, and your original query on the subform.

Thanks for your help John, I will give this a go later this week.