Solved Error on Creating a Query after changing LongTextField to Short Text

[silentwolf]

Hi guys,
could not find proper help from the net and maybe some of you guys know what the issue could be and if there is a way to go about it.

Via VBA I am importing CSV Files into my system and for that mater I need a LongText "Memo" field

However I am updating that field via code .. cleaning up that field of not needet Text.

Because of the reduced field size and for further Update queries I would need that field in a Textfield.

So I use following code

Public Sub UpdateFieldSizeToShort(tableName As String)
    With CurrentDb
        .Execute "ALTER TABLE " & tableName & " ALTER COLUMN Umsatztext TEXT(255)", dbFailOnError
    End With
End Sub

All works no errors all good..
But as soon as I create a simple SELECT Query I get that following error

Due to a Value that you placed into a Table Property An invalid condition was detected by Access....

if I click on it or away the Query runs as ment.

There are no FK or any of that ..
The string length of the original data was maximum 300 caracters in length or even less..

It is not critical for me as I can transfer the Table into a new Table but would like to know if any of you guys may have a solution as it would be nicer
to continue with that table rather then create a new one or move Data into a new table.

Cheers

 

[tvanstiphout]

I have never tried this, but if the field had >255 chars in some records, this statement might not actually have executed as well as you think.

One idea is to create a new field, copy the Left(255) in it using an Update query, delete the old field, and rename the new field.

 

[silentwolf]

Hi,
I just checked the "LongText" field for the Data and all went in correctly..

Not exactly sure why it does that but as I mentioned it is not the end of the world so I just need to see which option is best suited.

Cheers anyway )

 

[cheekybuddha]

I think your SQL is wrong.

Try instead:

Public Sub UpdateFieldSizeToShort(tableName As String)
    With CurrentDb
        .Execute "ALTER TABLE " & tableName & " ALTER COLUMN Umsatztext VARCHAR(255)", dbFailOnError
    End With
End Sub

 

[silentwolf]

Hi David,

I tried your code but unfortunatelly that does not change it..

Same issue

Cheers

 

[ebs17]

    With CurrentDb
        .Execute "UPDATE " & tableName & " SET Umsatztext = Left(Umsatztext, 255)", dbFailOnError
        .Execute "ALTER TABLE " & tableName & " ALTER COLUMN Umsatztext TEXT(255)", dbFailOnError
    End With

I would first change the content before changing the data type.

 

[silentwolf]

I will try that"

Thanks Eberhard!

Cheers

 

[silentwolf]

@ebs17

just have tested your code but unfortunatelly same thing nothing changed...

 

[Josef P.]

During the conversion, the TextFormat property remains, which Short Text cannot do anything with.
=>

With CurrentDb
    .Execute "UPDATE " & tableName & " SET Umsatztext = Left(Umsatztext, 255)", dbFailOnError
    .Execute "ALTER TABLE " & tableName & " ALTER COLUMN Umsatztext TEXT(255)", dbFailOnError
    .TableDefs(tableName).Fields("Umsatztext").Properties.Delete "TextFormat"
End With

 

[arnelgp]

create a new table with same structure but with short text rather than long text on the new table.
append the record from the old table to the new and delete the old.
rename the new table as the old table.

 

[silentwolf]

Hi arnelgp,

yes that is what I done now and it works just fine.

Cheers Guys