Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 32
  1. #16
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Based on your data in post 12, this is the output I get. Please verify that the values and date formatting is correct (I used the date format from post 12):


    SN 11010309; 1969; January, 1970
    SN 11030037; 1969; March, 1970
    SN 11031092; 1969; March, 1970
    SN 11090656; 1969; September, 1970
    SN 810013157; 1969; January, 1970
    SN 810030966; 1969; March, 1970
    SN 810090794; 1969; September, 1970
    SN 810092037; 1969; September, 1970
    SN 810093565; 1969; September, 1970
    SN 810101788; 1969; October, 1970
    SN 810102451; 1969; October, 1970
    SN 810102963; 1969; October, 1970
    SN 819110090; 1969; November, 1969
    SN 820031158; 1969; March, 1970
    SN 820031638; 1969; March, 1970
    SN 820031682; 1969; March, 1970
    SN 820050522; 1969; May, 1970
    SN 820062769; 1969; June, 1970
    SN 829110997; 1969; November, 1969
    SN 13030003; 1970; March, 1971
    SN 22070259; 1970; July, 1971
    SN 22070702; 1970; July, 1971
    SN 213020034; 1970; February, 1973
    SN 223071296; 1970; July, 1973
    SN 233041098; 1970; April, 1973
    SN 811080160; 1970; August, 1971
    SN 814010655; 1970; January, 1974
    SN 814020080; 1970; February, 1974
    SN 822070617; 1970; July, 1972
    SN 224060949; 1970; June, 1974
    SN 811100142; 1980; October, 1981
    SN 811110142; 1980; November, 1981
    SN 240020607; 1969; February, 1970
    SN 240090023; 1969; September, 1970
    SN 26081159; 1970; August, 1971
    SN 26082681; 1970; August, 1971
    SN 27120190; 1970; December, 1970
    SN 27120292; 1970; December, 1970
    SN 37010899; 1970; January, 1970
    SN 857040443; 1970; April, 1977
    SN 858020705; 1970; February, 1978

    Not sure what to do with the results at the moment - the above is just a debug.print output. I presume the master list table is to be updated with the date string (e.g. February, 1978)? Would that be true only if the date field was empty?
    Note: when complete, the procedure code will be added to this thread in order to help others in the future.
    EDIT: also, you haven't specified what to do if the serial is neither 8 or 9 digits. It could be Null, or some other number?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #17
    LBPete is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2017
    Posts
    27
    Outstanding but it has a hiccup. It looks like leading zeros have been dropped from serial numbers. I intentionally did not include any eight digit serial numbers in the list I posted earlier. In your sample all the nine digit serial numbers have the correct production date. All of the serial numbers with eight digits have incorrect production dates. But this is a huge step forward!

    The serial number field in the Master Table is the primary key so it would never be blank. There are many legitimate eight digit serial numbers in the Master Table so if possible, the coding should be able to identify that variant. These Eight digit serial numbers were used on some equipment made before the late 1960s. They are also used on certain equipment types, specifically speakers.

    There are also a substantial number of errors in the serial numbers on file. Some have too many digits or not enough digits. Others have alpha characters which is not valid for a serial number. Other have digits larger than 12 in the month position. This is the nature of the data. The actual serial numbers on the equipment can be hard to read and add to that fat fingered typing. If the serial number has any error in it that trips up the decoding, an error message can be placed in the production date field. Something like "Invalid Date Code."

    The goal is to have the production date in Master List Table updated with your coding. The calculation can be done outside the table and then appended in if that is better or easier. There are a significant number of decade errors in the existing data. I would like to be able to correct these errors by simply correcting the decade field.

    Please let me know if there is anything else you need and thank you for your help.

    - Pete

  3. #18
    LBPete is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2017
    Posts
    27
    Here are some samples of legitimate eight digit serial numbers. I've also included one with an invalid serial number.

    Model Serial Number Production Date Decade Entry Date Reporter ID Notes
    SP-5000A 97010247 January, 1987 1980 6/20/05 Import from AK 2005-06-20 2596
    SP-5000A 97010667 January, 1987 1980 6/20/05 Import from AK 2005-06-20 2596
    SP-5500X 97801846 Invalid Date Code 1979 12/24/03 759
    SP-70 91021333 February, 1981 1978 5/20/07 2455
    SP-70 91021356 February, 1981 1978 5/20/07 2455
    SP-70 91021583 February, 1981 1978 2/4/17 2455
    SP-70 91111687 November, 1981 1978 2/4/17 2455
    SP-7500X 97021131 February, 1977 1970 10/19/05 711
    SP-7500X 97022726 February, 1977 1970 10/19/05 711
    SP-7500X 95070961 July, 1975 1970 5/22/06 711
    SP-7500X 95071061 July, 1975 1970 5/22/06 711

  4. #19
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Re: post 17: Not to worry about the leading zeros. In updating only the Production Date field, they will not be affected. I will see what's wrong with the 8 digit records, but I was following your first post
    Some of the serial numbers are only 8 digits with the second digit indicating the year and the 3 and 4 digits indicating the month.
    but I may have coded it incorrectly if the above is correct. I'll review your new data.
    I will also include a check for alpha characters and if the 2 character date exceeds 13 by branching into separate functions and return a text value for each. Just so you know, this code isn't something you put into a table of any sort. You either have to know how to call a module level function manually, or have a form button or macro that can do that. However the code is called, the procedure will update your field with either the date or a text indicating the issue. This will be a one or the other thing; i.e. if it has both conditions that make it invalid, only one will be processed otherwise the coding gets a bit more involved. A subsequent code execution would catch the second issue.

    Should the procedure not run if the date field already contains a value that resembles a date? If it does, the calculated value would over-write existing data, which may not be a good idea.
    Last edited by Micron; 05-09-2017 at 03:30 PM. Reason: clarification

  5. #20
    Join Date
    Apr 2017
    Posts
    1,673
    Hi

    Maybe it'll be easier to code 2-number year + month number as 3-character long 32-bit number (My advice is to use Crockford's Base32 format). It allows to code up to 32767 different numbers. Of-course you have to set the century switch (p.e. month numbers over 5000 are for previous century, and less than 5000 are for current one). And you have to write 2 functions - the one to convert regulary numbers to 32-bit ones and the other for reverse conversion).
    Some examples:
    Month ShortMonth 32BitNumber
    197501 7501 7ad
    199912 9912 9nr
    200001 1 001
    201705 1705 ln9

    Btw. When you use 4-characters 32-bit string, you can code all dates from 01.01.19000 up to 24.11.4770 (1 048 575 different numbers, but you have to check, does Access allow so much).
    I have written converters for SQL server, there the limit is 6-character 32 bit number zzzzzz which represents the BigInt number 1 073 741 823. More is not possible because biggest number in SQL is not much over this number.
    Last edited by ArviLaanemets; 05-10-2017 at 07:06 AM.

  6. #21
    Join Date
    Apr 2017
    Posts
    1,673
    Hi

    An addition to my previous post.

    There is a way to code all months from January 1900 up to December 2227 with 3-character 32-bit string. Simply deduce 1900 from year number. i.e. the number 7501 represents January 1975, 10001 represents January 2000, etc. The latest month possible to convert will be 32712 (shortly under upper limit for 3-charcacter 32-bit string) which represents December 2227.
    Last edited by ArviLaanemets; 05-10-2017 at 10:44 AM.

  7. #22
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you want to take over because you have a better idea, then by all means, feel free. No skin off my nose
    For me, the issue is not how to make it happen, it's understanding the logic why it is that when the decade value is 1969 that a production year would be (e.g.) 1971 when I would have guessed 1970. OP has provided me with some further explanation which I have yet to read, but in the meantime, let us know if you want to take a crack at it. I won't be going down the path you suggest as I have little experience with those numeric bases.

  8. #23
    LBPete is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2017
    Posts
    27
    As far as the decade reference, you can only count on the it being less than the first (lowest) production date. There is no consistency in the gap between the year digit in the serial number and the 4th digit of the decade reference.

    In a nine digit serial number, the production year is always the third digit. If this year digit is greater than the 4th digit of the decade reference, it means the production took place in the same decade as the decade reference. The production year is the first three digits of the decade reference and the year digit as the 4th digit of the of the production year.

    If the year digit is less then the 4th digit of the decade reference, it means the production took place in the next decade. The production year is then the first three digits of the decade reference with the year digit as the 4th digit of the production year + 10 to shift the production year to the next decade.

    Here's two examples. The first is a year digit that is greater than the 4th digit of the decade reference, the other has a year digit that is less then the 4th digit of the decade reference.

    Model Serial Number Production Date Decade Entry Date Reporter ID Notes
    1000A 118062907 June, 1968 1966 12/30/05 Import from AK 2005-12-30 492
    1000X 010080554 August, 1970 1968 10/21/04 Import from AK 649

    For the 1000A model, the year digit is an 8 and the decade reference is 1966. 196, the first three digits of the decade reference then place the year digit of the serial number (8) as the 4th digit of the production year, 196(8)

    For the 1000X model, the year digit is a 0 and the decade reference is 1968. If the year digit is less then the 4th digit of the decade reference, the production took place in the next decade. There are two ways to do this, use the first three digits of the decade reference with the year digit as the 4th digit of the production date. Then to shift it to the next decade, add 10. 196(0) + 10 = 1970.
    An alternate method is to use the first two digits of the decade reference, add 1 to the third digits and the year digit from the serial number as the 4th digit of the production year. 19(6+1)0 = 1970.

    Hopefully this will clear it up.

    - Pete

  9. #24
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Micron View Post
    If you want to take over because you have a better idea, then by all means, feel free. No skin off my nose
    Mypostwas more for OP in line "In case you plan to scrap this and start with something more standardized"

    In current setup I don't see any good solutions. Maybe a reference table (Model, SNLength, MonthString, Year, Month)? But I'm not sure I get the logic in current setup at all

  10. #25
    LBPete is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2017
    Posts
    27
    Quote Originally Posted by ArviLaanemets View Post
    Mypostwas more for OP in line "In case you plan to scrap this and start with something more standardized"

    In current setup I don't see any good solutions. Maybe a reference table (Model, SNLength, MonthString, Year, Month)? But I'm not sure I get the logic in current setup at all
    I don't understand your suggestion. We are not creating a new date code. The date code was created in the 1960s and was used into the 1990s. What we are discussing is how to take this 3 digit date code and convert it into a month,year format. There are over 600 distinct models involved. It would be a chore to create a table defining the production range of each of these models.

    - Pete

  11. #26
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by LBPete
    I update the master table monthly and that's when I assign the production dates
    Quote Originally Posted by LBPete View Post
    The date code was created in the 1960s and was used into the 1990s
    Here is where I was confused! I assumed the production continues, but it looks like you are selling spares or something like this.

    Quote Originally Posted by LBPete View Post
    What we are discussing is how to take this 3 digit date code and convert it into a month,year format
    No way for this without some additional info! P.e. '101' in serial means, that the part was produced either in January 1961, January 1971, January 1981 or January 1991. To decide which is right one automatically, you need info you don't have in this 3-character string.

    Btw. there is some confusion with placement of this 3-character string too:
    1. Some serials are 9-numbers long;
    2. Somewhere you mentioned, that leading zeroes were lost, and as result you get 8number serials. This indicates, that serials are strings, and currently some of them are wrong.
    3. Then you are discussing 8-number and 9-number serials as regular ones. Does this mean there are 8-number serials not messed up too? When this is the case, can you always say, that the starting position for date part is (3-(9-Len(Serial)))? When not, then you have a very big problem!

    Quote Originally Posted by LBPete View Post
    There are over 600 distinct models involved. It would be a chore to create a table defining the production range of each of these models
    And it is less work to enter the same info (Decade, which for me looks like model's production start year instead) into your Master table for every single part? Maybe yes, when the number of entries in Master table will be small enough. But again, you can add models to junction table only, when you need to add to Master table an entry for new model (only once for model). And you don't need the Decade field in Master table anymore - you can read this from junction table too.

  12. #27
    LBPete is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2017
    Posts
    27
    ArviLaanemets, I appreciate your advice, recommendations and willingness to help but almost everything you are bringing up here has been addressed elsewhere in this thread and in some cases more than once. I'm not sure that rehashing it again will make it any more clear.

    - Pete

  13. #28
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The attached seems to work. Try altering tblMain data when testing by adding alpha or special characters, extra or less characters than 8 or 9 etc.
    The data you posted seems quite clean except for one apparently invalid date portion of a serial. Lots of notes. Hope you like it as it was a fairly involved solution.
    Open db then open frmMain and take it from there.

    RadioSerial.accdb

  14. #29
    LBPete is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2017
    Posts
    27
    Great! Thanks, allow me some time to work with it.

    - Pete

  15. #30
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    For those following: Lotsa pm'ing went on to sort out details which avoided a whole lot of unnecessary back & forth posting. Aside from table/field design changes, this one also has the following changes:

    - don't see value of PK in errors table so I removed it
    - added handling of Nulls in case source table is missing serial numbers
    - nothing is written to any table if the serial field in the source table contains Nulls
    - added a msgbox in case no records are returned to the record set
    - created field aliases for query as having same field names in both tables (as requested) causes the second instance of a field to show its full reference.

    RadioSerial.accdb

    Hopefully this design does the trick.
    Last edited by Micron; 05-18-2017 at 03:02 PM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Extracting DOB from ID Number
    By lmahere in forum Programming
    Replies: 8
    Last Post: 04-01-2018, 09:01 PM
  2. Extracting text from a string
    By Whughes98 in forum Queries
    Replies: 5
    Last Post: 04-17-2017, 11:18 AM
  3. extracting pdf files question
    By slimjen in forum Programming
    Replies: 3
    Last Post: 03-11-2015, 08:45 AM
  4. Replies: 9
    Last Post: 04-26-2013, 12:12 PM
  5. Write code to see all date between two date
    By barkarlo in forum Programming
    Replies: 2
    Last Post: 02-24-2013, 10:58 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