Results 1 to 8 of 8
  1. #1
    accesscav is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    4

    Question Modifying a part naming database changing numbering from XX-YYY-ZZZ to XYZZZ

    I have a part numbering database written by an engineer with whom I no longer work. I want to change the current numbering structure from XX-YYY-ZZZZ to XYZZZ removing one X, Y & Z as well as the dash components. The XX component belongs to a product ID table, the YYY component belongs to a group ID table and the ZZZZ component is a sequential component of the partnumber table.
    Can this be done with minimal modifications? I am capable of changing the form layouts.

    Click image for larger version. 

Name:	Poduct ID.jpg 
Views:	21 
Size:	270.3 KB 
ID:	17091


    Click image for larger version. 

Name:	Group ID.jpg 
Views:	21 
Size:	271.5 KB 
ID:	17092


    Click image for larger version. 

Name:	PartNumber.jpg 
Views:	21 
Size:	284.2 KB 
ID:	17093


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I presume the Xs, Ys, Zs represent variable letters/numbers, not literally XX-YYY-ZZZZ. You show 3 Ys reduced to 1 - should that be 2 Ys?

    Is this hyphenated value saved somewhere?

    Which fields have the components that need to be changed?

    The GroupNumber and ProductNumber fields are calculated type. SequentialID field is number type. You want to limit to 3 digits?

    If you want to provide db for review, follow instructions at bottom of my post.
    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
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    Why not make the PartNumber field a calculated field, based on ProductKey, GroupKey & SequentialID?

    You could use format(ProductKey,"0") & format(GroupKey,"00") & format(SequentialID,"000") as the expression.

    HTH

    John

  4. #4
    accesscav is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    4
    I presume the Xs, Ys, Zs represent variable letters/numbers, not literally XX-YYY-ZZZZ. You show 3 Ys reduced to 1 - should that be 2 Ys?
    Yes, the single X & , sorry, the single Y represent names of product categories & groups and the 3 Zs are sequential identifiers (XYZZZ).

    Is this hyphenated value saved somewhere?
    Unfortunitly I haven't been able to identify where they are but as they are present when a part number is generated they are there somewhere.

    Which fields have the components that need to be changed?
    Other than the finding and removing the hyphens I believe the validation rules and the expressions need to be modified followed by the forms.

    The GroupNumber and ProductNumber fields are calculated type. SequentialID field is number type. You want to limit to 3 digits?
    Yes, 3 Zs.

    I will attempt to edit and send you the db.

    Current:
    Click image for larger version. 

Name:	XX-YYY-ZZZZ.jpg 
Views:	16 
Size:	45.1 KB 
ID:	17121


    What I need:
    Click image for larger version. 

Name:	XYZZZ.jpg 
Views:	16 
Size:	105.0 KB 
ID:	17122

  5. #5
    accesscav is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    4
    Hi John,

    Unfortunitly I am not that switched on with code. Looking at the current Group ID expression (2nd image on original post) can you translate what it says in simple english? Can all that be replaced with (GroupKey,"0") as you suggest and still run?

    Cheers

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    John_G is suggesting you create a field in query with an expression using Format() function to display the number values as you wish. Or do the calculation in textbox on form/report.

    Calculated type field does not recognize the Format() function so could not do in table. This is probably why the developer used the IIf() expression. Maybe change GroupNumber expression to:

    IIf([GroupID]<10, "0", "") + Trim(Str([GroupID]))

    None of that resolves the issue of the hyphens.
    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.

  7. #7
    accesscav is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    4
    I attempt to revise the expression as suggested without succes. I did however manage to locate and remove the hyphens without drama.

    I have attached the db. Have a look & a play and let me know what you think.

    The images below are the user logon & main interface. The pw is 1234

    Click image for larger version. 

Name:	db logon.jpg 
Views:	14 
Size:	70.7 KB 
ID:	17127Click image for larger version. 

Name:	db Main Interface.jpg 
Views:	14 
Size:	92.1 KB 
ID:	17128
    Attached Files Attached Files

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What does 'without success' mean - error message, wrong results, nothing happens?

    Editing the expression is simply just typing what you want or copy/paste what I posted. Works for me.
    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. Replies: 8
    Last Post: 05-21-2014, 07:21 PM
  2. Modifying Lending database
    By Nigelsw in forum Access
    Replies: 3
    Last Post: 07-18-2012, 10:22 AM
  3. Changing the century part of the date.
    By Chet in forum Queries
    Replies: 2
    Last Post: 05-13-2012, 08:27 PM
  4. MOdifying forms in split database
    By Ignace in forum Access
    Replies: 1
    Last Post: 05-11-2012, 06:36 AM
  5. Modifying a database
    By simba in forum Access
    Replies: 0
    Last Post: 06-15-2011, 11:32 AM

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