ACCESS: TABLE Calculated Field

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
I have a field where the data type is Calculated Left([Emp_LastName],5) & Left([Emp_FirstName],2)

FYI This creates a UserID.

I want this is be the default but let the user make up a 5 character one if they choose.

whats the best way to do this?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
A field can be a calculated field, or a hard-coded field, but NEVER both at the same time (it is an "either or" proposition).
My recommendation would be to have another field for this Custom ID. Then add a condition to your Calculated field that checks that field first. If it is not blank, then return that value. Otherwise, return your calculation.
 
Upvote 0
Thanks, Joe. I was thinking I needed the second field but wanted to see if there was another way I didn't know about. Thanks again.

PS sometimes when I open the database (I have a front and back end with the tables) I get #Invalid . If I go to the table and click inside the formula I listed above, somehow the calc fixes itself and works. Any idea why sometimes it errors?
 
Upvote 0
PS sometimes when I open the database (I have a front and back end with the tables) I get #Invalid . If I go to the table and click inside the formula I listed above, somehow the calc fixes itself and works. Any idea why sometimes it errors?
Not sure. Almost sounds like there is a connection issue or delay.

However, I would recommend adding all calculated fields in a Query instead of doing it directly in the Table. The feature to add calculated fields in Tables was a recent feature, has many limitations, and is not supported by any other program other than Access. So if this ever grew big, and you needed to move it off of Access, you would have issues.

Trying moving that calculation to a query instead and see if that resolves the issue.
 
Upvote 0
If there's a user involved as you say, why not do the calculation on the form if they try to save without inputting their own value? Then only one field required. Or why not make them create a value? I have to wonder what use the data is if a user doesn't have to provide it, yet you are compelled to create an arbitrary value if they don't.
 
Upvote 0
I was also thinking that you could do this in a form pretty easily - the user inputs a value, and if it they don't you create it for them. Or the field for the ID can be prepopulated as soon as First Name and Last Name are known, but still be capable of being overwritten by the user.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top