Trim function

  • Thread starter Thread starter Bre-x
  • Start date Start date
B

Bre-x

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



Bre-x said:
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






Douglas J. Steele said:
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.
Click to expand...
Click to expand...
 
An update query is definitely the way to go.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bre-x said:
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



Bre-x said:
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






Douglas J. Steele said:
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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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
Click to expand...
Click to expand...
Click to expand...
 
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...
 
It may be easier to write, Albert, but an Update query will likely be
considerably faster.
 
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
 
Absolutely (other than the fact that it should be "SELECT * FROM tblanswers
WHERE 1=0" <g>)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Terry Kreft said:
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


--

Terry Kreft


Douglas J. Steele said:
It may be easier to write, Albert, but an Update query will likely be
considerably faster.
Click to expand...
Click to expand...
 
Ahh, you spotted the (not so) deliberate mistake <g>.


--

Terry Kreft


Douglas J. Steele said:
Absolutely (other than the fact that it should be "SELECT * FROM tblanswers
WHERE 1=0" <g>)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Terry Kreft said:
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


--

Terry Kreft


Douglas J. Steele said:
It may be easier to write, Albert, but an Update query will likely be
considerably faster.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Actually, this is a case where I would use/find code a good bit
Click to expand...
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?

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
Click to expand...
handy
if
VBA can be use it.

Regards,

Bre-x
Click to expand...
Click to expand...
Click to expand...
 
Back
Top