Field size change

[MikeR]

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

 

[Arvin Meyer [MVP]]

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?

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.

 

[MikeR]

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.

 

[Arvin Meyer [MVP]]

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.

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.

 

[a a r o n . k e m p f]

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

 

[Buddy]

duh

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.

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[a a r o n . k e m p f]

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

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.

 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..

 

[Tony Toews [MVP]]

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

Tony
--
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/

 

[Pete D.]

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

message

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 a r o n . k e m p f]

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

 

[a a r o n . k e m p f]

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