Trim existing data in a field

G

Guest

I have a temporary table that is created by the following code:

Private Sub cb_remoteio_AfterUpdate()
Dim SQL As String

'Delete Temporary Table

DoCmd.SetWarnings False

'Close Table
DoCmd.Close acTable, "tbl_temp_Pwr_Rte"

'Delete Table
DoCmd.DeleteObject acTable, "tbl_temp_Pwr_Rte"

SQL = "Select CRS_CabFC, CRS_Rev, Clientname, ProjectName, RrtNm,
CRS_CabTag, CRS_ERN, ERDN, " & _
"CCRSDN, CCRSDNRevNo, ERD, CRS_CabDesig, CRS_CabDft, CRS_EqptS,
CRS_EqptD, " & _
"CRS_CabSets, CABLETYPE, JACK_CLR, INS_V, Util_Volt, UNGR_QAN,
UNGR_SIZE, NEUT_QAN, NEUT_SIZE, " & _
"GND_QAN, GND_SIZE, CABLE_A, CABLE_DIA, CABLE_WT, CBR, CRS_DNS,
CRS_DND, CRS_DNW, " & _
"Routing01, Routing02, Routing03, Routing04, Routing05,
Routing06, Routing07, Routing08, Routing09, Routing10, " & _
"Routing11, Routing12, Routing13, Routing14, Routing15,
Routing16, Routing17, Routing18, Routing19, Routing20, CRS_Remarks INTO
tbl_temp_Pwr_Rte " & _
"From Qry_PwrRt_All " & _
"Where CRS_ERN = [FORMS]![frmPWRRouteRPT]![cb_CTLERN] AND
(CRS_CABFC NOT LIKE '*MV*' and CRS_CABFC not like '*PV*' and CRS_CABFC not
like '*LV*') ORDER BY CRS_CabTag "

DoCmd.RunSQL SQL

Dim dbcurr As DAO.Database
Dim tdfcurr As DAO.TableDef

Set dbcurr = CurrentDb()
Set tdfcurr = dbcurr.TableDefs("tbl_temp_Pwr_Rte")
tdfcurr.Fields("RrtNm").Name = "Description"
dbcurr.TableDefs.Refresh

Set tdfcurr = Nothing
Set dbcurr = Nothing


DoCmd.SetWarnings True

'Preview Report
DoCmd.OpenReport "Rpt_Ctl_Cab_Sch", acViewPreview
End Sub

I need to take the "CRS_EQPTS" field and trim everything from it except for
the first 6 characters. I understand that I can use a Left Function to trim
to 6 characters, but how do I do it for each and every record?

Any suggestions?
 
K

kingston via AccessMonster.com

Instead of SELECT ... CRS_EQPTS ... FROM..., try:
SELECT ... Left(CRS_EQPTS,6) AS NewCRS_EQPTS ... FROM...
I have a temporary table that is created by the following code:

Private Sub cb_remoteio_AfterUpdate()
Dim SQL As String

'Delete Temporary Table

DoCmd.SetWarnings False

'Close Table
DoCmd.Close acTable, "tbl_temp_Pwr_Rte"

'Delete Table
DoCmd.DeleteObject acTable, "tbl_temp_Pwr_Rte"

SQL = "Select CRS_CabFC, CRS_Rev, Clientname, ProjectName, RrtNm,
CRS_CabTag, CRS_ERN, ERDN, " & _
"CCRSDN, CCRSDNRevNo, ERD, CRS_CabDesig, CRS_CabDft, CRS_EqptS,
CRS_EqptD, " & _
"CRS_CabSets, CABLETYPE, JACK_CLR, INS_V, Util_Volt, UNGR_QAN,
UNGR_SIZE, NEUT_QAN, NEUT_SIZE, " & _
"GND_QAN, GND_SIZE, CABLE_A, CABLE_DIA, CABLE_WT, CBR, CRS_DNS,
CRS_DND, CRS_DNW, " & _
"Routing01, Routing02, Routing03, Routing04, Routing05,
Routing06, Routing07, Routing08, Routing09, Routing10, " & _
"Routing11, Routing12, Routing13, Routing14, Routing15,
Routing16, Routing17, Routing18, Routing19, Routing20, CRS_Remarks INTO
tbl_temp_Pwr_Rte " & _
"From Qry_PwrRt_All " & _
"Where CRS_ERN = [FORMS]![frmPWRRouteRPT]![cb_CTLERN] AND
(CRS_CABFC NOT LIKE '*MV*' and CRS_CABFC not like '*PV*' and CRS_CABFC not
like '*LV*') ORDER BY CRS_CabTag "

DoCmd.RunSQL SQL

Dim dbcurr As DAO.Database
Dim tdfcurr As DAO.TableDef

Set dbcurr = CurrentDb()
Set tdfcurr = dbcurr.TableDefs("tbl_temp_Pwr_Rte")
tdfcurr.Fields("RrtNm").Name = "Description"
dbcurr.TableDefs.Refresh

Set tdfcurr = Nothing
Set dbcurr = Nothing


DoCmd.SetWarnings True

'Preview Report
DoCmd.OpenReport "Rpt_Ctl_Cab_Sch", acViewPreview
End Sub

I need to take the "CRS_EQPTS" field and trim everything from it except for
the first 6 characters. I understand that I can use a Left Function to trim
to 6 characters, but how do I do it for each and every record?

Any suggestions?
Click to expand...
 
Top