Automated Field

[Guest]

Hi ;

I would like to create a new field in my database which should
automatically populate the related data in the field based on a criteria
of an another field . For example ;

Field A Field B
DD620 New Field

Depending on the first two letters of Field A (DD in this case) , i would
like Field B automatically assign a code ( i.e "X") so that i can split
responsibilities .

The criteria should be ; If first two letters of Field A is "DD" or "DB" or
"DC" assign record in field B "X" and If first two letters of Field A is non
of the above criteria assign record in field B "Y" .

Could you pls let me know how this can be done .

Steve

 

[Rick B]

Not sure when you want to do this. If it is when you add a new record, you
would simply create code in the 'after update' event of FieldA that says if
FieldA is... then Field B is ...

If you want to fix all your existing records, then you would need to create
an update query and place an if statement in the "update to" field of your
query that does a similar evaluation.

Rick B

 

[Lynn Trapp]

There isn't any need to create a new field in your table. Rather, simply do
the following in a query.

FieldB: IIf(Left([fielda],2)="DD" Or Left([fielda],2)="DB" Or
Left([fielda],2)="DC","X","Y")

 

[Guest]

Thanks for your interest Rick ;

Basically i would like to that both for my new records and
for the new ones i will add in the database . But i haven't input
many records so far so i can easily input the previous ones manually .

For the new records , could you let me know what exactly code should be
written and where/how i should add the criteria in as i my knowledge of
access is not so good . Could you write the code for my example so that i can
understand easily (specially for "OR" clause)

Thanks
Steve

 

[John Viescas]

You should never store a calculated value like this in your table. It's
simple enough to create the value in a query expression:

SELECT IIf(Left([FieldA], 2) = "DD" Or Left([FieldA], 2) = "DB", "X", "Y")
As [Field B]
FROM Mytable

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Guest]

Hi Lynn ;

Thank you very much for your reply .
I understand that could be done by a query but actually
i also want that field to be displayed on a form which is linked
to that database .

Steve

There isn't any need to create a new field in your table. Rather, simply do
the following in a query.

FieldB: IIf(Left([fielda],2)="DD" Or Left([fielda],2)="DB" Or
Left([fielda],2)="DC","X","Y")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi ;

I would like to create a new field in my database which should
automatically populate the related data in the field based on a criteria
of an another field . For example ;

Field A Field B
DD620 New Field

Depending on the first two letters of Field A (DD in this case) , i would
like Field B automatically assign a code ( i.e "X") so that i can split
responsibilities .

The criteria should be ; If first two letters of Field A is "DD" or "DB" or
"DC" assign record in field B "X" and If first two letters of Field A is non
of the above criteria assign record in field B "Y" .

Could you pls let me know how this can be done .

Steve

 

[Lynn Trapp]

Then base your form on a query that has that calculated value in it.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi Lynn ;

Thank you very much for your reply .
I understand that could be done by a query but actually
i also want that field to be displayed on a form which is linked
to that database .

Steve

There isn't any need to create a new field in your table. Rather, simply do
the following in a query.

FieldB: IIf(Left([fielda],2)="DD" Or Left([fielda],2)="DB" Or
Left([fielda],2)="DC","X","Y")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi ;

I would like to create a new field in my database which should
automatically populate the related data in the field based on a criteria
of an another field . For example ;

Field A Field B
DD620 New Field

Depending on the first two letters of Field A (DD in this case) , i would
like Field B automatically assign a code ( i.e "X") so that i can split
responsibilities .

The criteria should be ; If first two letters of Field A is "DD" or

"DB"
or

"DC" assign record in field B "X" and If first two letters of Field A

is
non

of the above criteria assign record in field B "Y" .

Could you pls let me know how this can be done .

Steve

 

[Guest]

Hi John ;

Thanks . That worked very well .
Is it possible then that can work as an update query so that
when i run this it automatically updates the related field in my table .
I'd like to base my queries on the master table where i need that
information from .

Steve

You should never store a calculated value like this in your table. It's
simple enough to create the value in a query expression:

SELECT IIf(Left([FieldA], 2) = "DD" Or Left([FieldA], 2) = "DB", "X", "Y")
As [Field B]
FROM Mytable

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hi ;

I would like to create a new field in my database which should
automatically populate the related data in the field based on a criteria
of an another field . For example ;

Field A Field B
DD620 New Field

Depending on the first two letters of Field A (DD in this case) , i would
like Field B automatically assign a code ( i.e "X") so that i can split
responsibilities .

The criteria should be ; If first two letters of Field A is "DD" or "DB"
or
"DC" assign record in field B "X" and If first two letters of Field A is
non
of the above criteria assign record in field B "Y" .

Could you pls let me know how this can be done .

Steve

 

[John Viescas]

Steve-

You don't need to store the value! Use the expression I gave you in a query
to generate the field as needed. You can base your forms and reports that
need this field on a query.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hi John ;

Thanks . That worked very well .
Is it possible then that can work as an update query so that
when i run this it automatically updates the related field in my table .
I'd like to base my queries on the master table where i need that
information from .

Steve

You should never store a calculated value like this in your table. It's
simple enough to create the value in a query expression:

SELECT IIf(Left([FieldA], 2) = "DD" Or Left([FieldA], 2) = "DB", "X",
"Y")
As [Field B]
FROM Mytable

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hi ;

I would like to create a new field in my database which should
automatically populate the related data in the field based on a
criteria
of an another field . For example ;

Field A Field B
DD620 New Field

Depending on the first two letters of Field A (DD in this case) , i
would
like Field B automatically assign a code ( i.e "X") so that i can split
responsibilities .

The criteria should be ; If first two letters of Field A is "DD" or
"DB"
or
"DC" assign record in field B "X" and If first two letters of Field A
is
non
of the above criteria assign record in field B "Y" .

Could you pls let me know how this can be done .

Steve