calculated value in a field

  • Thread starter Thread starter jf
  • Start date Start date
J

jf

I have three different fields that I want to combine
together into a single field that is derived from those
threee. The resulting field is a unique value that will
be the index field for the table.

For example, field is a two digit fiscal year number such
as 04, 05, 06, etc....field two is a text value of the
form 010, 020, 030, etc up to 090. The third field is
that sequential 4 digit contract number such as 0010,
0011, 0012, 0013, etc.

I want to combine the three into the fourth field value of
the form -- 05-010-0012

how do I do this?

thanx

jf
 
I have three different fields that I want to combine
together into a single field that is derived from those
threee. The resulting field is a unique value that will
be the index field for the table.
Click to expand...

This is bad design and is also unnecessary. A Primary Key can consist
of up to TEN fields; just ctrl-click all three fields in table design
view and click the Key icon.
For example, field is a two digit fiscal year number such
as 04, 05, 06, etc....field two is a text value of the
form 010, 020, 030, etc up to 090. The third field is
that sequential 4 digit contract number such as 0010,
0011, 0012, 0013, etc.

I want to combine the three into the fourth field value of
the form -- 05-010-0012
Click to expand...

You can *display* the concatenation by using an expression like

[FY] & "-" & [textvalue] & Format([ContractNumber], "-0000")

If you insist, you could use this same expression in an Update query
to fill the redundant field. This will of course cause failure if you
correct an error in (say) the contract number, as that correction will
not be applied to this "intelligent key".
 
Back
Top