Storing calculated fields..

[grades]

I know that it isn't usually best practise to store calculated fields in a table, but in my situation it is necessary, and I haven't really found a good answer for this after searching these and other forums.
Currently my database has one "Name" field (I didn't make it like this) which might look like this "Jonathan (John) Smith"
Now I also have reason to grab the name like this "Smith, John" for other purposes.
I've set up a form for inputting new people with separate fields for firstname, preferredname, and lastname.
I have the calculated fields:
=[fname] & " (" & [pname] & ") " & [lname]
=[lname] & ", " & [pname]
(Later I will add in all the iif's for if there is no preferred name)
Upon finishing the form, the user hits Submit, and I would like to store the concatenated values into the table in their appropriate fields.
I can do this with a SQL INSERT INTO statement but I'd have to add all 25 or so fields from the form, and injecting user input directly into a SQL statement is not best practise either.

There must be some other way?

 

[MarkK]

Some other way to what? You've said you are going to concatenate--against known best practice--the name data, so you can't use a bound form. In that case you'll need to insert the data manually. SQL's INSERT INTO is the most efficient for that.

But it seems to me then you are doing everything the hard way, which is obviously an option, but now you can't even sort your data by last name! I recommend you store your data in distinct fields and use a bound form.

Hope this helps,
Mark

 

[plog]

I know that it isn't usually best practise to store calculated fields in a table, but in my situation it is necessary

To quote my 4 year old niece--'But why?'