In Access table Consolidate 3 fields into 1

[kelleybr]

I am creating an access database for a user who is very prone to make errors, so I'm trying to create a database that does everything, without her having to retype anything. I'm bringing data in from an excel spreadsheet. The spreadsheet has 3 fields that needs to be consolidated into one big 10 digit number. My table has 3 fields:
CIF (6 digit account #)
Note (3 digit identifier)
draw (1 digit identifier)
I want to create a field in my table that consolidates these 3 fields into 1 number in my table, so she doesn't have to do it in Excel before the data is imported. Is this possible?

 

[Ed2020]

Yep, create an import table with four fields - your existing three plus another one to contain the concatenated value. Fow this example we'll call it "tblImport.Consol".

Import your data into the table then run an update query like this:

UPDATE tblImport SET tblImport.Concat = [tblImport]![CIF] & [tblImport]![Note] & [tblImport]![Draw];

Ed Metcalfe.

Please do not feed the trolls.....

 

[Golom]

If any of your fields have leading zeros or are less than the specified maximum length then concatenating them as a honkin' big numeric value will result in displacing the digits from their normal 6-3-1 positions.

To convert to a text

Select (Format(CIF,"000000") & 
        Format([Note],"000") & 
        Format(Draw,"0")) As Concatenated

From ...

And to numeric

Select (10000 * CIF + 10 * [Note] + Draw) As Concatenated

From ...

 

[Ed2020]

Hadn't thought of that! Good point.

Ed Metcalfe.

Please do not feed the trolls.....