Writing text from a combined text field to a table text field

[pdlginternet]

I have a dumb question that is stumping me (I'm a spotty Access user
-- use it for a while and become fairly proficient and then don't use
it for a while and forget things).

I have a table which has four fields:

ID (autonumber)
ProjectNumber (Text)
ProjectCo (Text)
ProjectName (Text)

I have a form which has control source for the "ProjectNumber" field
as - "PN-"&[ID] - which, on the form displays "PN-" followed by the
autonumber ID field value -- so, for example for record #1 it shows
"PN-1".

My problem is, I want that value (the "PN-1") written back to the
"ProjectNumber" field so that it is a permanent part of that record
and searchable and for the life of me, my brain just can't figure it
out.

I'm sure it's something easy?

Help!

 

[Douglas J. Steele]

No, you don't want it stored in the table. It's calculated data, which
shouldn't be stored. As fellow Access MVP John Vinson likes to say "Storing
calculated data generally accomplishes only three things: it wastes disk
space, it wastes time (a disk fetch is much slower than almost any
reasonable calculation), and it risks data validity, since once it's stored
in a table either the Total or one of the fields that goes into the total
may be changed, making the value WRONG."

Instead, create a query and add a calculated field to the query.

If you're using the graphical query builder, you'd put

ProjectNumber: "PN-" & [ID]

in an empty cell on the "Field" row.

If you're working directly with the SQL, you'd add

"PN-" & [ID] AS ProjectNumber

to the list of fields.

You'd then use that query wherever you would otherwise have used the table.