Results 1 to 7 of 7
  1. #1
    hnhpak is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jan 2015
    Location
    Karachi Pakistan
    Posts
    28

    Post Reduce text from one field and copy to other field

    Dear all

    I am making student database.
    I have following fields;
    1. Student ID
    2. Branch Code
    3. Academic Year
    4. Seven Digit ID



    Student ID contains full length ID having 13 digits and I want to reduce this ID to last seven digit only and the field is Text type field

    I am using VB code as under

    Me![Seven Digit ID] = [Student ID]
    Me![Card SNo] = "001"
    Me![STD Card ID] = [Branch Code] + "-" + [Seven Digit ID] + "-" + [Card SNo] + "-" + [Period]
    DoCmd.RefreshRecord

    The question is how can I copy [Student ID] (Only last seven digits) into [Seven Digit ID] field?

    Thanks in Advance

    Regards

    Haseeb ul Hasan

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why bother saving either of these calculated values? Why not just calculate when needed?

    Get the last 7 digits with:

    Right([Student ID],7)

    Also, & character is preferred concatenation operator.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    hnhpak is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jan 2015
    Location
    Karachi Pakistan
    Posts
    28
    Thank you very much, Matter solved..

  4. #4
    hnhpak is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jan 2015
    Location
    Karachi Pakistan
    Posts
    28
    I am using following codes to make specific [STD Card ID] from existing data.
    Private Sub Command0_Click()
    DoCmd.GoToRecord , , acFirst

    Do While Not Me.NewRecord

    Me![Seven Digit ID] = Right([Student ID], 7)
    Me![Card SNo] = "001"
    Me![STD Card ID] = [Branch Code] + "-" + [Seven Digit ID] + "-" + [Card SNo] + "-" + [Period]

    DoCmd.GoToRecord , , acNext

    DoCmd.RefreshRecord
    Loop

    End Sub



    Problem came when loop goes to end of file message came
    Run-Time Error '2015';
    "You can't go to the specific record"
    I want to eliminate this error, Is there any solution for this?
    Thanks in Advance

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why do you need to do this via looping records on form? Could probably just run an SQL UPDATE action.

    CurrentDb.Execute "UPDATE table SET [Seven Digit ID]=Right([Student ID],7), [Card SNo] = '001', [STD Card ID]=[Branch Code] & '-' & Right([Student ID],7) & '-001-' & [Period]"

    Would not be any issue if you just did not bother saving the calculated values.

    Why is the [Card SNo] = "001" for every record?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    hnhpak is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jan 2015
    Location
    Karachi Pakistan
    Posts
    28
    I am trying to resolve the matter with your guidance but still not successful may be due to some error in the statement.

    However the reason for [Card SNo]='001' is actually working as a check digit if someone lost his card the new card will be issue with another check digit so that the previous one will become void and cannot use on scanning with barcode scanner.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What does 'not successful' mean - error message, wrong results, nothing happens?

    The action SQL will change the [Card SNo] for every record in table. If you don't want that to happen, would need filter criteria in the statement.

    Instead of looping through records of form, loop through the form's recordsetclone.

    With Me.RecordsetClone
    While Not .EOF
    ![Seven Digit ID] = Right(![Student ID], 7)
    ![Card SNo] = "001"
    ![STD Card ID] = ![Branch Code] & "-" & ![Seven Digit ID] & "-" & ![Card SNo] & "-" & ![Period]
    .MoveNext
    Wend
    End With
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Copy text file to Memo Field
    By HDobler in forum Programming
    Replies: 5
    Last Post: 06-25-2021, 02:21 AM
  2. Replies: 5
    Last Post: 02-21-2012, 07:33 AM
  3. Reduce field size in query field
    By escapades_access in forum Queries
    Replies: 9
    Last Post: 03-13-2011, 12:21 PM
  4. Replies: 1
    Last Post: 03-13-2010, 08:38 PM
  5. Automatically Reduce Field Size
    By Rebecca in forum Access
    Replies: 4
    Last Post: 01-06-2010, 10:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums