ACCESS: TABLE Calculated Field

[gheyman]

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?

 

[Joe4]

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.

 

[gheyman]

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?

 

[Joe4]

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.

 

[Micron]

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.

 

[xenou]

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.