Trim 000s from a table column 2

[intrepidtechie]

Hello Friends,
This is the situation. I have a Access table that gets new data imported from another table (from the mainframe) through a VB process. Once the table gets updated one of the columns have extra precedding 00s..for example..instead of showing 384799474..it shows 00000384799474. Also the other thing I want to mention is the number of precedding 00s are not consistent. some are field values have 3 zeros while some might have only 2 zeros.

Is there any method to remove to zeros.
I can go with 2 options:
1. either remove the zeros before it gets imported..that means right there in the VB import routine..
2. Have a query that runs automatically each time the table gets udpated.

Please help at the earliest..

Thanks..

 

[RoyVidar]

As we don't know how you import, it is hard to address that.

I think you should be able to use something like this in a query:

[tt]update yourtable set yourfield = val(yourfield)[/tt]

Roy-Vidar

 

[intrepidtechie]

Hello Roy,
This is a simple import from a DB2 database.
If you want I can send you the VB process file..just give me your mail id

 

[RoyVidar]

This isn't quite how these forums work, have a look at for instance this faq on how to get the best answers faq222-2244 (#12 relates to this, but the whole faq is interesting in terms of how to get the most out of the membership. For instance #5 deals with crossposting (thread709-947391), which isn't exactly encouaraged either;-)).

