Back in ACCESS 2007 Calculated field in query

[djcmalvern]

Hi All,



Problem is to create a generated userID given (via a data entry mechanism) a Forename, Surname and the number from the primary key autonumber fields. In Access 2010 this was no problem to do using Calculated fields in the table definition. I used Left([Forename1], 3], left([Surname], 3) then concatenation with the PK number in a 3rd calculated field. Easy! BUT, I now find that the same thing has to be done in ACCESS 2007, ie no calculated fields in tables. So, I turn to calculated fields in queries? Is that correct? But how do I run an Append query to allow a new student to be added to the database, with the new calculated StudentID. That is, to enter a new row to my Student table with the 'calculated' student ID placed in the field in the table? Or should I be doing something much simpler? All help welcomed!

djcmalvern

[June7]

Much simpler is to use autonumber as primary key and just calculate the 'StudentID' whenever needed for display or for use as field to sort/search.

[djcmalvern]

Much simpler is to use autonumber as primary key and just calculate the 'StudentID' whenever needed for display or for use as field to sort/search.

Thanks,

I understand that, but the project spec is specific that the StudentID is to be 'generated' for storage in the table and is to be of the form LLLLnnnn. So I thought of just adding 1000 to the primary key for the nnnn bit and the Left() functions for the other bit, but is there a way to achieve this in ACCESS 2007 using queries with calculated fields or something. I guess it is an easy thing to do with VBA but, for the purpose of this little project, "programming will not be required". I'm confused!

[June7]

The value can be calculated whenever needed, saving to table requires code. Also, to use the autonumber field in the construct requires the record to be committed to table first. I do have code that generates a unique ID but does not involve autonumber. So, the point is there is no 'simple' way to do what you want and does require programming. I use only VBA and not sure macro can accomplish.

[djcmalvern]

Thanks June7. I'll just do it with VBA and see how things work out. Much obliged.

[June7]

Minor correction to my previous statement. The expression can use the autonumber field before the record is committed. I tested that.

Review discussion of topic in https://www.accessforums.net/databas...ice-34483.html