In desiging a form for input I have calculated fields mapped over from excel and want to know the best way to treat them so they are true to the calculations.

#1 (**permalink**) 28-Jul-2006, 08:14 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10649-desiging-form-input-i-have-calculated.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10649
mail merge document in WORD.

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

Bob Reynolds

#2 (**permalink**) 28-Jul-2006, 08:14 AM

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

--
Brendan Reynolds
Access MVP

"rjr" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10649
> ERFname
> ERMI
> ERLname
> SPDBA
>
>
> Problem: Files imported into access from excel with calculations
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10649
> 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
>
>

#3 (**permalink**) 28-Jul-2006, 08:14 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10649
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10649
> (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 (**permalink**) 28-Jul-2006, 08:15 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10649
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" 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 [] .
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10649
>
> 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
>>
>>
>>