Calculation Field in table vs Query calculation

[Leonidsg]

Hello I have a question on calculations based on dates in a table. I have two dates: Date of Birth: [DOB], and Date of Diagnosis: [DateDx]. I want a third field to calculate Age at Diagnosis by taking the difference between those two. It would be best if I could display that number in years and months. So I was wondering:

1. Do I use a calculation field in a table or a query instead?
2. If I use a query does this stress the system too much?
3. With a query I tried a DateDiff("yyyy", [DOB], [DateDx]) equation but it is saying there is a syntax error, comma is/isn't where is should be. Also using this I would only get years not years and months.

Any help with be great thank you.

Lenny

[RuralGuy]

Try using this in your query:

DateDiff("yyyy", [DateDx], Date())+ Int( Format(Date(), "mmdd") < Format( [DateDx], "mmdd") ) As TheAge

[SteveH2508]

As I say to my students - it is a computer - it is designed to compute things - it does it way better and way faster than you can!

Don't worry about stressing the system - it will barely notice a calculation like this.

[ketbdnetbp]

Leonidsg -

I agree with the OP, the calculation is best suited in a query. As a follow-up to RG's suggestion that addresses this issue based upon the current date, I encountered a similiar situation relative to age (years and months), as of a specific date for a 401(k) calculation; I used the following approach and it may fit your needs. Create a query based upon the table that contains those dates.

In a new column of that query, add the following to the field line (returns total months between the dates):
Months: IIf((DatePart("d",[DOB])<=DatePart("d",[DateDx])),(Int(DateDiff("m",[DOB],[DateDx]))),(Int(DateDiff("m",[DOB],[DateDx])-1)))

Then, in another new column, add this to the field line (this will return months not divisible by 12 , i.e. remaining months):
RM: Int([Months] Mod 12)

Last, in another new column, add this to the field line (just to make it an easier read):
AgeAtDiagnosis: Int([Months]/12) & " Years, " & [RM] & " Month(s)"

I used three columns for illustrative purposes. There are probably ways to use fewer columns but, it was easier for me to use three. Test to see if this gets you the desired results.

All the best,

Jim

[Leonidsg]

Try using this in your query:

DateDiff("yyyy", [DateDx], Date())+ Int( Format(Date(), "mmdd") < Format( [DateDx], "mmdd") ) As TheAge

I still need a little help. I have two variables DOB and DateOfSurgery. Where do I put the DateofSurgery variable in the above equation. Thank you for all your help.

I didn't see Ketbdnetbp post, let me try that first. Thanks again for all the help!!!

[RuralGuy]

You should just be able to replace [DateDx] with [DateofSurgery].

[RuralGuy]

Actually, I don't think that is correct. I need to think about it for a minute.

[RuralGuy]

I think it is going to be:
DateDiff("yyyy", [DateOfSurgery], [DOB])+ Int( Format([DOB], "mmdd") < Format( [DateOfSurgery], "mmdd") ) As TheAge
...but I might have one of the dates backwards.

[Leonidsg]

So when I am trying to use your formula everything is fine until I input AgeAtDiagnosis: Int([Months]/12) & " Years, " & [RM] & " Month(s) I am told that there is an extra ")" in the equation. Any suggestions.

Thank you

Leonidsg -

I agree with the OP, the calculation is best suited in a query. As a follow-up to RG's suggestion that addresses this issue based upon the current date, I encountered a similiar situation relative to age (years and months), as of a specific date for a 401(k) calculation; I used the following approach and it may fit your needs. Create a query based upon the table that contains those dates.

In a new column of that query, add the following to the field line (returns total months between the dates):
Months: IIf((DatePart("d",[DOB])<=DatePart("d",[DateDx])),(Int(DateDiff("m",[DOB],[DateDx]))),(Int(DateDiff("m",[DOB],[DateDx])-1)))

Then, in another new column, add this to the field line (this will return months not divisible by 12 , i.e. remaining months):
RM: Int([Months] Mod 12)

Last, in another new column, add this to the field line (just to make it an easier read):
AgeAtDiagnosis: Int([Months]/12) & " Years, " & [RM] & " Month(s)"

I used three columns for illustrative purposes. There are probably ways to use fewer columns but, it was easier for me to use three. Test to see if this gets you the desired results.

All the best,

Jim

[Leonidsg]

Okay i think i was able to figure out the parentheses but now it prompts me for a parameter value for Months and RM? Thank you

Leonidsg -

I agree with the OP, the calculation is best suited in a query. As a follow-up to RG's suggestion that addresses this issue based upon the current date, I encountered a similiar situation relative to age (years and months), as of a specific date for a 401(k) calculation; I used the following approach and it may fit your needs. Create a query based upon the table that contains those dates.

In a new column of that query, add the following to the field line (returns total months between the dates):
Months: IIf((DatePart("d",[DOB])<=DatePart("d",[DateDx])),(Int(DateDiff("m",[DOB],[DateDx]))),(Int(DateDiff("m",[DOB],[DateDx])-1)))

Then, in another new column, add this to the field line (this will return months not divisible by 12 , i.e. remaining months):
RM: Int([Months] Mod 12)

Last, in another new column, add this to the field line (just to make it an easier read):
AgeAtDiagnosis: Int([Months]/12) & " Years, " & [RM] & " Month(s)"

I used three columns for illustrative purposes. There are probably ways to use fewer columns but, it was easier for me to use three. Test to see if this gets you the desired results.

All the best,

Jim

[ketbdnetbp]

Leonidsg -

Cannot replicate the problem as the query seems to work for me. However, because [Months] and [RM] are dependent, I would double check the syntax in the [Months] field. It should be:

Months: IIf((DatePart("d",[DOB])<=DatePart("d",[DateDx])),(Int(DateDiff("m",[DOB],[DateDx]))),(Int(DateDiff("m",[DOB],[DateDx])-1)))

...Change field names if needed.

The SQL for the query that works for me is:

SELECT Table4.ID, Table4.Name, Table4.[DOB], Table4.DateDx, IIf((DatePart("d",[DOB])<=DatePart("d",[DateDx])),(Int(DateDiff("m",[DOB],[DateDx]))),(Int(DateDiff("m",[DOB],[DateDx])-1))) AS Months, Int([Months] Mod 12) AS RM, Int([Months]/12) & " Years, " & [RM] & " Month(s)" AS AgeAtDiagnosis
FROM Table4;

I have also attached my test db, hope this helps,

Jim

[Leonidsg]

Hey Jim I very much appreciate your help. I downloaded your database and looked at mine and I was able to get the same result. However I would like the Age to appear in the original table, so in your example that would be table 4. Any suggestions of how to add this to the original table? Or am I confused.

Thank you
Lenny

Leonidsg -

Cannot replicate the problem as the query seems to work for me. However, because [Months] and [RM] are dependent, I would double check the syntax in the [Months] field. It should be:

Months: IIf((DatePart("d",[DOB])<=DatePart("d",[DateDx])),(Int(DateDiff("m",[DOB],[DateDx]))),(Int(DateDiff("m",[DOB],[DateDx])-1)))

...Change field names if needed.

The SQL for the query that works for me is:

SELECT Table4.ID, Table4.Name, Table4.[DOB], Table4.DateDx, IIf((DatePart("d",[DOB])<=DatePart("d",[DateDx])),(Int(DateDiff("m",[DOB],[DateDx]))),(Int(DateDiff("m",[DOB],[DateDx])-1))) AS Months, Int([Months] Mod 12) AS RM, Int([Months]/12) & " Years, " & [RM] & " Month(s)" AS AgeAtDiagnosis
FROM Table4;

I have also attached my test db, hope this helps,

Jim

[ketbdnetbp]

Leonidsg -

Unless there is a compelling reason to the contrary, calculated fields are best suited in a query. On the db I sent to you, the calculation is in the query named "Table4 Query1". However, if it is absolutely necessary, add a text field to the original table named AgeatDiagnosis and perform the calculation at the form level using VBA to set it's value after the the DateDX is updated.

All the best,

Jim

[Leonidsg]

Jim,

So when people are looking at the data is the best way for them to see the calculation from the field in a report? The people who will actually will use the database will not know to look at the query table to find the calculated field.

Thank you,
Lenny

[ketbdnetbp]

Leonidsg -

The calculation is conducted in the query. However, the result can be pulled from the query and used in a form, report, or elsewhere as needed. Display and use of its value really depends on where it serves the business purpose best.

Jim