Selecting data between to specific dates in a form

[Guest]

Sorry if this is an easy one but I am new to this area.

I have created a query from our SQL database that list all hours booked by
eack employee and the date booked, from when we 1st started collectin thi
information.

I am wanting to create a form that anyone can go into and select a date
range so that the can calculate the hours between these dates.

Th other problem that I have got is that the date are in number format and
not in date. How dan I format this correctly?

Regards

 

[Allen Browne]

The first thing to do will be to convert your number field into a real
date/time field.

To do that:
1. Open your table in design view.

2. Add a new field, of type Date/Time.

3. Save the table. Close.

4. Create a new query using this table.

5. Change it to an Update query (Update on Query menu, or on the ribbon in
A2007.)

6. Drag the new date field into the grid.

7. In the Update row, enter an expression that parses the number correctly.
For example, if your have the number 20071231 for the last day of this year,
use:
DateSerial(Left([f],4), Mid([f],5,2), Right([f],2))
where you replace the "f" with your field name.

8. Run the query. Verify that the dates are right.

9. Open your table in design view, and delete the numeric field.

Now that you have a real date/time field, you can add text boxes to your
form, and filter it ot the date range and employee you want. For an example
of how to write the code to filter the form, see:
Search form - Handle many optional criteria. Sample database
at:
http://allenbrowne.com/ser-62.html

Now the form is filtered, you can add a text box in the Form Footer section
can then give you the total. For example, if you have a numeric field named
Hours, the Control Source of the text box in the form footer would be:
=Sum([Hours])