Removing numbers from a string

[ozdemirozgur]

i have a table which has company codes. It is a text field but actually company codes are numbers with zeros in the beginning like 00020, 00021, 00153 etc. When I move these values to another table which also has the company codes and also a text type field, it removes the zeros in the beginning which is somthing I do not want. any idea why it might happen ?

thanks, oz

 

[bob fitz]

How are you moving the codes?

 

[gemma-the-husky]

I would think somewhere, you are (maybe effectively and inadvertently) converting the text value to a number

one reason may be non declaring variables. Do you have option explicit at the top of your code modules?

 

[ozdemirozgur]

Hi

my code is below.


Do While Not a.EOF

Coy_Id = a!Coy_Id

DoCmd.SetWarnings False

If (a!OO_DUBNo = 1) Then

If (a!Key > 1) Then
DoCmd.RunSQL "INSERT INTO b value (Coy_Id)"
End If

 

[boblarson]

Hi

my code is below.


Do While Not a.EOF

Coy_Id = a!Coy_Id

DoCmd.SetWarnings False

If (a!OO_DUBNo = 1) Then

If (a!Key > 1) Then
DoCmd.RunSQL "INSERT INTO b value (Coy_Id)"
End If

That is not the full code. Post the WHOLE thing. I can tell it isn't the full thing because you have
Do While Not a.EOF

and so where does that come from, etc. You need to post the ENTIRE procedure INCLUDING the declarations.

Dave is likely correct. You probably have Coy_ID declared as a Variant, either explicitly or implicitly.

 

[ozdemirozgur]

Hi,

I have the entire code below;

Option Compare Database
Option Explicit
 
