Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    7

    Unanswered: fill field based on another field

    I have a small application I am building and need to have a field in the same table store the value based on what is entered.

    I have field called #units. then have another one that is the value of #units/ 30 minutes. I need the total field to show the return value. What is the best way to do this in Access 2007?

    thank you in advanced.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Preferably on a form. I like to write vba code to set the value (I'll put the total field on the form) once the other values are gotten (not null), that value is written to the field. You could also write a query to the job.

    DON'T try to put this logic into the table design! That's a bad idea.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2009
    Posts
    7
    Ok will see what I can do. The data is coming from a Excel doc that I am importing and would have to be imported every day. It has a column setup to do this, I just want to have a cleaner veiwing application for the single document. Thank you.

  4. #4
    Join Date
    Feb 2009
    Posts
    7
    ok well not having much luck with getting this figured out. I am new and learning so be kind with the wisdom that might be shared. I can not see how to do a query for this. I have tried playing with different things with no luck.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To store a calculated value in a field on a form:

    1. Add all your fields to the form (ie. FieldName1, FieldName2, and TotalField).
    2. In the AfterUpdate event of FieldName1 and AfterUpdate event of FieldName2, add code like the following (note: you can also create a function and just call that as well but this example doesn't show the function method):

    if isnull(me!FieldName1) or isnull(me!FieldName2) then exit sub
    me!TotalField = me!FieldName1 + me!FieldName2 (or whatever your equation is).

    - Note: You may want to lock the TotalField to prevent accidental overwriting of the value.

    To do it in a query, make an update query (usually with criteria for the ID field so you don't update ALL records).
    Add your TotalField as a column in the query and in the "Update To" row, add in your equation (ie. =[FieldName1] + [FieldName2])

    In your specific case, you have 2 fields to add to the form (ie. NumUnitsField and TotUnits) and your equation is = me!NumunitsField/30 in the AfterUpdate event or [NumUnitsField]/30 in the query. (Note: make sure you're dealing with integer type fields.) I was confused in the /30 "minutes" part. Are you trying to do a "Date" calculation or number calculation? If it is a date function, see the help section on the Date Function (such as DateAdd) to determine what equation you should use!! Date type calculations are done on Date type fields only.

    Also - DON'T start any field names with a # or any other odd non-alpha/numeric (I prefer only alpha)!! DON'T even use # in the field name (or table name, form name, function, etc...etc.. - basically ANYWERE in a name!!) DON'T also use spaces or reservered words! It will make your life MUCH easier if you stick to these rules.
    Last edited by pkstormy; 03-29-09 at 22:42.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Feb 2009
    Posts
    7
    I will try your ideas. Bummer is I do have the field with # Reg set. I will have to try and change this. Have to be careful when i get the excel file as it is done that way from the Oracle database th company uses. Thank you for all that information. The 30 is minutes each REG is done in 30 minutes and the total hours is given in another field.

    I found a new issue not sure how to fix. In my excel doc the field that shows the total hours does not have the totals based on the new form.
    When imported the field rounded the values to the next lower. That is not bad but I thought it would keep the values as in 2.5 is now 2. In my new form I have it working so that the filed will update the value but I have to type every single value in the #REG field to make it right. What would make the field complete as it is suppose to should i figure on building another query that will just do the update?
    Last edited by supert8ch; 03-30-09 at 00:56.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding the #....

    I'd rename the field now or expect more problems as you get deeper into coding/queries! Having a fieldname start with a # (or a # anywhere in the name) is 'especially' problematic!

    Regarding the 2.5 verus 2. In your MSAccess table, change the field size from "Long Integer" to "Double". Old 2.5 values that were converted to 2 will probably have to be re-entered with 2.5.
    Last edited by pkstormy; 03-30-09 at 14:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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