Create a calculated field in a table, based on string from a query

[louise]

Hello,


I am trying to create a text field and save it to a table. It is to be a combination of letters and numbers, indicating semester, course type, course number, and an asterisk if enrollment is limited:

The first three or four letters, followed by a hyphen, will be built in a query based on fields from other tables:
CourseCodeFront: IIf([CurrentSemester]="Fall" And [SingleOrMulti]="Single-Session","FSS-",IIf([CurrentSemester]="Fall" And [SingleOrMulti]="Multi-Session","FS-",IIf([CurrentSemester]="Spring" And [SingleOrMulti]="Single-Session","SSS-",IIf([CurrentSemester]="Spring" And [SingleOrMulti]="Multi-session","SS-"))))

Then, I want the user to append a field name CourseCodeNumber in my table (I have sorted the courses by times and dates, and presented them in a form, so the user can assign consecutive numbers.
Then, in some cases, I want an asterisk applied, based on the same query as above, this expression: Asterisk: IIf([MaximumSize]>0,"*","")

So, combined then into CourseCode (already a field in my table) : CourseCodeFront & CourseCodeNumber & Asterisk
The final result should look like these examples: FFS-1, FS-1, FS-3 etc.

And, I would like this to be save CourseCode in the table. The value doesn't change and it is called upon many times, so I hate to recalculate it each time.
As an added wrinkle, I do need to also keep CourseCodeNumber for sorting, since CourseCode itself is a text field now.

All this works fine, except that I am not able to save to the table without actually typing it in. I had hoped that the user could add just the number (CourseCodeNumber). But, while I can display the CourseCode, it does not save to the table.

I would appreciate any help. I am not a programmer, so I am hoping this can be done via a query.
Thanks!

[pbaldy]

Have you tried the new calculated field data type?

[June7]

Can be done by UPDATE sql action.

Build a query that pulls in the necessary fields, switch to UPDATE query, set the UpdateTo row to the concatenated value field or put expression right there.

However, it may be simpler just to save the component values to separate fields and then concatenate in a Calculated field in table.

[June7]

Well, guess I was too talky and Paul got in first!!

[louise]

Have you tried the new calculated field data type?

I thought about the calculated field but my understanding is that it cannot use fields from other tables. Is that correct?
Also, if I could do that, would I combine all these expressions into one big expression? how?
thanks!

[June7]

That's why I suggested saving the component values and then concatenate in table.

If you want to provide db for analysis, follow instructions at bottom of my post.

[pbaldy]

My apologies, I missed that in your original post. You are correct, the calculated field can only use fields from the same table. You can use June7's update query, or have a field you keep updated when adding or updating records via forms.

[louise]

My apologies, I missed that in your original post. You are correct, the calculated field can only use fields from the same table. You can use June7's update query, or have a field you keep updated when adding or updating records via forms.

Could you say a bit more about "have a field you keep updated when adding or updating records via forms"? I think that is precisely what I want to do-- keep the CourseCode updated (rarely) when I add info in the form. What I can't figure out is whether there is a way have values save to that field without explicitly typing them in. They show in the form as the string I created, but don't save to the field in the table.
Thanks again.

[pbaldy]

Check out Allen's "you want to store it anyway" section:

http://allenbrowne.com/casu-14.html

[louise]

Hello,
I wanted to give an update on how this worked out, thanks to your suggestions and some hints from Allen Browne's article, as was suggested. I had created several text strings and wanted to enter values for one additional field (CourseCodeNumber) and have them all combine into a longer string to be saved to CourseCode.

I accomplished this by setting "After Update" for the field CourseCodeNumber to the CourseCode expression: CourseCode = CourseCodeFront & CourseCodeNumber & Asterisk. So, the complete CourseCode field is created and saved after CourseCodeNumber is entered, via After Update.

Thanks for your help!

[pbaldy]

Happy to help!