How to combine columns within a table (Access SQL)

Question

There are three columns of date information (month, day, year) in a table and I would like to create a new column in the table and combine those 3 columns into a the new column so that I can have a single formatted date. How can I do this? I know how to query this but I am trying to figure out how to copy this information to the original table. Thanks in advance.

Take a look at the DATESERIAL function.

onedaywhen
– onedaywhen

2011-05-18 21:33:46 +00:00
Commented May 18, 2011 at 21:33 — onedaywhen
– onedaywhen, Commented May 18, 2011 at 21:33
please add the working query you have.

bw_üezi
– bw_üezi

2011-05-19 09:26:45 +00:00
Commented May 19, 2011 at 9:26 — bw_üezi
– bw_üezi, Commented May 19, 2011 at 9:26
What data type are your 3 existing columns?

HK1
– HK1

2011-05-19 13:22:06 +00:00
Commented May 19, 2011 at 13:22 — HK1
– HK1, Commented May 19, 2011 at 13:22

Hector Sanchez · Accepted Answer · 2011-05-18 21:07:39Z

2

If I understood well, you need to alter your table and add a datetime column You need to do something like this

UPDATE YourTable
SET DATECOLUMN = '#' & DAYCOLUMN & '/' & MONTHCOLUMN & '/' & YEARCOLUMN & '#'

It was the sintaxis in access if i remember it well.

Greetings.

answered May 18, 2011 at 21:07

Hector Sanchez

2,3274 gold badges26 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user759935 Over a year ago

I tried the following and I got the error "Microsift access didn't update 10 fields due to a type conversion failure": update Autographs set full_date = '#' & day & '/' & month & '/' & year& '#';

TheOtherTimDuncan Over a year ago

The type conversion errors are probably from some invalid day, month or year values in your fields that are causing an invalid date to be generated. Look for values like Day=30 and Month=2.

onedaywhen · Accepted Answer · 2011-05-19 08:52:47Z

2

There are a number of way of managing such a data scrubbing exercise e.g.

CREATE a new table including a new NOT NULL column and omitting the now-redundant columns, INSERT..SELECT into the new table only the data you require, DROP the old table (you'll end up with a different table name which may not be a bad thing).
ADD a new nullable column to the existing table (or NOT NULL if you have an appropriate DEFAULT), UPDATE the new column, alter the column to make it NOT NULL then DROP the now-redundant columns.

answered May 19, 2011 at 8:52

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Collectives™ on Stack Overflow

How to combine columns within a table (Access SQL)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related