Trim value of all fields

[DebbieDavis]

Hi there, I have exported several delimited text files and would like to know if there is some way I could write a function to trim all of the fields I've imported. I know how to write a regular query to do this, but there are over 30 fields and I don't really want to update field1 with for example trim([field1]) for each field. Lazy I guess but I figure there must be a programmatic way to do it. I thank you in advance for your for your thoughts.

 

[PHV]

Do it with a recordset.
You can browse the Fields collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DebbieDavis]

I guess I forgot to say I only know the basics. I have no idea what you are talking about or how to do it. Thanks anyway, though!!

 

[RJFost]

You could either do it when the query runs, or afterwards; in either case, I agree with PHV- you'll have the best luck loading the data into a recordset. Unfortunately, you're looking at lots of Trim() functions, no matter what.

If you do it before, you need to change your SELECT query syntax to the "...Trim([Field1]), Trim([Field2]), ..." you mentioned above. This way, the data's leaned-down before you load the recordset; you can export without having to do anything else.

If afterward, run an UPDATE query against the recordset to get rid of the fat (e.g., "UPDATE...SET Field1 = Trim([Field1]), Field2 = Trim([Field2]), ...". Again, once that's done you can export with no worries.

Hope this helps...

 

[DebbieDavis]

Thanks very much. In another post, I included an ASP version of what I'm trying to do but nobody has replied. I guess I'll just dump the tables into a SQL database and run my code in ASP. Once again, thank you very much for your reply!

dd

 

[PHV]

I included an ASP version
And you pretend you have no idea what a recordset is ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RJFost]

I was wondering about that, myself ;-)