if...then logic in fields

[phs]

Recently I have been creating a database in Access 2007 that looks at the end date of certain events and then compare it with todays date in order to determine whether or not the event can actually be participated in.

If the current date is before the end date, then I would like the field to display "current." On the same note, I would like the field to display "passed" if the current date is past the end date.

Normally I understand that this issue would normally be solved using forms, reports, or queries, but I was wondering if it would be possible to do this at the table level. Also, I am hoping to solve this problem by adding another field to the table that the dates are stored in, thus making the navigation less complicated.

Please note: I am new to access and do not completly understand the syntax or logic of SQL and Visual Basic. If possible could you explain the tech terms and logic that you are using.

Thank you for your help

 

[Mr. B]

I don't feel that using a Calculated Field in an Access table is the place to do what you are trying to do.

I would suggest that you take a look at this link and then decide for yourself
http://allenbrowne.com/casu-14.html

In this page Allen Browne talks about how the results of a calculated field are not reliable and are not recalculated after data is initally entered. I would think that in your situation this would rule out the use of the calculated field.

The place to do this type of thing is, as you have already stated, in forms or queries and not at the table level.

 

[Access_guy49]

What is the reason for wanting to do it at the table level?
You need to really make sure you have a NEED for that because it will require a daily recalculation of all records that are new or "current"
storing a "current" value would only be valid for the day with which it was calculated.
If you want it to be current every time you open the database, then really this is "calculated data" and should only be viewed in a query form or report calculated on the fly and not stored as a value within your database.

 

[phs]

Mr. B, thank you for your help, that website looks very useful.

I would like to do this on the table level because I eventually display that information within queries and forms. I personally would perfer to have the "current" field be stored with the rest of the data. However, if that is not possible, how would I create a similar effect within the query or form level?

 

[Mr. B]

In my opinion, you really should only do this at the query, form or report level. If you are using bound forms then everthing can be done at the query level and the use the query as the record source for both forms and reports.

Good luck with your project.