Date Functions in Access 1

[smileychickie]

I am trying to create a report that will allow me to show data from the current month, changing as the month changes. I am kind of new to Access so I can't quite figure this one out. Please help.

 

[Rohdem]

You need to build a criteria that filters for the current month. Create an expression in the query that returns only the month from your date

i.e.
mnth: datepart("m", [recorddate])

then in the criteria section for the expression 'mnth' put

datepart("m", date())

this will return all records from the current month. You can then use this query to build the report.
Mike Rohde
rohdem@marshallengines.com

 

[smileychickie]

Thanks but that didn't quite give me what I wanted. I have a table for my technicians' that shows how many hours they spent on each job. I need to be able to print a monthly report of the hours for each tech.

 

[WaltLukeIII]

If you are doing a report in the report or page footer use a total field
i.e.
the control source is
= Sum([column Name to total])

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[Rohdem]

First of all, looking at my post, I mistyped the first sentence. You need to build a query, not a criteria.

Now, if you want to break the records down or 'group' them by a field such as technician, you need to set the groupings on your report. When you are in the design view of the report, go to the 'View' menu and select sorting and grouping. Add the field that contains technician (or technician ID or whatever) to the list and select a sorting preference. Then at the bottom of the window, set group header and group footer to 'yes'. Now go back to your report. If you put the technician box in the 'technicianheader' area, then you will have a report that displays each technician and the jobs he or she has associated with it. Now in the 'technicianfooter', you can create a text box that has a control source like:
=sum([jobhours])
This will total the hours for each group (technician).
Now if you set the report's recordsource to a query that I discribed in the last post, you should have something close to what you want. Keep in mind that I generalized the field names and you will need to substitute your own field names to make it work.

Good Luck
Mike Rohde
rohdem@marshallengines.com