Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Expression Help (2002)

    I need help.... I have a database that I need to have calculate dates.
    I have never built an expression before. What I need is an expression to subtract from one field to populate the other automatically.

    Basically put, on the form, when the user types in the field 'end date' (ex. 6-1-06), the field '12 months' would have the value fill in automatically with the date 6 months earlier than what was entered into 'end date' (ex. 12-1-05).

    Is this possible, how would I write it and how would I build it in the expression builder?

    Thanks in advance.

    Ray

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

    Re: Form Expression Help (2002)

    Welcome to Woody's Lounge!

    You can type the expression into the Control Source property of the text box:

    =DateAdd("m",-6,[DateField])

    where DateField is the name of the date field. If you prefer to use the expression builder:
    - Click the builder button (the ... to the right of the Control Source property).
    - Double click Functions.
    - Double click Built-In Functions.
    - Select the Date/Time category.
    - Select the DateAdd function.
    - Click Paste. (See screenshot)
    - Click Help for information about the function and its arguments.
    - Replace

  3. #3
    New Lounger
    Join Date
    Feb 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Expression Help (2002)

    Hans...I could tell reading your other strings on here that you were very very smart....also very helpful....it worked!

    I have been knockin my head against the wall for a month trying to get this to work trying different expressions. The big thing was where you placed it....that was my shortcoming. You were the only one that broke it down into plain enough english for me and guided me to where to go....thank you very much!

    Re PS.... the data is only good for 18 months-thus the field [end date] and is removed after that date. However, after 12 months, the information is used differently for the last 6 months, so it is important to show when that date is which is what the [12 months] is for.

    Thank you ever so much again for your help.

  4. #4
    New Lounger
    Join Date
    Feb 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Expression Help (2002)

    Sorry to say...I have a new problem. I now get the information to do what I want in the forms, but its not showing up in the table.

    Is this going to be an either one thing or the other type deal or will I be able to get the information to kick back to the table in order to print it.

    Ray

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

    Re: Form Expression Help (2002)

    Since the date in the "12 months" text box is a calculated value, there is no need to store it in a table - it would be redundant information.

    Instead of calculating the value in a form, you could do it in a query based on the table, by adding a column

    DateMinus6Months: DateAdd("m",-6,[DateField])

    (replace DateMinus6Months with the name you want this column to have, and DateField with the name of the date field). You can use this query as record source for the form (with DateMinus6Months as control source of the "12Months" text box), and as record source for a report to be printed.

    If you really need the calculated value in the table, post back.

  6. #6
    New Lounger
    Join Date
    Feb 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Expression Help (2002)

    Thanks Hans....sorry for the delay in response. After I looked at it, I am not worried about it. I can just do the info search in the form.....Ray

Posting Permissions

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