# Calculated Fields in tables

1. Novice
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2013
Posts
19

## Calculated Fields in tables

I have the following fields called [BDM Start Date], [Bronze], [Silver], [Gold], [Platinum]. [BDM Start Date] is manually populated by the user. Based on this I need to do a calculation for the remaining fields. The calculations I need are as follows:

For [Bronze] I need 6 months from [BDM Start Date] and then bump this to the first of the following month. To do this I used the following formula: =Str(Month(DateAdd("m",7,[BDM Start Date])))+"/1/"+Str(Year(DateAdd("m",7,[BDM Start Date]))) This formula worked in a Query but doesn't seem to work in a table.

For [Silver] I need 12 months from [BDM Start Date] and then bump this to the first of the following month.

For [Gold] I need 18 months from [BDM Start Date] and then bump this to the first of the following month.
For [Platinum] I need 24 months from [BDM Start Date] and then bump this to the first of the following month.

Any sugestions?

2. Functions don't work in table calculated field. Calculated field is intended for simple calcs like: Quantity * Amount.

Do complex calcs in query and use query as source for forms and reports. Or do the calcs in textboxes.

3. Novice
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2013
Posts
19
Thanks for your help. I will try this.

4. Novice
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2013
Posts
19
Ok I added the above to a query and was able to calculate the [Bronze],[Silver],[Gold], and [Platinum] fields. Now I need to run a query that calculates what the current level is based on todays's date. I have placed the following formula inside the same query:

Current: IIf(Date()<[Bronze],"Stanadard",IIf(Date()>=[Bronze] And (Date()<[Silver]),"Bronze",IIf(Date()>=[Silver] And (Date()<[Gold]),"Silver",IIf(Date()>=[Gold] And (Date()<[Platinum]),"Gold","Platinum"))))

However, when I run the query I get "Platinum" as my answer. Any suggesstions?

5. Don't use the Str() function to calculate dates. This means a string value compared to Date() and in this case there is a space in front of the year part. Use & instead of + for concatenation but the result is still a string value.

Use DateSerial() or CDate().

6. Novice
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2013
Posts
19
I will try this now. Thanks again.

7. Master of Nothing
Windows XP Access 2000
Join Date
Sep 2010
Location
Posts
8,860
I find it is easier to "fall through" to get a result. The formula is simpler.

You might try this:
Code:
`IIf(Date()>=[Platinum],"Platinum",IIf(Date()>=[Gold],"Gold",IIf(Date()>=[Silver],"Silver",IIf(Date()>=[Bronze],"Bronze","Stanadard"))))`
Given these dates,
[Platinum] = 6/30/2013
[Gold] = 5/31/2013
[Silver] = 4/30/2013
[Bronze] = 3/30/2013

If Date() = 7/1/2013, the result will be "Platinum" (Date() > [Platinum])
If Date() = 6/15/2013, the result will be "Gold" (Date() >= [Gold] and < [Platinum])
If Date() = 5/15/2013, the result will be "Silver" (Date() >= [Silver] and < [Gold])
.
.

My \$0.02

8. Novice
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2013
Posts
19
Ok I used the CDate function and that worked great for calculating my {Bronze],[Silver],[Gold],[Platinum] fields. However when I use the following formula in the same query to calculate my [Current] field it does not seem to calculate correctly.
IIf(Date()<[Bronze],"Standard",IIf(Date()>=[Bronze] And (Date()<[Silver]),"Bronze",IIf(Date()>=[Silver] And (Date()<[Gold]),"Silver",IIf(Date()>=[Gold] And (Date()<[Platinum]),"Gold",IIf(Date()>=[Platinum],"Platinum")))))

9. Try ssanfu's suggestion.

10. Novice
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2013
Posts
19
Currently I have the actual dates:
[Platinum] = 8/1/2014
[Gold] = 2/1/2014
[Silver] = 8/1/2013
[Bronze] = 2/1/2013

When I run the query using IIf(Date()>=[Platinum],"Platinum",IIf(Date()>=[Gold],"Gold",IIf(Date()>=[Silver],"Silver",IIf(Date()>=[Bronze],"Bronze","Stanadard")))) I get a value of "Gold" when it should be "Bronze"

11. The expression works in my test.

Here is my SQL:

FROM Table1;

Post your complete SQL statement or provide db for analysis. Follow instructions at bottom of my post.

12. Novice
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2013
Posts
19
I tried running the following formula in the same query that I used to calculate the [Bronze],[Silver],[Gold],[Platinum] fields. Does that cause a problem? I am not actually writing any code as I do not know how. Everything I am doing is inside a query.