Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Question Unanswered: IF Statement Help

    I have two fields in the same table..
    As the user enters in data on the form,
    If Field 1 is a negative amount...

    I want it to put the amount into Field 2, if not, just put a $0.00 in Field 2

    Here is what I have, but it gives me a --- #Error message, any thoughts?

    IIf([TotalPaid]<0,[Overpaid]=[TotalPaid],[Overpaid]=0)

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Smile Try it this way

    IIf([TotalPaid]<0,[Overpaid]=[TotalPaid],[Overpaid]="0.00")

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Thank you that seemed to work!

    One more question, I notice that if I am using 3 fields on a form,
    Field 1 is a deposit amount the user enters,
    Field 2 is a payment amount the user enters,
    Field 3 is a sum of {Field 1 + Field 2} and it displays correctly on the form..

    Problem is, how can it store it in the Field in the Table??

    Am I confusing Bound and unbound?

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by databasemon
    Thank you that seemed to work!

    One more question, I notice that if I am using 3 fields on a form,
    Field 1 is a deposit amount the user enters,
    Field 2 is a payment amount the user enters,
    Field 3 is a sum of {Field 1 + Field 2} and it displays correctly on the form..

    Problem is, how can it store it in the Field in the Table??

    Am I confusing Bound and unbound?
    Not meaning to question your design intentions, but it is generally not considered good design practice to store values that can be calculated, from data that is already stored, in a table.

    That being said, you can create a recordset and move the cursor of the recordset to the current record on your form. Then just use syntax such as:

    .Edit
    .Fields ("FieldName") = "value from your text box"
    .Update

    Good luck.

    Gregg

  5. #5
    Join Date
    Nov 2003
    Posts
    300
    I guess that maybe I am trying to do that,,,as I thought I needed to store it so I could reference the amount in a report or other form...

    So, will the procedure you mentioned allow me to store the value in a Total Field in the Table? Where do I put that ?

    I wanted to refer to that same total amount in some reports and other forms, so I don't know how to reference it.?

    Please help-Thanks...
    Last edited by databasemon; 11-19-03 at 13:22.

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by databasemon
    I guess that maybe I am trying to do that,,,as I thought I needed to store it so I could reference the amount in a report or other form...

    So, will the procedure you mentioned allow me to store the value in a Total Field in the Table? Where do I put that ?

    I wanted to refer to that same total amount in some reports and other forms, so I don't know how to reference it.?

    Please help-Thanks...

    Might be easier for you to just create a query that calculates the value for you. You could create a calculated field in the queries that feed your reports or if it is just a few reports, you can create the calculated field in the reports themselves.

    If you have a lot of reports with a lot of different recordsources feeding them, then the table method isn't always a bad idea.


    As far as where you would put the procedure to perform the table update, you can do it in any number of places in your form. You could use the after update event of the last text box entered. You would want to confirm that the textboxes were filled with valid data first. You could use a command button that explicitly performs the action. Take your pick.

    I personally like to put calculations in the reports themselves or the query that drives them.

    Gregg

  7. #7
    Join Date
    Nov 2003
    Posts
    300
    Thank you very much, I just have a few reports and I will use the calculations withing the reports to do as you say.

Posting Permissions

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