Field size change

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MikeR - 12 Apr 2008 00:54 GMT

A field was set to data type text, field size 5. Some 5 character data was entered,
and then it was discovered that the field size should have been 10. Adding a new
record using the new size is OK, but trying to type more than 5 characters into an
existing record(directly into the table) is not allowed. This is a stand alone db. No
front end yet.
Is this normal behavior in Access 2000?
Any way to get the new field size to affect existing rows?
Mike

Reply to this Message

Arvin Meyer [MVP] - 12 Apr 2008 01:18 GMT

>A field was set to data type text, field size 5. Some 5 character data was
>entered, and then it was discovered that the field size should have been
[quoted text clipped - 3 lines]
> Is this normal behavior in Access 2000?
> Any way to get the new field size to affect existing rows?

That is not the behavior you should expect. Try compacting the database
containing the data (assuming a split database). If that doesn't work, try
imorting everything into a new empty database.

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Reply to this Message

MikeR - 12 Apr 2008 01:53 GMT

> That is not the behavior you should expect. Try compacting the database
> containing the data (assuming a split database). If that doesn't work,
> try imorting everything into a new empty database.

Thanks Arvin -
Some joy. Now if I delete the data in the field, and re-type it, it works. But I
can't just place the cursor at the end of the data and append to it. For instance,
if the field were '54 Bay', I can't just add ' Street' to it.

I don't know what you mean by a split db. As I said before, it's a db only. There are
no forms or programming associated with it. I think of it as a back end.

Reply to this Message

Arvin Meyer [MVP] - 12 Apr 2008 03:03 GMT

>> That is not the behavior you should expect. Try compacting the database
>> containing the data (assuming a split database). If that doesn't work,
[quoted text clipped - 8 lines]
> only. There are no forms or programming associated with it. I think of it
> as a back end.

Try running a query to add five characters, like:

Update MyTable Set MyField = IIf(Len([MyField] <=5),[MyField] & "12345",
[MyField])

Now if the field MyField has 5 or less characters, it will append; 12345 to
the end of it. Go into you table and fix the data then for anything with
12345 still left, create an alias column to get your data:

GetJunk: Right([MyField],5) = "12345"

Then use 12345 as Criteria.

If there only a few records, hand delete the Junk data, or run another
update query to strip off the last 5 characters if there's more than a few.

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Reply to this Message

Buddy - 12 Apr 2008 23:30 GMT

duh

>>> That is not the behavior you should expect. Try compacting the database
>>> containing the data (assuming a split database). If that doesn't work,
[quoted text clipped - 25 lines]
> update query to strip off the last 5 characters if there's more than a
> few.

Reply to this Message

a a r o n . k e m p f @ g m a i l . c o m - 13 Apr 2008 01:11 GMT

and for the record; this probably qualifies as some of the WORST SQL
i've ever seen in my whole life.
Even if it is 'air code'.

Update MyTable Set MyField = IIf(Len([MyField] <=5),[MyField] &
"12345", [MyField])

I mean..

surely you mean

UpdateMyTable Set MyField = MyField + '12345'
WHERE Len(MyField) < 5

I think that is a much better statement; sorry

-Aaron

> >> That is not the behavior you should expect. Try compacting the database
> >> containing the data (assuming a split database). If that doesn't work,
[quoted text clipped - 26 lines]
> --
> Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

Reply to this Message

a a r o n . k e m p f @ g m a i l . c o m - 12 Apr 2008 03:33 GMT

SQL Server is easier, faster, safer, more secure, reliable and
powerful.

AND IT IS CHEAPER BECAUSE YOU DO NOT HAVE TO RESEARCH BUGS LIKE THIS.

SQL Server just works.

Access _SUCKS_ as a databse. Access ADP rocks for a front end-- I'd
suggest a class at your local community college to learn SQL Server
(the most popular database in the world)

-Aaron

> A field was set to data type text, field size 5. Some 5 character data was entered,
> and then it was discovered that the field size should have been 10. Adding a new
[quoted text clipped - 4 lines]
> Any way to get the new field size to affect existing rows?
> Mike

Reply to this Message

Pete D. - 13 Apr 2008 22:21 GMT

Didn't you say in the last thread college doesn't teach Access?

SQL Server is easier, faster, safer, more secure, reliable and
powerful.

AND IT IS CHEAPER BECAUSE YOU DO NOT HAVE TO RESEARCH BUGS LIKE THIS.

SQL Server just works.

Access _SUCKS_ as a databse. Access ADP rocks for a front end-- I'd
suggest a class at your local community college to learn SQL Server
(the most popular database in the world)

-Aaron

On Apr 11, 4:54 pm, MikeR <nf4lNoS...@pobox.com> wrote:

> A field was set to data type text, field size 5. Some 5 character data was
> entered,
[quoted text clipped - 8 lines]
> Any way to get the new field size to affect existing rows?
> Mike

Reply to this Message

a a r o n . k e m p f @ g m a i l . c o m - 13 Apr 2008 23:33 GMT

I'm talking about SQL Server dog

SQL Server is one of the optional engines for MS Access. But the
classes are for 'SQL Server' and not for 'Microsoft Access'

-Aaron

> Didn't you say in the last thread college doesn't teach Access?
>
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Reply to this Message

Tony Toews [MVP] - 13 Apr 2008 02:32 GMT

Note that Aaron's answer to just about every question is SQL Server
and ADPs. No matter how appropriate.

Tony

Signature

Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Reply to this Message

a a r o n . k e m p f @ g m a i l . c o m - 13 Apr 2008 23:32 GMT

Note that Tony's answer to just about every question is a retarded
database that isnt' reliable, scalable, performant-- or accetable.

His mis-information does nothing but clutter up this group.

_ALL_ of the MVPs around here are just plain-- TOO OLD AND STUPID- to
move to SQL Server.

Case closed

-Aaron

> Note that Aaron's answer to just about every question is SQL Server
> and ADPs. No matter how appropriate.
[quoted text clipped - 6 lines]
> Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

Reply to this Message