Query only certain dates

ozzy68

Registered User.
Local time
Today, 05:25
Joined
Dec 19, 2011
Messages
28
I am new to this forum and reletively new to Access as well, please by patient.

I am attempting to display a field called "DueDates" based off of data located in another Field called "CourseCompletionDates", I know how to advance every record by a determined amount but what I am trying to due is have it advance the date for some records by one year, others by two years, etc. The unique identifier is the field called "CourseID". Any help would be appreciated.
 
How do you know how to advance a date? What determines if a specific date is to be advanced 1 year, 2 years or etc years?
 
Thanks for the reply,
I was able to figure it out shortly after I posted the question. The course ID field is unique and identifies which courses are good for one, two, or three years. I had to use an "iif" statement: iif([CourseID]="ANN001ME2",[CoursecompletionDate])+(365)

They only values that change are the course ID's or the number of days at the end.
 
@plog:
I'm guessing I'm right in saying it would normally be better to make use of a table of courses & the corresponding increase in date for each rather than rely on a long Iif for this? Would mean if criteria for date changes would be easier to alter at a later date, no?
 
@student:
I was thinking the same thing. In fact, I'm working on that now, just having a tough time because of the amount of historical data because it causes tons of duplicate date in the tables.
 
Any ideas why it contains duplicate dates? If you're trying to pull this data into a table you'll be able to eradicate duplicates on import I would presume. Or does it already exist in table form?
 
The dates duplicate because there are several employees that have attended the same class of the past five years, multiply that by 100+ courses and that will give you an idea of what I'm looking at. I'm trying to narrow it down by splitting the table while keeping related information connected to the employee, as it stand I'm working with approximately 30K records.
 
Ah, I think I see the confusion. I meant a separate table with a list of all course codes in one field and the relevant increase (one year, two, etc) in the next field. This could then be linked to the original table in a report by course ID to calculate the new due-date for each course for each individual. The query does all the work for you. This way if the length of time before a specific course is due changes you can just change the relevant entry in the course length table. Make more sense now?
 
Yes, I understand now..thanks, I'll give that a try.
 
Most welcome. Come back if you're having any problems :-)
 
@student, okay got the tables set p and working properly but now I have a new problem, it is calculation due dates for really old data as well. How to express "no older than three years from today"?
 
That will depend upon the structure and fields used for the dates, but would be along the lines of criteria placed against the relevant date marking which records to update/display - CourseCompletionDates I presume. Criteria in Design view would be similar to >Date()-1095 for a rolling three year period from whenever your query is run (assuming 365 days/yr), or >#21/12/2008# for records after a fixed date (3 yrs from today). The date needs to be in the same format as that stored for the original data. Note the #-marks around the date are required for it to recognise this as a date in criteria if using a fixed date. Also bear in mind Access reserved words (i.e. Date) should not be used for field names.
 
So did that solve the problem for you? How did you get on?
 

Users who are viewing this thread

Back
Top Bottom