Post the code here, if you need assistance with it (re #14), then someone will perhaps have a go at it.

Didn't the query work?

Roy-Vidar

 

[TheAceMan1]

How are ya intrepidtechie . . . . .

[blue]Roy Vidar[/blue] has already suggested what you need . . . . an [blue]Update Query[/blue], and its all done in one shot . . . .

If your not sure how to make an [blue]Update Query[/blue], let us know and we'll step you thru it . . .

See Ya! . . . . . .

 

[intrepidtechie]

I am sorry guys..I am a newbie on tek-tips..
If you can explain how the update query would do the job..please show me..

 

[intrepidtechie]

Hello Guys here is the code in Vb if this helps..
the number in question is ACCOUNT_NBR (ACCOUNT_NUMBER)
================================================
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Importing Data table to Access
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
strQuery = "DELETE * FROM IKONICSPAYSYSTAG;"
conn.Execute strQuery
strQuery = "INSERT INTO IKONICSPAYSYSTAG ( APPLICATION_CD, ORGANIZATION_CD, ACCOUNT_NBR, RECORD_ID, CUSTOMER_NAME, CUSTOMER_NBR, ZIP_CD, CUR_BAL_AMT, DAY_DELQ_COUNT, USER_1_ALPHA_TXT, USER_2_CD, USER_3_CD, REJECT_COND_CD, USER_2_ALPHA_TXT, BILLTO_FUNC_PSTDUE_AMT )" & _
" SELECT [APPLICATION_CD], [ORGANIZATION_CD], [ACCOUNT_NBR], [RECORD_ID], [CUSTOMER_NAME], [CUSTOMER_NBR], [ZIP_CD], [USER_4_AMT], [DAY_DELQ_COUNT], [USER_1_ALPHA_TXT], [USER_2_CD], [USER_3_CD], [REJECT_COND_CD], [USER_2_ALPHA_TXT], [CUR_BAL_AMT] " & _
" FROM " & strTableName
conn.Execute strQuery

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Change the Account_Number to remove 00s in this function.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
strQuery = "DELETE * FROM PaysysTag;"
conn.Execute strQuery
strQuery = "INSERT INTO PaysysTag ( CUSTOMER_NAME, ACCOUNT_NUMBER, CUSTOMER_NBR, CUR_BAL_AMT, DAY_DELQ_CNT, USER_1_ALPHA_TXT, ZIP_CD, USER_2_CD, USER_3_CD, USER_2_ALPHA_TXT, BILLTO_FUNC_PSTDUE_AMT )" & _
" SELECT CPT.CUSTOMER_NAME, CPT.ACCOUNT_NBR, CPT.CUSTOMER_NBR, CPT.CUR_BAL_AMT, CPT.DAY_DELQ_COUNT, CPT.USER_1_ALPHA_TXT, CPT.ZIP_CD, CPT.USER_2_CD, CPT.USER_3_CD, USER_2_ALPHA_TXT, BILLTO_FUNC_PSTDUE_AMT " & _
" FROM IKONICSPAYSYSTAG AS CPT, " & _
" (SELECT CPT.APPLICATION_CD, CPT.ORGANIZATION_CD, CPT.ACCOUNT_NBR, Max(CPT.RECORD_ID) AS RECID " & _
" FROM IKONICSPAYSYSTAG AS CPT " & _
" WHERE CPT.RECORD_ID<>'6' " & _
" GROUP BY CPT.APPLICATION_CD, CPT.ORGANIZATION_CD, CPT.ACCOUNT_NBR) AS QDPT " & _
" WHERE (((CPT.ACCOUNT_NBR)=QDPT.ACCOUNT_NBR) And ((CPT.APPLICATION_CD)=QDPT.APPLICATION_CD) And ((CPT.ORGANIZATION_CD)=QDPT.ORGANIZATION_CD) And ((CPT.RECORD_ID)=QDPT.RECID) And CPT.REJECT_COND_CD = ' '); "
conn.Execute strQuery

End Sub
================================================

 

[RoyVidar]

Did you try the val function?

- in the select

[tt]" SELECT CPT.CUSTOMER_NAME, val(CPT.ACCOUNT_NBR), CPT.CUSTOMER_NBR, CPT.CUR_BAL_AMT, ..."[/tt]

or MarkSweetland's suggestion

[tt]" SELECT CPT.CUSTOMER_NAME, format(CPT.ACCOUNT_NBR,'#'), CPT.CUSTOMER_NBR, CPT.CUR_BAL_AMT, ..."[/tt]

Roy-Vidar

 

[TheAceMan1]

intrepidtechie . . . . .

[blue]RoyVidars[/blue] suggestions are on target and should work (no problemo!). [blue]They enable full automation of the process you seek![/blue] For future reference, you'll find [purple]update[/purple] queries very useful. [blue]They're easy to make, fast in execution, and allow you to continue your work[/blue] where other conversion methods fail; or at least until you work them out. The following is a small synopsis of getting an [blue]Update Query[/blue] up and running:

[blue]Reference for making an Update Query[/blue]

[ol]
[li]Goto the Query Window; select [purple]New[/purple]; double-click [purple]Design View[/purple]; double-click [purple]your table name[/purple]; click [purple]Close[/purple].[/li]
[li]On the menubar select [purple]Query[/purple] - [purple]Update Query[/purple][/li]
[li]From the field list of your table; double-click [purple]ACCOUNT_NBR[/purple]. [blue]The field should now appear in the query grid.[/blue][/li]
[li]In the [purple]Update To:[/purple] row, copy/paste [purple]Val([ACCOUNT_NBR])[/purple][/li]
[li]Close, Save, Name the query.[/li][/ol]

To execute fron the query window; just click or double-click the query.

To execute [blue]from VBA[/blue]; [purple]DoCmd.OpenQuery "YourQueryName"[/purple]

See Ya! . . . . . .

 

[intrepidtechie]

Roy/Mark...you guys have been just awesome.
One last question..so if I go according to Mark's syntax my code would be

============================================
" SELECT CPT.CUSTOMER_NAME, format(CPT.ACCOUNT_NBR,'#'), CPT.CUSTOMER_NBR, CPT.CUR_BAL_AMT, CPT.DAY_DELQ_COUNT, CPT.USER_1_ALPHA_TXT, CPT.ZIP_CD, CPT.USER_2_CD, CPT.USER_3_CD, USER_2_ALPHA_TXT, BILLTO_FUNC_PSTDUE_AMT " & _
============================================
and if I go by Roy's syntax it would be

======================================
" SELECT CPT.CUSTOMER_NAME, val(CPT.ACCOUNT_NBR), CPT.CUSTOMER_NBR, CPT.CUR_BAL_AMT, CPT.DAY_DELQ_COUNT, CPT.USER_1_ALPHA_TXT, CPT.ZIP_CD, CPT.USER_2_CD, CPT.USER_3_CD, USER_2_ALPHA_TXT, BILLTO_FUNC_PSTDUE_AMT " & _
======================================

Am I correct guys?
Please confirm.