Trim but using an expression / identifier ??

[boblarson]

It works past the line now but get an error on the following line ( maybe becuase i forgot to say that there are sometimes empty in the plot field)


strImportText = .Fields("Plot") 'Read the imported text value

Error was

invalid use of null

Change this

strImportText = .Fields("Plot") 'Read text value

to this then:

strImportText = Nz(.Fields("Plot"),"")

 

[secondangel]

I didnt change the code as yet, i left as is, i had a space in the bit where "DVDIMPORTFROM " was

Im really sorry for being so dumb as to miss a space.

So actually its referencing the file ok now its just the null command - ill try that what you suggested bob

 

[secondangel]

AHA bingo it works great, thansk so much Ken for the code and for Bob for pointing out the Nz bit


21995 records proccessed



Heres where you guys shoot me. Its now chopping it to 255 characters.

I forgot *dont hit me* that its a memo field not a text field.

Wheres the embarassed smiley

 

[kenwarthen]

Sorry about the space issue. That was a typo.

 

[secondangel]

Sorry about the space issue. That was a typo.

No problem, thats cool. Its sorted now - it does what you guys have helped me do

Just the memo/text thing issue.

Is it a case of swapping the code so it thinks its a memo field?

 

[kenwarthen]

Ashley,

So the data type of the field Plot is memo? I ran a test string that was 317 characters before processing. The function worked fine, returning a string ending in the double hyphen that was 261 characters in length. I had no problem with the code updating the field in my table. If there's a problem updating field "Plot" in table "DVDIMPORTFROM" my guess is it's related to the data type of the field.

Ken

 

[secondangel]

Really wierd. It works. Just if you view the table in datasheet view it chops it down to 255 chars

However make a quaick tabular form using the wizard to display it and hey presto, theres the complete full memo with everything after the -- chopped off.

Very very strange

Now i have to export to excel with it chopping

Gonna try using the transferspreasheet option set to excel 8-10 and see if it works

Thanks so much for all your help

Ashley