Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 12th, 2004, 07:08 AM
Authorized User
 
Join Date: Dec 2003
Location: Ravenshead, Notts, United Kingdom.
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?

Reply With Quote
  #2 (permalink)  
Old January 12th, 2004, 02:38 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
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
Default

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
Reply With Quote
  #3 (permalink)  
Old January 21st, 2004, 06:07 AM
Authorized User
 
Join Date: Dec 2003
Location: Ravenshead, Notts, United Kingdom.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 02:54 AM.


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