[AccessD] Trim in Access 2007

Hi Patricia & Group,

I wish to trim all values in the column rather than set them
all to a single value. I will certainly try this on a copy of my
table, but the SQL view of an Update query will confirm that
the Field.Name should be used.

Michael R. Mattys
MapPoint & Access Dev
www.mattysconsulting.com

----- Original Message ----- 
From: "O'Connor, Patricia (OTDA)" <Patricia.O'Connor at otda.state.ny.us>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Friday, November 30, 2007 12:52 AM
Subject: Re: [AccessD] Trim in Access 2007


>I think you are getting field.NAME mixed up with the value in the 
>field.VALUE.
> I am not at work so I can't verify the full statement but you could try
>
> Dim oDB As DAO.Database
> Dim oTbl As DAO.TableDef
> Dim oQry As DAO.QueryDef
> Dim oFld As DAO.Field
>
> Dim strTmp as string
> Dim strSql as string
>
> Set oDB = CurrentDb
> Set oTbl = odb.TableDefs("CASS")
> strSql = "UPDATE CASS SET CASS.["
>
> For Each oFld In oTbl.Fields
>    strTmp = TRIM(ofld.VALUE)
>    Debug.print = ofld.Name " - Untrimmed: " & LEN(fld.VALUE)  & " trimmed: 
> " & LEN(strTmp)
>
>    qdfTrim.SQL = strSQL & ofld.Name & "] =  " & strTmp
>    qdfTrim.Execute
> Next
>
> HTH
>
> *************************************************************
> * Patricia E. O'Connor
> * Associate Computer Programmer/Analyst
> * OTDA - BDMA
> * (W) mailto:Patricia.O'Connor at otda.state.ny.us
> * (W) mailto:aa1160 at otda.state.ny.us
> ***********************************************************
>
>
> --------------------------------------------------------
> This e-mail, including any attachments, may be confidential, privileged or 
> otherwise legally protected. It is intended only for the addressee. If you 
> received this e-mail in error or from someone who was not authorized to 
> send it to you, do not disseminate, copy or otherwise use this e-mail or 
> its attachments.  Please notify the sender immediately by reply e-mail and 
> delete the e-mail from your system.
>
>
> ________________________________
>
>
> From: accessd-bounces at databaseadvisors.com on behalf of Michael R Mattys
> Sent: Thu 11/29/2007 11:01 PM
> To: AccessD
> Subject: [AccessD] Trim in Access 2007
>
>
>
> All fields in table defined as TEXT (39)
>
> I wrote:
> For Each fld In oDB.TableDefs("CASS").Fields
>    qdfTrim.SQL = "UPDATE CASS SET CASS.[" & fld.Name & "] = Trim([" &
> fld.Name & "])"
>    qdfTrim.Execute
> Next
>
> When I do a Len(AnyField), they are all 39.
> How do I know if Trim() worked or not?
> What's going on here?
>
> Michael R. Mattys
> MapPoint & Access Dev
> www.mattysconsulting.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com 
> <http://www.databaseadvisors.com/>
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com