Calculated column, first letters of last word in an other field

[Chrtalgo]

Hi,


I have just started using Access, and have encountered a problem.

I have a case register where the person in charge of the case is listed (Firstname Middlename Lastname), in the "Employee" field.
What I need is a field for a calculated case number. The field "case number" must return like this:



2017-FLA-1234

Where "2017" is the year retrieved from the "Date registered" field,
"FLA" is first letter in first name and first two letters in last name (Firstname Middlename Lastname)
"1234" is four random generated digits.

Unique listings in field "Case number" should not be allowed.


I am very new to Access and not familiar with VBA at all. I manage to get everything except the first letters of the last name. I have searched the web and not found anything that I can get to work. Looks like whats stopping me is that you are not allowed to use custom functions in a calculated column.

I am hoping for a bit of help, either by calculating the case number as soon as the employee is selected, or by having it calculated when a save button is pressed in a new case form.

Thanks in advance!

[aytee111]

Not sure what you mean by "you are not allowed to use custom functions in a calculated column" - ? The other problem you are facing is what happens when a person's is carried on a table in one field. It should always be two separate fields - one for first and middle names and the lastname in its own field. If at all possible, change it now before you go any further.

[isladogs]

Agree with aytee except suggest 3 separate name fields to avoid issues with Anne Marie vs Anne-Marie
You also need to plan ahead for last names like O'Neill. Will you use ON or O'?
Also how will you distinguish between Jon Snow and Jill Snodgrass when your system has JSN for both

A much easier solution is to give each person a 3 letter unique ID and use that.

Using a random 4 digit number is also a mistake as potentially you could repeat case numbers.
Suggest starting with 0001 & incrementing that part by 1 each time.

Unique listings by case number are not allowed

Are you sure you meant that?

[davegri]

Aytee111 is absolutely correct. A world of grief is waiting for you if you have the full name in one field.
Also:

Unique listings in field "Case number" should not be allowed.

Don't you mean:
Duplicate listings in field "Case number" should not be allowed.

[orange]

To summarize what others have advised, get your requirements clearly described and agreed to before trying to build a database.
It will save you time and ensure you are building according to needs.
With database you should work with atomic fields (one fact one field) -which you can concatenate for display/presentation.

Here is a link to more info on database planning and design if you are new to database generally.

Good luck.

[John_G]

Looks like whats stopping me is that you are not allowed to use custom functions in a calculated column.

That sounds as if you are using a calculated fields in a table - am I correct? The general opinion on that is - don't. Calculated fields should be in queries, not tables.

If you want calculate the case number, do it in a data entry form before saving the record.
But, please read previous posts - they are giving you very good advice.

[Chrtalgo]

I want to elaborate a little bit on what I want.

We have almost a thousand members, where some of the members are elected to be on the board and some of the members are also chosen to be executing officers, working with the other members cases.

I have created a table with all members, including the executives, which I called employees in the OP. I have added a yes/no check-box in the table to distinguish between regular members, and members with executive tasks, and all members are listed with both first and last name separated, as suggested, and one fields where the two are combined into the whole name. I have made a form from this table to cycle through the member register and to easily change the member status.
I have a separate table for all registered cases, and I want to assign each case to one of the members elected as executives. This is why I created a field that combined first and last name. When I created a combo box that looked up everyone that had “true” on the check box for executive officer, I could see and select the executives full name.

Now I have changed it to lookup both first name and last name fields from the member register and show both fields, were executive = true. This works fine until one is seleced, as only first name would show up. Is there a solution to this?
The other problem remains also, I need field in the case register to capture first letter of first name, and first two letters from last name. This would be easy if the selected officers name showed up in two fields, as I could just use Left([first;1)&Left([Last];2)


The table looks like this:

Case ID	Date registered	Description	Executive officer	Status
(Year from date registerred)-(first letter officer name, two letter officers last name)-(4 digits)	dd.mm.yyyy	Text	John Johnson	In progress
Should be automatically calculated by filling in date and selecting an officer.

Members register table:

First name	Last name	Full name	Member since	Division	Executive officer?	Date of birth	Gender
John	Johnson	John Johnson	dd.mm.yyyy	2	True	dd.mm.yyyy	M

Maybe this could be done by queries, but does having a query automatically update the table, as the calculated fields do, in real time? Or do you have to run the query manually for each entry?

I really appreciate all help!



And yes, I did of course mean that duplicate values in the case ID should not be possible

[June7]

I advise against using officer initials as part of case ID as it is an unnecessary complication. Also, it loses relevance when multiple members have same initials, granted your member pool may be small enough it is less likely, but it is not impossible (John Hanson, Jessica Harper).

Updatable queries are editing table directly. Non-updatable queries won't allow user input.

Edits are committed to table when:
1. table or query or form is closed
2. move to another record in table/query/form
3. run code to save

Should not be a natural field in table with full name. The full name should be a calculated field (in table if you really want) that concatenates other fields.

A combobox RowSource can be multi-column to show the full name and also provide each name part:

SELECT MemberID, LastName & ", " & FirstName AS FullName, LastName, FirstName FROM Members;

[ArviLaanemets]

Your problems started with Members table not having a proper ID field. Easiest will be to use autonumeric id

tblMembers: MemberID (PK, autonumeric), FirstName, [MiddleName], LastName, MemberSince, Division, MemberStatus, DOB, Gender

tblMemberStatus: MemberStatus (PK, int), StatusTxt (p.e. "Executive Officer")

tblCases: CaseID, CaseDate, MemberID, CaseStatus

Edit: Don't include calculated fields into tables - like FullName into Members table.

Edit: Another point is, tables are for keeping data. For manipulating data are forms. The best practice is to hide data from user - tables don't have enough control over data entry quality, and it will be too easy to enter data into them , which can corrupt your database. And when you need to correct some data in tables manually - make a copy of your database. Then make another copy and make your changes there. Then test the changed copy, and only when you are sure that all was OK, overwrite your original database with tested one (and keep the first copy for some time as it was created).

[ssanfu]

My $0.02.....

I NEVER use calculated FIELDS in tables. Calculations should be done in queries.
This is a query with a calculated column to get the full names:

June showed the SQL of a query, I have the query in the query designer.

I never use a table as the record source for a form.... I always a query (for many reasons).


June and Arvil state very good points......