View Full Version : Data Selection in Access

19-08-2004, 06:57 PM
I have built a database in Access to track cycling expenses, training etc. Two minor problems.

1.. The date is entered in the format ddmmyyyy. How can I set the criteria to select records for say 2003 when printing reports?

2.. Time. I record the time for training rides etc but have to enter the time as eg 90 for one and a half hours. Then when I print reports I just divide the figure by 60. Rough I know but I am no wizard with Access.

19-08-2004, 08:30 PM
Hi for the first one you create a query of all the fields you need for the report. Before saving the query enter the following in a blank field row where YearOnly will be the name of the expression field and [Date] is the name of your field that holds the dates. You then put 2004 in the criteria for this new field.

Field Row has... YearOnly: Year([Date])
Criteria Row has.... 2004

You now use this query as the source data for the report.

For the 2nd question open the Table in design view. You can change the data type to Date/Time and amend the format property (General tab) to "mm:ss" (include the quotes). Note that if your field isnt already a date/time then you may lose some data so create a new field then transfer old data to the new field so you dont lose your times.


19-08-2004, 08:58 PM
An alternative option to parry's suggestion for selecting dates is to use something like the following in the criteria section under "date" in your query :

>=#01/01/04# And <=#31/12/04# - this will select all data for the 2004 year - even if you format the date field in the table as ddmmyyyy. Doing it this way allws you to select dates for whatever range you want and you are not restricted to look at one year at a time. If the date range for the query is constantly changing then you can enter the dates on the fly when you open the query by using something like the following in the criteria field :

>=[Enter start date] And <=[Enter end date] - provided you enter the start and end dates in the correct format then you can pick any date range you want on the fly.

With parry's 2nd suggestion you can also do hh:mm:ss - but be aware that if you use either format for the times then Access will force you to enter the times with the semi-colon (there are ways around that but we won't go there yet).