Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    24

    Unanswered: bound field on a form to be the sum of four other fields

    hello

    i've got a field1 on my form. i want to be field1 = fieldA + fieldB + fieldC + fieldD for every record that has an ID number higher than 1500. Record below that already have data in the field1 field and don't need to be the sum of the 4 fields.

    in my context:
    I've got a Name field at the top of the page. This Name field has to be compiled of surname, first name, academic title, and mr./mrs. ..., that has to be entered below. But for the first 1500 records it has been entered seperately and sometimes there's data in the name field that cannot be broken into the 4 fields. So only for the new data this has to be the sum of the four fields.

    So can anyone help me accomplish this?

    Thanks!

  2. #2
    Join Date
    Sep 2004
    Posts
    8

    may not work for you but...

    Might think about updating the table so that all fields are alike by either seperating into fields or running them into one field. Use an update query to only select those 1500 records in the criteria and update the field based on a string to & them together so that all records are alike. Might want to try this on a make table query first to see if the field looks good, then use the update query. This way you would have consistant reports without trying to do the same thing in the design of the report.

  3. #3
    Join Date
    Sep 2004
    Posts
    24
    ok thank you for the reply!!

    i am now trying a similar idea
    i created 2 queries:
    1 select query that justs selects the "name" field from the table for the first 1500 records, 1 update query for record 1501-... that changes name into the join of the 4 other fields.

    Then i was thinking on creating two text fields for the form that run on the queries, and writing some vba code that enables the first for the first 1500 and the second for 1501 - ... . This way the information for the old and the new data both get saved in the "name" field of the table. But I don't know if this is a good idea, I am not very experienced and don't know if you can run a text field on a action query...

  4. #4
    Join Date
    Sep 2004
    Posts
    8

    Might work...

    Don't know. I have never tried that before. I have always tried to keep data in field of the same context. You might run into a possibility of a duplicate record entry if the information is entered differently for the same "Name"; even with your "Name" field as the Key field. Hope it works out for you.

  5. #5
    Join Date
    Sep 2004
    Posts
    24
    hello,
    I think I've found a solution, I wrote this little piece of code:

    If ID.Value >= 1720 Then
    Me.field1.Value = [fieldA] & " " & [fieldB] & " " & [fieldC] & " " & [fieldD]
    Else
    End If


    this keeps the old data as it is, and the new data is as I want it, plus it is also saved correctly in the table...

    Think this is correct or do you see any errors ??

Posting Permissions

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