Changing Column properties

[Smee]

Greetings.

In a table I have a column filled with 4 digit numbers. I need to convert these number to 6 digit numbers.

The conversion I have worked out. However, the column properties only allow 4 digits. To make it 6, I have to physically go in to the design view and change.

As the data in this table is imported, I want to be able to do this change automatically after every import.

Anyone know how?

Thanks

 

[Mile-O]

Do you have this field set to Text or Numeric?

If Text, then why don't you just default the field's width to seix characters?

If Numeric, then is it Integer, Long Integer, Single, or Double?

You say you "conversion worked out" - what conversion? Adding two 0s to the front of the number or something more mathematical?

 

[Smee]

I have a large table, filled with Timesheet information that includes a column called Year period.

This is a 4 digit number, that identifies the year, and the number of 2 week periods in that year.

eg. 9804 is 1998, 4th period into the year.

With them being 2 week periods there are obviously 26 a year, so from 9826, it goes to 9901.

When it became year 2000 the computer system implimented at that time meant that the year period went to 4801 for some strange reason. 2001 became 49, 2002 became 50 etc. Not very easy to identify what year it is.

It is part of my job to convert this to a 6 digit date.

eg 9801 = 199801 = 1st period in 1998
5017 = 200217 = 17th period in 2002 etc.

I have managed to do this by adding 190000 to pre2000 dates, and adding 200000 and then subtracting 4800 from post 2000 dates.

Before I can run the query that does this, I have to go into the design, and change the precision to 6 from 4. It then works fine.

Its this last part I want to automate, whether with a Macro or VBA, so I can then assign the query to a form and any user can inport the data and convert the date column.

Eventually the old database will be resigned, and the inporting won't need to be done. However, for the time being this is required.

Thanks

 

[Smee]

Any ideas anyone

Completely stuck on this one - any idea?

 

[Smee]

Can't alter fields, only add or remove them !?

Hey thanks for the reply.

I looked about in the help files for quite a while. I couldn't find the specific contents part you mentioned - I'm using Access 2000 if that makes a difference - but I eventually found a small reference to the ALTER TABLE statement.

After more looking I found this on the Microsoft support pages :-

Microsoft Access DDL supports the ALTER TABLE DDL statement. This statement is useful when you need to remove or add a field to an existing table.

Note: This statement won't let you alter an existing field in an Access table

Does this mean my task is not possible? I just need to alter the precision, but it doesn't seem likely.

I found it at this Link


Ignore me

After writing this message I decided to try with what I have anyway, and sure enough I got it working. Not by changing the precision - it seems you can't do that. However I could change it to an integer which has a default precision of "auto" which does the job.

Thanks alot for your help.