Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Novice seeking help...suppress hyphens

Discussion in 'Information Technology' started by bradjensmith@gmail.com, Jul 28, 2006.

  1. bradjensmith@gmail.com

    bradjensmith@gmail.com
    Expand Collapse
    Guest

    I have an easy vb question in MS Access. I'm looking to modify an
    account number field that may contain hyphens. Forgive my "ugly"
    code...

    Currently I read in the field from the table and write it directly back
    out.

    Here's the SQL that pulls the data (including the Account_num field):

    DIR_DEP_DISTRIB_tbl = "SELECT [DIRDEPDIST].*,DATASRCE_ID.US_CAN_IND " +
    _
    "FROM DIRDEPDIST INNER JOIN DATASRCE_ID ON
    DIRDEPDIST.EMPLID = DATASRCE_ID.EMPLID " + _
    "WHERE (((DATASRCE_ID.US_CAN_IND)='USA')); "

    and then later I'm currently outputting like this to a text file:

    Print #1, Tab(1); _
    in_recs.Fields("EMPLID").Value & "|" & _
    IIf(IsNull(in_recs.Fields("EFFDT").Value), "01JAN1900",
    Format(CDate(in_recs.Fields("EFFDT").Value), "ddmmmyyyy")) & "|" & _
    in_recs.Fields("PRIORITY").Value & "|" & _
    in_recs.Fields("ACCOUNT_NUM").Value & "|" & _
    in_recs.Fields("ACCOUNT_TYPE").Value & "|" & _
    in_recs.Fields("BANK_CD").Value & "|" & _
    in_recs.Fields("DEPOSIT_AMT").Value & "|" & _
    in_recs.Fields("AMOUNT_PCT").Value & "|" & _
    IIf(in_recs.Fields("DEPOSIT_TYPE").Value = "B", "Y", " ") & "|" & _
    Format(Date, "ddmmmyyyy") & "|INITLOAD|" & _
    Format(Date, "ddmmmyyyy") & "|INITLOAD";


    The ACCOUNT_NUM field is the field I need to modify to suppress hyphens
    if they appear.

    Thanks for any help!!
     
  2. Loading...

    Similar Threads Forum Date
    Do you believe in seeking help and advice on worldly matters from SGGS, via taking gur-vaaks ? Intellectual Articles May 19, 2015
    Seeking Sikh Mentor New to Sikhism Feb 13, 2014
    Controversial Margaret Doughty, Atheist Seeking U.S. Citizenship, Told To Join Church Or Be Denied Hard Talk Jun 20, 2013
    Opinion Seeking Sikhs Breaking News Aug 16, 2012
    Nature Seeking Enlightenment From Spirits and Forests in Japan Breaking News Jul 18, 2011

  3. hippomedon@googlemail.com

    hippomedon@googlemail.com
    Expand Collapse
    Guest

    Have you tried using the Replace function?
    Replace(in_recs.Fields("ACCOUNT_NUM").Value,"-","")

    It might do the trick.
    Paul
     
  4. bradjensmith@gmail.com

    bradjensmith@gmail.com
    Expand Collapse
    Guest

    Thanks Paul...Just stumbled upon this....Assigned a new variable
    ACCOUNT and did this:

    ACCOUNT = Replace(in_recs.Fields("ACCOUNT_NUM"), "-", "")

    Just to confirm, it appears that if the field doesn't contain "-" it
    still outputs correctly.

    Thanks for the help!!
     

Share This Page