Flagging

[kevmc]

Is there any way I can flag individual records so it shows when a course needs to be retaken? I have only found a way that will format all records. I need idividual records for each individual.

Thanks for your help,

kevmc

 

[namliam]

Anything is possible i allways say but ussually its best to post a question with examples or a sample db...

I have 2 tables... employees and courses with these fields ... i want to... bla bla

Regards

 

[ColinEssex]

Use a TickBox?

 

[kevmc]

Sorry about that.

The database contains 3 tables, Employees, Courses and Courses Attended. The sub form contains the fields, Emp ID, Emp Name Last, First, Course ID, Course Description, Units and Date. I need to flag up when a course is due to be retaken.
I hope this is enough info, if not, let me know what else you need.

Sorry for any inconvenience.

Regards,

kevmc

 

[namliam]

Seeing as you have the info on when what course was taken by Who. Now if you also know how long it takes before it has to be Retaken, then its "easy stuff"

For instance
Course: First Aid
YearsValid: 2

Name: John Doe
Course: First Aid
Taken: 25 aug 2001

Now joining the 2 tables and doing dateadd("yyyy",[YearsValid],[Taken])

This will then be the date to retake...

Regards

 

[kevmc]

Sorry about the delay, working away.

The tables are already joined.
Is that a formula that needs entering somewhere, if so where.

Thanks for your help.

kevmc

 

[namliam]

Zzzzzzz

Do you have fields like i suggested? The adjust to suite your needs.

Or is it totaly off, if so post a sample of your db or tell us/me the needed fields and i might be able to think something up....

Regards

 

[kevmc]

I'm very sorry if this is boring you namliam, but as I said, I've only just started using Access.

All I need to know is, where do I enter the formula:-
dateadd("yyyy",[years valid],[Taken]) ?

Thanks and sorry

kevmc

 

[namliam]

Your not boring me... We all have to start somewhere, dont we?

You need to put that formula into a query so you can show which courses are (about) to expire...

Regards

 

[kevmc]

namliam,
I have created a query with the Fields, Last Name, First Name, Course Description, Date Passed, Valid and Retake. When I run it I just get one row with no entries in it. The Date Passed field is Date/Time, the Valid field is Text and I entered the formula dateadd(“yyyy”,[Valid],[Retake]) into the Retake column of the query.

Can you give me any ideas what I’ve done wrong?

Once again, I apologise for my ignorance.

Thanks for your help

kevmc

 

[namliam]

what is the value of valid?

Can you post a sample of your DB?

Regards

 

[kevmc]

Sorry, I can't send a copy of the database.

The value in valid is 5. This would give a course lifetime of 5years.
I think I have managed to get it to work on a form by adding the formula to the Retake control in design view. Now I can't get the conditional formatting to work. A Query would be better.

Thanks for your help.

kevmc.

 

[namliam]

Try:

Retake: dateadd(“yyyy”,val([Valid]),[Date Passed])

Dont use spaces in your fieldnames... It bytes!

Regards

 

[kevmc]

sorry to inform you, but that doesn't work either. I've put the formula in the criteria row of the Retake field in the Query.

I'm getting as sick of it as you are now.

Thanks for your help.

kevmc

 

[namliam]

Retake: dateadd(“yyyy”,val([Valid]),[Date Passed])

If:
DatePassed = #01-01-2001#
valid = 2
Retake will be 01-01-2003

then add <=date() to the criteria and presto !

I will -now- even go as far as to post a sample for you...

SELECT YourTable.*, DateAdd("yyyy",[Valid],[DatePassed]) AS myRetake
FROM YourTable
WHERE DateAdd("yyyy",[Valid],[DatePassed]<=Date()

This will return all courses that have expired! (<=Date())

If you want to know in advance (lets say 1 month) the use DateAdd("m",1,date()) instead of date()

Regards