|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Bre-x" <cholotron[ at ]hotmail.com> wrote in message
news:u6aTja%23uGHA.1504[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > 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
>
>
|
|
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" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message
news:ukswDj%23uGHA.4752[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > 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 > http://I.Am/DougSteele> (no e-mails, please!) > > > "Bre-x" <cholotron[ at ]hotmail.com> wrote in message > news:u6aTja%23uGHA.1504[ at ]TK2MSFTNGP03.phx.gbl... >> 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 >> >> > >
|
|
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" <cholotron[ at ]hotmail.com> wrote in message
news:QnrCg.60$365.57[ at ]edtnps89...
[Quoted Text] >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" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message > news:ukswDj%23uGHA.4752[ at ]TK2MSFTNGP02.phx.gbl... >> 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 >> http://I.Am/DougSteele>> (no e-mails, please!) >> >> >> "Bre-x" <cholotron[ at ]hotmail.com> wrote in message >> news:u6aTja%23uGHA.1504[ at ]TK2MSFTNGP03.phx.gbl... >>> 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 >>> >>> >> >> > >
|
|
An update query is definitely the way to go.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Bre-x" <cholotron[ at ]hotmail.com> wrote in message
news:kqrCg.62$365.50[ at ]edtnps89...
[Quoted Text] > 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" <cholotron[ at ]hotmail.com> wrote in message > news:QnrCg.60$365.57[ at ]edtnps89... >>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" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message >> news:ukswDj%23uGHA.4752[ at ]TK2MSFTNGP02.phx.gbl... >>> 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 >>> http://I.Am/DougSteele>>> (no e-mails, please!) >>> >>> >>> "Bre-x" <cholotron[ at ]hotmail.com> wrote in message >>> news:u6aTja%23uGHA.1504[ at ]TK2MSFTNGP03.phx.gbl... >>>> 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 >>>> >>>> >>> >>> >> >> > >
|
|
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...
"Bre-x" <cholotron[ at ]hotmail.com> wrote in message
news:u6aTja%23uGHA.1504[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > 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
>
>
|
|
It may be easier to write, Albert, but an Update query will likely be
considerably faster.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Albert D. Kallal" <PleaseNOOOsPAMmkallal[ at ]msn.com> wrote in message
news:%23eNS0KDvGHA.2036[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > 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...
>
>
>
> "Bre-x" <cholotron[ at ]hotmail.com> wrote in message
> news:u6aTja%23uGHA.1504[ at ]TK2MSFTNGP03.phx.gbl...
> > 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
> >
> >
>
>
|
|
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" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message
news:e0WgvFHvGHA.1372[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > It may be easier to write, Albert, but an Update query will likely be > considerably faster. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele> (no e-mails, please!) > > > "Albert D. Kallal" <PleaseNOOOsPAMmkallal[ at ]msn.com> wrote in message > news:%23eNS0KDvGHA.2036[ at ]TK2MSFTNGP05.phx.gbl... > > Actually, this is a case where I would use/find code a good bit easier
> > 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...
> >
> >
> >
> > "Bre-x" <cholotron[ at ]hotmail.com> wrote in message
> > news:u6aTja%23uGHA.1504[ at ]TK2MSFTNGP03.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>
>
|
|
Absolutely (other than the fact that it should be "SELECT * FROM tblanswers
WHERE 1=0" <g>)
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Terry Kreft" <terry.kreft[ at ]mps.co.uk> wrote in message
news:ON2zg5SvGHA.5088[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > 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" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message > news:e0WgvFHvGHA.1372[ at ]TK2MSFTNGP04.phx.gbl... >> It may be easier to write, Albert, but an Update query will likely be >> considerably faster. >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele>> (no e-mails, please!) >> >> >> "Albert D. Kallal" <PleaseNOOOsPAMmkallal[ at ]msn.com> wrote in message >> news:%23eNS0KDvGHA.2036[ at ]TK2MSFTNGP05.phx.gbl... >> > 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... >> > >> > >> > >> > "Bre-x" <cholotron[ at ]hotmail.com> wrote in message >> > news:u6aTja%23uGHA.1504[ at ]TK2MSFTNGP03.phx.gbl... >> > > 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 >> > > >> > > >> > >> > >> >> > >
|
|
Ahh, you spotted the (not so) deliberate mistake <g>.
--
Terry Kreft
"Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message
news:%23eq2L1YvGHA.5044[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > Absolutely (other than the fact that it should be "SELECT * FROM
tblanswers
> WHERE 1=0" <g>)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Terry Kreft" <terry.kreft[ at ]mps.co.uk> wrote in message
> news:ON2zg5SvGHA.5088[ at ]TK2MSFTNGP06.phx.gbl...
> > 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" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message
> > news:e0WgvFHvGHA.1372[ at ]TK2MSFTNGP04.phx.gbl...
> >> It may be easier to write, Albert, but an Update query will likely be
> >> considerably faster.
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "Albert D. Kallal" <PleaseNOOOsPAMmkallal[ at ]msn.com> wrote in message
> >> news:%23eNS0KDvGHA.2036[ at ]TK2MSFTNGP05.phx.gbl...
> >> > 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...
> >> >
> >> >
> >> >
> >> > "Bre-x" <cholotron[ at ]hotmail.com> wrote in message
> >> > news:u6aTja%23uGHA.1504[ at ]TK2MSFTNGP03.phx.gbl...
> >> > > 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
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>
>
|
|
|