[AccessD] Trim in Access 2007

O'Connor, Patricia (OTDA) Patricia.O'Connor at otda.state.ny.us
Thu Nov 29 23:52:06 CST 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/>





More information about the AccessD mailing list