Merging two fields to one in access

[luciegiles]

I want to merge a client ID and date, currently separate fields, into one field to create a unique identifier in Access - how can I do this?

Thanks

[Stewart Ross]

Hi luciegiles. If you had told us the names of the fields concerned and their types, or given us some examples, it would have helped us to answer you more clearly. I use generic names below, which you should replace accordingly if you want to try these suggestions for yourself.

Create a query based on your table and add all the fields from your table to that query. In a blank column within the query editor add a new calculated field. At its simplest this is along the lines of:

NewID: [Your ID Field] & [Your Date Field]

The SQL equivalent to this is

SELECT <list of other fields>, [Your ID Field] & [Your Date Field] AS NewID 
FROM [your table name];

This will result in a composite ID which is a text string comprising your ID field immediately followed by your date field (without any spaces between them).

However, the date field could well be transferred to the composite ID string as a full date/time value, not just the date. Also, the date part will be listed year-last according to the likely regional settings applying to dates for your country, in either d/m/y or m/d/y form, which will not help you if you need to order the composite ID field values without separating the ID and date components. You can resolve this using the Format function to return the date in year-month-day form which will then order correctly:

NewID: [Your ID Field] & Format([Your Date Field], "yyyy-mm-dd")

You don't say whether your ID field is numeric or text based. If it is numeric, it would be better to use leading zeros to ensure that the numeric part of the composite ID is of fixed width. Format can again do that for you, as the example below (which is for 6 digits) shows:

NewID: Format([Your ID Field], "000000") & Format([Your Date Field], "yyyy-mm-dd")

If your ID field is a text field you can't use format to set up a fixed-width ID, but you can use another function to add spaces for this purpose. Say you were allowing up to 10 spaces for the ID, you could have it in fixed width like this:

NewID: [Your ID Field] & Space(10 - Len([Your ID Field])) & Format([Your Date Field], "yyyy-mm-dd")

Finally, if you need to have a visual indication of where the two components are separated you can simply add some form of separator character in between, like the vbar below:

NewID: [Your ID Field] & Space(10 - Len([Your ID Field])) & "|" & Format([Your Date Field], "yyyy-mm-dd")

If you add a specific (non-space) separator character such as the vbar that does not occur in either component you may not need to put the first field into fixed width at all (as you will be able to extract the strings on either side of the separator), but this is entirely up to you in terms of your application.

I hope this gives you some idea of the simple and not so simple approaches that can be taken. I would recommend that the composite is built using fixed-width components - it will make it much easier to extract the separate components if this is required at a future date.

-Stewart

[luciegiles]

Hi Stewart,

thanks very much for this response, very helpful

L