Trim function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bre-x - 09 Aug 2006 20:05 GMT

Is there a way to use VBA to trim and entire record?

I have a table that has 37 columns, each column need to be "trim"
I know that I can create a update query, but I would be really handy if VBA
can be use it.

Regards,

Bre-x

Reply to this Message

Douglas J. Steele - 09 Aug 2006 20:19 GMT

Can you give an example of what you're trying to do?

In general, it's almost always more efficient to use SQL rather than VBA if
it's possible to do the same thing either way.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Is there a way to use VBA to trim and entire record?
>
[quoted text clipped - 5 lines]
>
> Bre-x

Reply to this Message

Bre-x - 09 Aug 2006 21:15 GMT

I am dowloading some records (sales orders) from a Pervasive SQL server, the
table has the following fields

SO, double
CUSCOD, text
SODESC, text

ratter than doing a query like this:

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE BKARINV SET BKARINV.CUSCOD = [BKARINV]![CUSCOD],
BKARINV.SODESC = [BKARINV]![SODESC];"
DoCmd.SetWarnings True

I would like to have a vba code, the BKARINV table has 37 columns (would be
a longggggg query)

Why?? Pervasive fills in the entire field. Example:

The CUSCODE 's field lenght is 15 characters, but if you enter "JOHN",
eventhough "JOHN" has only 4 characters
Pervasive fill the rest with something!!! The only way to clear that
"something" is by using the Trim funcion.

Thanks for your time.

Regards,

Bre-x

> Can you give an example of what you're trying to do?
>
[quoted text clipped - 12 lines]
>>
>> Bre-x

Reply to this Message

Bre-x - 09 Aug 2006 21:18 GMT

Hehehe, I forgot the actual TRIM!!! function

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE BKARINV SET BKARINV.CUSCOD =
Trim([BKARINV]![CUSCOD]), BKARINV.SODESC = Trim([BKARINV]!
[SODESC]);"
DoCmd.SetWarnings True

thanks again

>I am dowloading some records (sales orders) from a Pervasive SQL server,
>the table has the following fields
[quoted text clipped - 42 lines]
>>>
>>> Bre-x

Reply to this Message

Douglas J. Steele - 09 Aug 2006 22:14 GMT

An update query is definitely the way to go.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hehehe, I forgot the actual TRIM!!! function
>
[quoted text clipped - 51 lines]
>>>>
>>>> Bre-x

Reply to this Message

Albert D. Kallal - 10 Aug 2006 05:08 GMT

Actually, this is a case where I would use/find code a good bit easier to
write....

Are you sure all collums need a trim?

the following code snip would do the trick...

Sub mytrimall()

Dim rst As DAO.Recordset
Dim f As DAO.Field

Set rst = CurrentDb.OpenRecordset("tblanswers")

Do While rst.EOF = False

rst.Edit
For Each f In rst.Fields

If f.Type = dbText Then
f = Trim(f)
End If
Next f
rst.Update
rst.MoveNext
Loop

rst.Close

End Sub

Note how the above code does skip non txt fields, as your table might have
id/automnumber field...

> Is there a way to use VBA to trim and entire record?
>
[quoted text clipped - 5 lines]
>
> Bre-x

Reply to this Message

Douglas J. Steele - 10 Aug 2006 12:37 GMT

It may be easier to write, Albert, but an Update query will likely be
considerably faster.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Actually, this is a case where I would use/find code a good bit easier to
> write....
[quoted text clipped - 39 lines]
> >
> > Bre-x

Reply to this Message

Terry Kreft - 11 Aug 2006 11:10 GMT

But you can combine the two, something like:-

Sub mytrimall()
Dim db as DAO.Database
Dim rst As DAO.Recordset
Dim f As DAO.Field
dim strSQL1 as string
dim strSQL2 as string

Const SQL_BASE = "UPDATE tblanswers SET "
Set db = Currentdb
Set rst = db.OpenRecordset("SELECT * tblanswers WHERE 1=0")

For Each f In rst.Fields
If f.Type = dbText Then
strSQL = strSQl & "[" & f.Name & "] = Trim([" & f.Name & "]), "
End If
Next

rst.Close
Set rst = Nothing

If Len(strSQL) > 0 then
strSQl = SQL_BASE & Left(strSQl, Len(strSQl) - 2)
db.Execute strSQL
End if
Set db = Nothing
End Sub

Signature

Terry Kreft

> It may be easier to write, Albert, but an Update query will likely be
> considerably faster.
[quoted text clipped - 42 lines]
> > >
> > > Bre-x

Reply to this Message

Douglas J. Steele - 11 Aug 2006 22:29 GMT

Absolutely (other than the fact that it should be "SELECT * FROM tblanswers
WHERE 1=0" <g>)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> But you can combine the two, something like:-
>
[quoted text clipped - 74 lines]
>> > >
>> > > Bre-x

Reply to this Message

Terry Kreft - 12 Aug 2006 19:06 GMT

Ahh, you spotted the (not so) deliberate mistake <g>.

Signature

Terry Kreft

> Absolutely (other than the fact that it should be "SELECT * FROM tblanswers
> WHERE 1=0" <g>)
[quoted text clipped - 77 lines]
> >> > >
> >> > > Bre-x

Reply to this Message