Results 1 to 13 of 13

Calculated Fields in tables

  1. #1
    dharsh is offline 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. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,257
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

  4. #4
    dharsh is offline 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. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,257
    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().


    CDate(Month(DateAdd("m",7,[BDMStartDate])) & "/1/" & Year(DateAdd("m",7,[BDMStartDate])))


    DateSerial(Year(DateAdd("m",7,[BDMStartDate])),Month(DateAdd("m",7,[BDMStartDate])),1)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    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
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #8
    dharsh is offline 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. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,257
    Try ssanfu's suggestion.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    dharsh is offline 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. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,257
    The expression works in my test.

    Here is my SQL:

    SELECT BDMStartDate, CDate(Month(DateAdd("m",19,[BDMStartDate])) & "/1/" & Year(DateAdd("m",19,[BDMStartDate]))) AS Gold, CDate(Month(DateAdd("m",13,[BDMStartDate])) & "/1/" & Year(DateAdd("m",13,[BDMStartDate]))) AS Silver, CDate(Month(DateAdd("m",7,[BDMStartDate])) & "/1/" & Year(DateAdd("m",7,[BDMStartDate]))) AS Bronze, CDate(Month(DateAdd("m",25,[BDMStartDate])) & "/1/" & Year(DateAdd("m",25,[BDMStartDate]))) AS Platinum, IIf(Date()>=[Platinum],"Platinum",IIf(Date()>=[Gold],"Gold",IIf(Date()>=[Silver],"Silver",IIf(Date()>=[Bronze],"Bronze","Stanadard")))) AS Class
    FROM Table1;

    Post your complete SQL statement or provide db for analysis. Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    dharsh is offline 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.

    IIf(Date()>=[Platinum],"Platinum",IIf(Date()>=[Gold],"Gold",IIf(Date()>=[Silver],"Silver",IIf(Date()>=[Bronze],"Bronze","Stanadard"))))

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

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,257
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculated fields
    By FinChase in forum Queries
    Replies: 5
    Last Post: 02-02-2012, 06:12 PM
  2. Calculated fields across tables and forms
    By drrob1983 in forum Queries
    Replies: 4
    Last Post: 09-15-2011, 09:41 AM
  3. Calculated fields
    By graciemora in forum Forms
    Replies: 1
    Last Post: 02-04-2011, 06:07 AM
  4. calculated fields
    By nashr1928 in forum Forms
    Replies: 1
    Last Post: 07-21-2010, 04:49 PM
  5. Sum of calculated fields
    By nkuebelbeck in forum Forms
    Replies: 9
    Last Post: 03-12-2010, 01:32 PM

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