Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 12th, 2004, 07:08 AM
Authorized User
Join Date: Dec 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date query in Access

I have a database where the records are for members who have to attend a number of events each 12 months. The start date for each 12 month period depends on their job tile. Thus members with job title "A" will start their event year on say 30 October in each year. Members with a different job title might start their event year on the 30 September of each year.

Each members record has a field (which is a look up) called 'YearStart' which is a text field and has an input mask _/_/. A typical entry would be 30/10/.

There is a field for the date of the Event ('EventDate')which is a Date field.

I require to produce a query which shows all the events attended by a Member within his current year. So, if the Members YearStart is 30/10/, and the date the report is required is November 2003, the query will need to identify those courses the Member has attended between 30/10/2003 and 30/10/2004. Can anyone help?

Old January 12th, 2004, 02:38 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

I would not use YearStar you used. I would have two fields: intStartMonth and intStartDay (both are type Integer). For each title I would set both of them, e.g. intStartMonth = 10, intStartDay = 30.

Then to make the comparison use these in a query or in code:

dtmStartDate = DateSerial(Year(Date()), intStartMonth, intStartDate)
dtmEndDate = DateAdd("yyyy", 1, dtmStartDate)

I assumed you always want to check during the same year you run the query, e.g. Year(Date()). If not, change that to the correct year.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Old January 21st, 2004, 06:07 AM
Authorized User
Join Date: Dec 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts

Thank you SerranoG - I have changed the structure of the member's records so that I can use your suggestions. I have decided to use code rather than a query, and as the check date will not always be the same as the start date I have tested for that by ensuring that the start date year is not greater than the current year - if so the start date year is simply reduced by 1.

Thanks for your help.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Syntax to query Date/Time in MS ACCESS chiuyianl Access 4 March 31st, 2005 09:08 AM
Query by date dsealer Access 11 November 9th, 2004 09:47 PM
Date Query sefrank@onebox.com Classic ASP Databases 4 January 28th, 2004 07:27 PM
Date based query when date is nvarchar MichaelTJ SQL Language 4 January 12th, 2004 09:57 PM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.