# 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?  Reply With Quote

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.  Reply With Quote

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

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?  Reply With Quote

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().  Reply With Quote

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

7. Master of Nothing Windows XP Access 2000           Join Date
Sep 2010
Location
Posts
8,459
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  Reply With Quote

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")))))  Reply With Quote

9. Try ssanfu's suggestion.  Reply With Quote

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"  Reply With Quote

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.  Reply With Quote

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.

When I do the above it returns everything with a value of "Gold" or "Platinum"  Reply With Quote

13. That's what the query I posted does - calculates all in one. Again, if you want to provide db for analysis, follow instructions at bottom of my post.  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Tech Forums: Microsoft Office Forums