[AccessD] Trim in Access 2007

Unfortunately, strTmp = Trim(oFld.Value) is invalid in this context.
Thank you anyway, Patricia. Feel free to try again. I'm stumped.

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

----- Original Message ----- 
From: "Michael R Mattys" <mmattys at rochester.rr.com>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Friday, November 30, 2007 8:05 AM
Subject: Re: [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
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com