The first thing to do will be to convert your number field into a real
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
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,
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
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:
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"The Rook" <TheRook@discussions.microsoft.com> wrote in message
> 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
> 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?