Sub UpdateFinanceIsinandCountries()
 
    Dim myDB   As Database
    Dim HOLDLive_2000_Combined As Recordset
    Dim HOLDLive_2000_Combined_NoDUBS As Recordset
    Dim Coy_Id As String
    Dim OO_ShareholderId As String
    Dim HOLD_DATE As Date
    Dim CURR_PCENT As Double
    Dim DubNo  As Integer
    Dim SHRHLDRID As String
    Dim OFF_RECNO As String
    Dim OO_DUBNo As Double
    Dim Key    As Double
 
    DubNo = 0
 
    Set myDB = CurrentDb()
 
    Set HOLDLive_2000_Combined = myDB.OpenRecordset("HOLDLive_2000_Combined", dbOpenTable)
 
    Set HOLDLive_2000_Combined_NoDUBS = myDB.OpenRecordset("HOLDLive_2000_Combined_NoDUBS", dbOpenTable)
 
    With HOLDLive_2000_Combined
        .Index = "Coy_Id"
 
        HOLDLive_2000_Combined.MoveFirst
 
        Do While Not HOLDLive_2000_Combined.EOF
 
            Coy_Id = HOLDLive_2000_Combined!Coy_Id
 
            OO_ShareholderId = HOLDLive_2000_Combined!OO_ShareholderId
 
            HOLD_DATE = HOLDLive_2000_Combined!HOLD_DATE
 
            CURR_PCENT = HOLDLive_2000_Combined!CURR_PCENT
 
            SHRHLDRID = HOLDLive_2000_Combined!SHRHLDRID & ""
 
            OFF_RECNO = HOLDLive_2000_Combined!OFF_RECNO & ""
 
            OO_DUBNo = HOLDLive_2000_Combined!OO_DUBNo
 
            Key = HOLDLive_2000_Combined!Key
 
            DoCmd.SetWarnings False
 
            If (HOLDLive_2000_Combined!OO_DUBNo = 1) Then
 
                If (HOLDLive_2000_Combined!Key > 1) Then
 
                    DoCmd.RunSQL "INSERT INTO HOLDLive_2000_Combined_NoDUBS (Coy_Id, OO_ShareholderID, Hold_Date, CURR_PCENT, SHRHLDRID, OFF_RECNO, OO_DubNo, KEY) values (" & Coy_Id & ",""" & OO_ShareholderId & """,""" & HOLD_DATE & """,""" & CURR_PCENT & """,""" & SHRHLDRID & """,""" & OFF_RECNO & """,""" & OO_DUBNo & """,""" & Key & """)"
 
                End If
 
 
                Coy_Id = HOLDLive_2000_Combined!Coy_Id
 
                OO_ShareholderId = HOLDLive_2000_Combined!OO_ShareholderId
 
                HOLD_DATE = HOLDLive_2000_Combined!HOLD_DATE
 
                CURR_PCENT = HOLDLive_2000_Combined!CURR_PCENT
 
                SHRHLDRID = HOLDLive_2000_Combined!SHRHLDRID & ""
 
                OFF_RECNO = HOLDLive_2000_Combined!OFF_RECNO & ""
 
                OO_DUBNo = HOLDLive_2000_Combined!OO_DUBNo
 
                Key = HOLDLive_2000_Combined!Key
 
            End If
 
            If (HOLDLive_2000_Combined!OO_DUBNo > 1) Then
 
                Coy_Id = HOLDLive_2000_Combined!Coy_Id
 
                OO_ShareholderId = HOLDLive_2000_Combined!OO_ShareholderId
 
                HOLD_DATE = HOLDLive_2000_Combined!HOLD_DATE
 
                CURR_PCENT = HOLDLive_2000_Combined!CURR_PCENT
 
                SHRHLDRID = HOLDLive_2000_Combined!SHRHLDRID & ""
 
                OFF_RECNO = HOLDLive_2000_Combined!OFF_RECNO & ""
 
                OO_DUBNo = HOLDLive_2000_Combined!OO_DUBNo
 
                Key = HOLDLive_2000_Combined!Key
 
            End If
 
            HOLDLive_2000_Combined.MoveNext
 
        Loop
 
    End With
 
End Sub

 

[boblarson]

Thank you but next time you post large bits of code, please use Code Tags:

 

[ozdemirozgur]

Hi Bob,

thanks for your reply. but I did not understand what to change in my code?

thanks, ozgur

 

[boblarson]

Hi Bob,

thanks for your reply. but I did not understand what to change in my code?

thanks, ozgur

I didn't come back with an answer for that. I just added code tags and indenting to your code to make it more readable and then I posted that you need to use code tags here on the forum when posting long bits of code.

From what I've seen of your code, it may hinge on what the SQL is of which the query "HOLDLive_2000_Combined" consists.

 

[ozdemirozgur]

Hi,

do you have any idea, how can I change to code to deal with the problem above?

thanks, Ozgur

 

[boblarson]

Hi,

do you have any idea, how can I change to code to deal with the problem above?

thanks, Ozgur

What is the SQL of the query HOLDLive_2000_Combined?

 

[ozdemirozgur]

Hi,

please find the attached code. Basically, when I move the company code (COY_ID) from one tabke to another - text field in both tables, it removes the leading zeros. eg 00021 becomes 21, 00153 becomes 153.

thanks, ozgur

 

[boblarson]

Hi,

please find the attached code. Basically, when I move the company code (COY_ID) from one tabke to another - text field in both tables, it removes the leading zeros. eg 00021 becomes 21, 00153 becomes 153.

thanks, ozgur

Sorry but that wasn't the answer to my question. Please answer it so we can move on:

What is the SQL of the query HOLDLive_2000_Combined?

So that means - go into the query in design view, and then go to VIEW > SQL VIEW and then paste the SQL string that it is showing.

 

[ozdemirozgur]

Hi Bob,

this is not a query. This is a code written in a module.

thanks, ozgur

 

[VilaRestal]

What Bob means ozgur is:
The line

Set HOLDLive_2000_Combined = myDB.OpenRecordset("HOLDLive_2000_Combined", dbOpenTable)

Is opening a query called HOLDLive_2000_Combined (it's obviously not a table)
He wants to see the SQL of that query

One possible source of the error that I can see is in the line

DoCmd.RunSQL "INSERT INTO HOLDLive_2000_Combined_NoDUBS (Coy_Id, OO_ShareholderID, Hold_Date, CURR_PCENT, SHRHLDRID, OFF_RECNO, OO_DubNo, KEY) values (" & Coy_Id & ",""" & OO_ShareholderId & """,""" & HOLD_DATE & """,""" & CURR_PCENT & """,""" & SHRHLDRID & """,""" & OFF_RECNO & """,""" & OO_DUBNo & """,""" & Key & """)"

Coy_Id (the variable) isn't enclosed in quotation marks

Try replacing that line with:

DoCmd.RunSQL "INSERT INTO HOLDLive_2000_Combined_NoDUBS (Coy_Id, OO_ShareholderID, Hold_Date, CURR_PCENT, SHRHLDRID, OFF_RECNO, OO_DubNo, KEY) values ('" & Coy_Id & "',""" & OO_ShareholderId & """,""" & HOLD_DATE & """,""" & CURR_PCENT & """,""" & SHRHLDRID & """,""" & OFF_RECNO & """,""" & OO_DUBNo & """,""" & Key & """)"

(note the addition of 2 apostrophes)