How to use a control source and add an expression?

[rudycortez2]

I am building a data base for training, I want to be able to have a control source on a field in a form along with an expression for the field,The expression im using is "DateAdd("interval",Number,[Field]). I want to be able to type in a issue date in one field and have it automatically add the expiry to the other field as well as the query and table.

[NeoPa]

Set the .DefaultValue to the formula you want. You can also set it again in an AfterUpdate event procedure of the first control.

[rudycortez2]

The Data Entered in the Issue Field still does not calculate onto the Expiry field after changing the DefaultValue, and adding to AfterUpdate

[zmbd]

rudycortez2:
Is there some reason you need to store the expry date?
One can use a calculated field in a query (or an unbound control) to, well, calculate the date upon demand.

[rudycortez2]

Yes. I'm building a training data base that have classes from OSHA. The issue date and expiry date are very important. I have all the trainings in the same query and table. But have different forms for each. I also have a main training form that has all the trainings. What my end goal is to be able to just enter in the issue date and it will automatically enter the expiry date. And then I would like to build different queries for each training to filter out expired trainings.

[NeoPa]

Frustratingly, you don't seem to deal with the points raised.

My suggestion from post #2 would work. If you have a problem implementing that let us know what that problem is. Currently I cannot help you as you don't say what the problem is.

As for the question as to why you need an Expiry date, you answer that, but not in a way that answers the question. You only explain why an Expiry date is important. Not why it needs to be stored.

[zmbd]

rudycortez2
I understand what you are doing, OSHA requirements are something I deal with every day.

Lets say for example we are interested in Mr. Zulu's missing training, then we could use WHERE clause to return all passed dates that are a year or more older that todays date (or null/zls), just subtract one year from current date (or include a null check), this returns everything that is out of date and so forth.

If you want to see the retraining dates then a calculated field that adds a year to the passed date.

In neither case do I save these results, they are calculated on demand via the query.

This is one of the times that I would store the training date for audit reasons for every class taken and passed; however, that's database design.

[rudycortez2]

NeoPa:

Thats exactly it, when i entered the expressions, into the fields .DefaultValue, but it does not do the calculations. Nor does it store the information onto the Table or Query.

And it needs to be stored so i can run a query on it to search out of date training, and for my better half the Human Resources Director can understand how to use it, im simply a Safety Director with little experience with Access.

zmbd:

Yes i do not understand how to do the "Where". Someone else was working on this Database before me but quit, he was our tech guy. So im having difficulty figuring out expressions and how exactly to do all this.

So what i have tried so far is putting the expression into the control source, and the calculations did work, but did not store it to the table or query.

My next try was what Mr NeoPa recommended and the calculations did not work, it would only store what was manually typed it.

[zmbd]

ok,
I'm going to PM you my set of standard tutorials. In the long run these will greatly help you as you progress.

As for the query design, this diverges enough from the current thread that I think we'll need to split this thread into a new one.

This would be an example - there are many more

SELECT tbl_data.data_pk
   , tbl_data.data_text
   , tbl_data.data_timestart
FROM tbl_data
WHERE (
   ((tbl_data.data_timestart)
      >=DateAdd("y",1,Date())));

In the Query editor I dragged the fields:
data_pk/text/timestart into the grid
Below the data_timestart in the first criteria row I entered the calculation <=DateAdd("y",-1,Date()) (actually I would more than likely limit this further as every record with the date one year or more less than current will return ... 1908-2013)
But this will give you an idea...

I've a Christmas Party to get ready for, I'll send you the PM here this evening (my time (-_^) )once you work thru those tutorials you'll have the tools to handle this!
Also look for this thread to split at that point, I'll send the link.

[NeoPa]

RudyCortez2
"Thats exactly it, when i entered the expressions, into the fields .DefaultValue, but it does not do the calculations. Nor does it store the information onto the Table or Query."
That's not an explanation of your problem or the difficulties you encountered trying to get it to work. You don't include any code showing what you tried. You don't explain what was wrong with it other than stating it didn't work.

RudyCortez2
"And it needs to be stored so i can run a query on it to search out of date training"
That simply isn't true. Queries can be run that produce such a value using a formula. That is why I asked you the question. Because I knew that the answer was almost certainly that you have no reason to suppose it needs to be stored at all, and having to consider the question would make you realise you're on the wrong track with all this. Ignoring the question the first time simply meant that you didn't get the benefit I anticipated for you. It's never a good idea to ignore questions or suggestions from anyone you request help from. There's always a chance they may know what they're talking about and you'll miss something you need.

My advice would still be to avoid saving it anyway, but if you're still more comfortable with that approach in spite of all the advice given, then we still can help you, but you'll need to be a bit more forthcoming with detailed information to avoid tying our hands.

[rudycortez2]

NeoPA:

Okay, i understand where you're coming from. Okay my problem is when i use the expression DateAdd("yyyy",1[Field])in the .DefaultValue or the AfterUpdate, it does not calculate the expression.
And when you put it that way, i'm not sure what you mean by storing it?
I'm trying to make it so when i enter it into the Form, the calculated Date will show up in the Expiry Field. As well as the "Main" Form, Table,and Query that has all the training Dates and Expiry Date.
This is whats being used in the Query.

SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Contact Name], Employees.*
FROM Employees
ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));
 

So im not really sure how to do any of that.