Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

In desiging a form for input I have calculated fields mapped over from excel and want to know the be

Discussion in 'Information Technology' started by rjr, Jul 28, 2006.

  1. rjr

    rjr
    Expand Collapse
    Guest

    Hello, MS Access 2003. I have a form and have 5 fields that were
    calculations in MS Excel. This db has been imported from Excel.

    My problem: Placing a field in the form view that will do
    calculations on other fields and merged those fields.

    Fields

    BusType
    ERDBA
    ERFULLNAME
    ERCO
    NameMerge
    ERFname
    ERMI
    ERLname
    SPDBA


    Problem: Files imported into access from excel with calculations
    that were used there to get the calculaitons.

    NameMerge excel formula
    =IF(BUSTYPE="",ERFULLNAME,IF(BUSTYPE="SP",ERFULLNAME,IF
    (BUSTYPE="Part",ERCO,IF(BUSTYPE="Corp",ERCO,""))))
    ***used to merge a calculated fullname of an employer with the
    company name.

    SPDBA EXCEL FORMULA =IF(BUSTYPE="",NAMEMERGE,IF
    (BUSTYPE="SP",NAMEMERGE &
    DBAMERGE,IF(BUSTYPE="Part",NAMEMERGE & DBAMERGE,IF
    (BUSTYPE="Corp",NAMEMERGE
    & DBAMERGE,""))))
    ***used to calculate some nulls and then either produce a merged
    name or a
    merged name and a dba name.

    DBAMERGE =IF(BUSTYPE="",ERDBA,IF(BUSTYPE="SP"," dba " &
    ERDBA,IF(BUSTYPE="Part"," dba " & ERDBA,IF(BUSTYPE="Corp"," dba " &
    ERDBA,
    ""))))
    ***used to calculate the dba name and actually insert the "dba" into
    the
    name..

    ERFULLNAME =CONCATENATE(IF(ERFNAME="","",ERFNAME2),IF
    (ERFNAME="","","
    "),IF(ERMI="","",ERMI),IF(ERMI="",""," "),ERLNAME)
    ***used to calculate a person's full name from three fields.


    All of the basic fields are in access when I imported them over. How
    do I get these into a field titled as indicated above and have it
    automatically calculate the product so that I can merge it with a
    mail merge document in WORD.

    Any help would be appreciated as I am very inexperienced with
    access. Thank you

    Bob Reynolds
     
  2. Loading...


  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    I answered this question yesterday, Bob, when you posted it under the
    subject line 'Calculated field in form view'.

    --
    Brendan Reynolds
    Access MVP

    "rjr" <rjr1040@bellsouth.net> wrote in message
    news:BTsfg.516$ED2.120@bignews2.bellsouth.net...
    > Hello, MS Access 2003. I have a form and have 5 fields that were
    > calculations in MS Excel. This db has been imported from Excel.
    >
    > My problem: Placing a field in the form view that will do
    > calculations on other fields and merged those fields.
    >
    > Fields
    >
    > BusType
    > ERDBA
    > ERFULLNAME
    > ERCO
    > NameMerge
    > ERFname
    > ERMI
    > ERLname
    > SPDBA
    >
    >
    > Problem: Files imported into access from excel with calculations
    > that were used there to get the calculaitons.
    >
    > NameMerge excel formula
    > =IF(BUSTYPE="",ERFULLNAME,IF(BUSTYPE="SP",ERFULLNAME,IF
    > (BUSTYPE="Part",ERCO,IF(BUSTYPE="Corp",ERCO,""))))
    > ***used to merge a calculated fullname of an employer with the
    > company name.
    >
    > SPDBA EXCEL FORMULA =IF(BUSTYPE="",NAMEMERGE,IF
    > (BUSTYPE="SP",NAMEMERGE &
    > DBAMERGE,IF(BUSTYPE="Part",NAMEMERGE & DBAMERGE,IF
    > (BUSTYPE="Corp",NAMEMERGE
    > & DBAMERGE,""))))
    > ***used to calculate some nulls and then either produce a merged
    > name or a
    > merged name and a dba name.
    >
    > DBAMERGE =IF(BUSTYPE="",ERDBA,IF(BUSTYPE="SP"," dba " &
    > ERDBA,IF(BUSTYPE="Part"," dba " & ERDBA,IF(BUSTYPE="Corp"," dba " &
    > ERDBA,
    > ""))))
    > ***used to calculate the dba name and actually insert the "dba" into
    > the
    > name..
    >
    > ERFULLNAME =CONCATENATE(IF(ERFNAME="","",ERFNAME2),IF
    > (ERFNAME="","","
    > "),IF(ERMI="","",ERMI),IF(ERMI="",""," "),ERLNAME)
    > ***used to calculate a person's full name from three fields.
    >
    >
    > All of the basic fields are in access when I imported them over. How
    > do I get these into a field titled as indicated above and have it
    > automatically calculate the product so that I can merge it with a
    > mail merge document in WORD.
    >
    > Any help would be appreciated as I am very inexperienced with
    > access. Thank you
    >
    > Bob Reynolds
    >
    >
     
  4. Kernow Girl

    Kernow Girl
    Expand Collapse
    Guest

    RE: In desiging a form for input I have calculated fields mapped over

    Hi Bob - do you want these calculated fields to be stored in the DB itself?
    If no you can re-create the formula either on the form or in a query. either
    way the IF statement is the same except it uses IIF instead of IF and the
    fields are surrounded by [] .

    If your IF statement is on a Form you create and Unbound Text box, enter the
    formula below and name it

    =IIF([BUSTYPE]="",[ERFULLNAME],IIF([BUSTYPE]="SP",[ERFULLNAME],IIF
    > ([BUSTYPE]="Part",[ERCO],IIF([BUSTYPE]="Corp",[ERCO],""))))


    If you are using this data in word it's easier in a Query, and therefore you
    just need to put this IF statement in the Field line with the name first --
    like this

    NAMEMERGE: IIF([BUSTYPE]="",[ERFULLNAME],IIF([BUSTYPE]="SP",[ERFULLNAME],IIF
    > ([BUSTYPE]="Part",[ERCO],IIF([BUSTYPE]="Corp",[ERCO],""))))


    Run the Query and you have your merged info.

    However you might not need to go to Word to do your mail merge as the
    reports in Access can give you merged letters and labels.

    Hope this helps - let me know if you need any more info.
    Yours --- Dika


    "rjr" wrote:

    > Hello, MS Access 2003. I have a form and have 5 fields that were
    > calculations in MS Excel. This db has been imported from Excel.
    >
    > My problem: Placing a field in the form view that will do
    > calculations on other fields and merged those fields.
    >
    > Fields
    >
    > BusType
    > ERDBA
    > ERFULLNAME
    > ERCO
    > NameMerge
    > ERFname
    > ERMI
    > ERLname
    > SPDBA
    >
    >
    > Problem: Files imported into access from excel with calculations
    > that were used there to get the calculaitons.
    >
    > NameMerge excel formula
    > =IF(BUSTYPE="",ERFULLNAME,IF(BUSTYPE="SP",ERFULLNAME,IF
    > (BUSTYPE="Part",ERCO,IF(BUSTYPE="Corp",ERCO,""))))
    > ***used to merge a calculated fullname of an employer with the
    > company name.
    >
    > SPDBA EXCEL FORMULA =IF(BUSTYPE="",NAMEMERGE,IF
    > (BUSTYPE="SP",NAMEMERGE &
    > DBAMERGE,IF(BUSTYPE="Part",NAMEMERGE & DBAMERGE,IF
    > (BUSTYPE="Corp",NAMEMERGE
    > & DBAMERGE,""))))
    > ***used to calculate some nulls and then either produce a merged
    > name or a
    > merged name and a dba name.
    >
    > DBAMERGE =IF(BUSTYPE="",ERDBA,IF(BUSTYPE="SP"," dba " &
    > ERDBA,IF(BUSTYPE="Part"," dba " & ERDBA,IF(BUSTYPE="Corp"," dba " &
    > ERDBA,
    > ""))))
    > ***used to calculate the dba name and actually insert the "dba" into
    > the
    > name..
    >
    > ERFULLNAME =CONCATENATE(IF(ERFNAME="","",ERFNAME2),IF
    > (ERFNAME="","","
    > "),IF(ERMI="","",ERMI),IF(ERMI="",""," "),ERLNAME)
    > ***used to calculate a person's full name from three fields.
    >
    >
    > All of the basic fields are in access when I imported them over. How
    > do I get these into a field titled as indicated above and have it
    > automatically calculate the product so that I can merge it with a
    > mail merge document in WORD.
    >
    > Any help would be appreciated as I am very inexperienced with
    > access. Thank you
    >
    > Bob Reynolds
    >
    >
    >
     
  5. rjr

    rjr
    Expand Collapse
    Guest

    Re: In desiging a form for input I have calculated fields mapped over

    First, Thanks to both of you for responding. I'm sorry it turned into a
    double post but I couldn't find the first one anywhere as posted so, go
    figure. Anyhow
    I would like these calcualted fields to be stored in the DB - YES
    Having said that is it a fair statement that I should make it a field in my
    database (already have) and how do I get the query (very weak in queries) to
    put the data in that field to make it permanent.

    Thanks again to all

    BOB

    "Kernow Girl" <KernowGirl@discussions.microsoft.com> wrote in message
    news:BB55A53F-8546-4505-A64C-C41D862E1272@microsoft.com...
    > Hi Bob - do you want these calculated fields to be stored in the DB
    > itself?
    > If no you can re-create the formula either on the form or in a query.
    > either
    > way the IF statement is the same except it uses IIF instead of IF and the
    > fields are surrounded by [] .
    >
    > If your IF statement is on a Form you create and Unbound Text box, enter
    > the
    > formula below and name it
    >
    > =IIF([BUSTYPE]="",[ERFULLNAME],IIF([BUSTYPE]="SP",[ERFULLNAME],IIF
    >> ([BUSTYPE]="Part",[ERCO],IIF([BUSTYPE]="Corp",[ERCO],""))))

    >
    > If you are using this data in word it's easier in a Query, and therefore
    > you
    > just need to put this IF statement in the Field line with the name
    > first --
    > like this
    >
    > NAMEMERGE:
    > IIF([BUSTYPE]="",[ERFULLNAME],IIF([BUSTYPE]="SP",[ERFULLNAME],IIF
    >> ([BUSTYPE]="Part",[ERCO],IIF([BUSTYPE]="Corp",[ERCO],""))))

    >
    > Run the Query and you have your merged info.
    >
    > However you might not need to go to Word to do your mail merge as the
    > reports in Access can give you merged letters and labels.
    >
    > Hope this helps - let me know if you need any more info.
    > Yours --- Dika
    >
    >
    > "rjr" wrote:
    >
    >> Hello, MS Access 2003. I have a form and have 5 fields that were
    >> calculations in MS Excel. This db has been imported from Excel.
    >>
    >> My problem: Placing a field in the form view that will do
    >> calculations on other fields and merged those fields.
    >>
    >> Fields
    >>
    >> BusType
    >> ERDBA
    >> ERFULLNAME
    >> ERCO
    >> NameMerge
    >> ERFname
    >> ERMI
    >> ERLname
    >> SPDBA
    >>
    >>
    >> Problem: Files imported into access from excel with calculations
    >> that were used there to get the calculaitons.
    >>
    >> NameMerge excel formula
    >> =IF(BUSTYPE="",ERFULLNAME,IF(BUSTYPE="SP",ERFULLNAME,IF
    >> (BUSTYPE="Part",ERCO,IF(BUSTYPE="Corp",ERCO,""))))
    >> ***used to merge a calculated fullname of an employer with the
    >> company name.
    >>
    >> SPDBA EXCEL FORMULA =IF(BUSTYPE="",NAMEMERGE,IF
    >> (BUSTYPE="SP",NAMEMERGE &
    >> DBAMERGE,IF(BUSTYPE="Part",NAMEMERGE & DBAMERGE,IF
    >> (BUSTYPE="Corp",NAMEMERGE
    >> & DBAMERGE,""))))
    >> ***used to calculate some nulls and then either produce a merged
    >> name or a
    >> merged name and a dba name.
    >>
    >> DBAMERGE =IF(BUSTYPE="",ERDBA,IF(BUSTYPE="SP"," dba " &
    >> ERDBA,IF(BUSTYPE="Part"," dba " & ERDBA,IF(BUSTYPE="Corp"," dba " &
    >> ERDBA,
    >> ""))))
    >> ***used to calculate the dba name and actually insert the "dba" into
    >> the
    >> name..
    >>
    >> ERFULLNAME =CONCATENATE(IF(ERFNAME="","",ERFNAME2),IF
    >> (ERFNAME="","","
    >> "),IF(ERMI="","",ERMI),IF(ERMI="",""," "),ERLNAME)
    >> ***used to calculate a person's full name from three fields.
    >>
    >>
    >> All of the basic fields are in access when I imported them over. How
    >> do I get these into a field titled as indicated above and have it
    >> automatically calculate the product so that I can merge it with a
    >> mail merge document in WORD.
    >>
    >> Any help would be appreciated as I am very inexperienced with
    >> access. Thank you
    >>
    >> Bob Reynolds
    >>
    >>
    >>
     

Share This Page