Expressions

[Ella1981]

Hi All!
I am pretty new to Access so I apologise in advance if this question is really basic but I can't seem to find any specific help.

I work for a training company and am setting up a new Access database to track our learners. I have set up a table to show when their qualification is expected to finish as each has a specific timescale.

Firstly, I wan't Access to calculate the expected end date based on the start date, how do I do this?

Secondly, I want Access to look at this expected end date and highlight to me in some way if it is overdue (as this affects our funding).

Is this at all possible? I'm sure it is but am finding the whole expression thing a bit confusing!!

 

[Beetle]

Firstly, I wan't Access to calculate the expected end date based on the start date, how do I do this?

You would typically do this as a calculated field in a query, or in the Control Souce of a calculated control on a form or report.

In a query it might look like;

DueDate: DateAdd("d", 30, [StartDate])

In the Control Source of a form/report control it would look similar but you would leave off the field alias (the value to the left of the colon) and precede it with an = sign;

=DateAdd("d", 30, [StartDate])

Secondly, I want Access to look at this expected end date and highlight to me in some way if it is overdue (as this affects our funding).

This would typically be done using Conditional Formatting in a form or report. Something like;

Expression Is: Date() > DueDate

Then you would set the controls background accordingly (or something similar).

 

[The_Doc_Man]

Ella, you posted this in tables. As you are a novice at Access, I understand. However, for future reference, Access isn't like Excel. You can do pretty much anything in an Excel worksheet including various formulas and such. Access is very different.

Tables can contain one thing and one thing only - data. No formulas, unlike Excel that can put spot formulas in any cell.

Queries can contain formulas that apply to every row in the underlying source of the query, unlike Excel that can put a formula on one row but not another.

There is a mind-set in Excel that very much differs from the mind-set required for Access.

What you asked for can be done many ways, but you need to keep ALL of the tools in the toolkit ready for use. Once you learn the proper way to use each of the major elements of Access, you will find your life a lot simpler. Please don't take this as an admonition, but as simple advice on studying the basics.

You might also want to search the forum for topics to see if anything useful comes up. The Search option in the ribbon at the top of the page works pretty well. Try it.

 

[Ella1981]

Thank you both for your help. I appreciate you pointing out the differences between Access and Excel. I agree it requires a totally different mindset. It is amazing what Access is able to do (if you know what you are doing)...I fear I am turning into an Access Geek!