Tech Support Guy banner

Access 2010 expression from form needs to reflect in table

1131 Views 6 Replies 2 Participants Last post by  hesykhia
I am super new to Access (like, what I know I taught myself or learned from some short youtube tutorials a few days ago), but I'm putting together a database for my reporting at work. This is the first time I've really run into an issue.

I am using Access 2010. I have a table called "Holds Table", and a form called "Holds Form". I have a field on both called "Fraud Status Date?", and a field called "Case Age".

"Fraud Status Date?" is, obviously, a date field, and the control source for that text box on my form references the correct field on my table.

"Case Age" is another matter. It has to calculate how many days have passed since the case was opened. Currently, the control source I have for that is "=DateDiff("d",[Fraud Status Date?],Date())", and it's working great.

However, the "Case Age" field in the table is blank, because no text box is pointing to it. I need to have the number from the form show up in the table, so that I can query it.

Any suggestions on how to do this? I'm not super solid with VBA, so if you have suggestions with that, please spell out exactly where I would click and what I would enter where (step by step, because I'm such a n00b). If I'm going about this backwards, or the wrong way, please give me alternatives!

I really appreciate ANY help you can give!
Not open for further replies.
1 - 7 of 7 Posts
First of all welcome to TSG.
Second well done in getting this far.
Last but least, what you have is the correct way to do what you want.
Let me explain, the case age this month could e say 60 days, if you store that in the table, next month it would be incorrect because it would then be 90 days, but the table would still say 60 days unless you overwrite it each time.
What you have here is a case of Dynamic Data and it should not be stored, but calculated as and when required.
But of course you want to use the this in reports, so the answer is to use your form's formula as a Column Heading of your report Query.
  • Like
Reactions: 1
Thank you so much! That has worked, and I'm so happy about it. I was very concerned that I wouldn't be able to figure out a way to query that field.

I do have a follow up question though. You know how I said that expression I have ("=DateDiff("d",[Fraud Status Date],Date())") is working great? Yeah, that isn't correct anymore for some reason.

It does calculate the way it's supposed to, but say I make a mistake in filling out the form, and need to change the Fraud Status Date to a different day. It will change, but the Case Age stays the same. So, basically, it was accurate for the first time I put it in, but doesn't update if I change the date after the fact. With several different people working out of this form, it is very likely to happen that someone puts in the wrong day, and it seems like they would have to start a new entry entirely. I'd like to avoid that if at all possible.

And an additional issue I have, in relation to this, is how to let the date update daily. It seems like it should, since it's "Date()", but I need to make sure that it does, because I need to query based on ">=83", and have it be accurate.

Any words of wisdom on these questions?
See less See more
You will find that it will update correctly the next time you open the form, however that is a bit disconcerting for the person puting the data in.
To overcome this you can use some VBA in the "After Update" Event Procedure either to "refresh" the Age or you can use VBA to do the calculation for you when the data is input or edited.

The Date() should always update daily as it is system generated.
  • Like
Reactions: 1
I have zero experience with VBA in Access. I've tried a bit, and have been fairly unsuccessful with it. Any pointers on the code I would use to 'refresh' it? And, it would go in the "After Update" slot on the "Fraud Status Date" object, right?
Correct, when you click on it select "Event Procedure" and then press the 3 small dots on the right hand side, that will take you to the correct place to enter the code.
The code would be

me.[Case Age].requery

assuming that Case Age is the name of the field.
  • Like
Reactions: 1
OBP - you, sir, are a godsend. THANK YOU SO MUCH.
1 - 7 of 7 Posts
Not open for further replies.