Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Location
    Duluth, Minnesota, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Expression (Access 97)

    I'm trying to alter a field in an existing database so the person entering data can choose 1 of 3 options, and in another field a date is calculated and inserted. My current database does this with 2 options...the person chooses 6 months or 1 year, and the date 6 months or 1 year beyond is inserted in another field. Here is my current code to do this: PaidThrough: IIf([Member Type]="6 months",DateAdd("m",6,[Last Paid]),DateAdd("m",12,[Last Paid])).
    Now I'm adding a 3rd option - 1 month - and I'm not having any success. Here is the closest I've come: PaidThrough: IIf([Member Type]="1 month",DateAdd("m",1,[Last Paid]) or If[Member Type]="6 months",DateAdd("m",6,[Last Paid]),DateAdd("m",12,[Last Paid])) but it says my operators and operands aren't in order. I've tried many different Ifs, elses, or options and none have worked. Any suggestions? I'm thinking I'm missing something simple.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Expression (Access 97)

    Hi Deebock,

    Try the following expression instead:

    IIf([Member Type]="1 month",DateAdd("m",1,[Last Paid]),IIf([Member Type]="6 months",DateAdd("m",6,[Last Paid]),DateAdd("m",12,[Last Paid])))

    or

    DateAdd("m",IIf([Member Type]="1 month",1,IIf([Member Type]="6 months",6,12)),[LastPaid])

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access Expression (Access 97)

    >>Any suggestions? I'm thinking I'm missing something simple.<<

    I have a suggestion. Don't store that calculated date in your database! This is a clear violation of database normalization rules. Why not change your MemberType field to an integer (dropping the "months"). Then whenever you need to calculate the PaidThru value, you can do a simple calculation: DateAdd("m",[MemberType],[LastPaid])
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Jul 2003
    Location
    Duluth, Minnesota, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Expression (Access 97)

    Thanks Hans...your first expression did the trick!

  5. #5
    New Lounger
    Join Date
    Jul 2003
    Location
    Duluth, Minnesota, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Expression (Access 97)

    Mark,
    We're wondering why you feel this is a violation of database normalization? The field we're calculating is the expiration date of the member, calculated by what type of member they are and when they paid their membership fee. Only one date is stored at a time and it changes each time they renew their membership. Comments?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Expression (Access 97)

    Do you have the date paid stored? What Mark is talking about is that if you have the date paid stored as well, the storing the expiration date as well is unnecessary because it can always be calculated. If you store only the expiration date, then it is not a violation of normalization.
    Charlotte

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access Expression (Access 97)

    Because you are storing a value in a field that is derived solely from a calculation involving other fields in the record. The inherent risk is that you must remember to recalculate this field whenever ANY of the underlying fields get changed; and the problem is that fields don't always get changed via a single form that you can control. There may be multiple forms, update queries, etc. Therefore, the PaidThrough date in your database may not always be correct, whereas deriving it when needed will ALWAYS be correct.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    New Lounger
    Join Date
    Jul 2003
    Location
    Duluth, Minnesota, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Expression (Access 97)

    I see what you mean... Thanks for the refresher on normalization rules.

  9. #9
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Expression (Access 97)

    In a case like this wouldn't it be a good idea to store the date so that if the calculation method does change it will not try to alter an already agreed expiration date?

    Peter

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access Expression (Access 97)

    I presume they have stored the DatePaid and the Months. I'm not sure what can change that would alter the calculation of the expiration date? If, for example, they change the number of months (let's say everybody is getting a free month), then you want the expiration date to change automatically.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access Expression (Access 97)

    But maybe not the previous ones.

  12. #12
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access Expression (Access 97)

    If in fact they are storing previous values, they should be in separate records. And you still calculate the DatePaidThru the same way for each record.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access Expression (Access 97)

    It's easy to overlook the rules, mostly because it is easier to do the calculation on data entry and store the value, rather than having to do it every time. And we all have done this at one time or another for various reasons (some good, some bad). But you just need to recognize when you are storing such calculated data, even if you then make a conscientious decision to go ahead anyway. But keep it quiet, so the normalization police won't come after you!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •