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

R

rjr

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
 
B

Brendan Reynolds

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

Kernow Girl

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],""))))
Click to expand...

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],""))))
Click to expand...

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
 
R

rjr

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 said:
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],""))))
Click to expand...

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],""))))
Click to expand...

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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculated field in form view 1
Calculated field in form view 0

Top