Error 5: Invalid procedure call of argument

[LBPete]

I've been using code that was created by Micron in this thread: https://www.accessforums.net/showthread.php?t=65915. It's been working flawlessly until today. When I run the macro, I get the captioned error code. The file processes but drops roughly 1/4 of the lines of data. I don't see anything unique about the data it drops but running it several times, it's always the same data that drops. I have not made any changes to the code, database or procedure but now I get this error.

The access database is attached. Please be kind with your comments.

Any help is appreciated.



- Pete

[Micron]

In CalcModelYear, the built in MonthName function is being passed "00".
Obviously that cannot be. Will take some digging, but there is probably something wrong with the data or at least with the way we expected it to be parsed.

There is a missing table (because it's linked). Do I need it?

EDIT:
This is the 1st serial that fails (there may be others): 231009818
I cannot recall the logic or parameters you long ago said what format these take. Something like the month was the 4th and 5th characters maybe? Consider this serial and let me know if anything pops out at you.
EDIT2: Yes

Most of the serial numbers are 9 digits with the third digit representing the year and the 4th and 5th digits representing the month. xx507xxxx for example would indicate July, 1975 production for a given model.

[orange]

What exactly do you have to do to see the problem?
Micron has been around, so is suggest we wait for his comment.

[Micron]

Among other tests, the db already checked for serials that
- contained alpha
- are >9 or <8 in length
- based on an 8 character (positions 3,4) or 9 character (positions 4,5) serial, where the month >12

While waiting, I figured out that there ought to be a test for when the month part of a serial is 00. This is done, but I'm holding off posting the revised db until there's a response from OP to find out if there's anything else to consider. The issue with 231009818 is that the MonthName function errs out because the month value is 00.

[LBPete]

In CalcModelYear, the built in MonthName function is being passed "00".
Obviously that cannot be. Will take some digging, but there is probably something wrong with the data or at least with the way we expected it to be parsed.

There is a missing table (because it's linked). Do I need it?

No, that linked table is used to assign the decade reference. The error code occurs later in the process

EDIT:
This is the 1st serial that fails (there may be others): 231009818
I cannot recall the logic or parameters you long ago said what format these take. Something like the month was the 4th and 5th characters maybe? Consider this serial and let me know if anything pops out at you.
EDIT2: Yes

The manufacture year is represented as the third digit of a 9 digit serial number or the second digit of an 8 digit serial number. If I remember correctly, the logic compares it to a "decade" reference. if the 8/9th digit is larger than or equal to the 4th digit of the decade reference, it assigns the same decade as the reference. If it is smaller, it assigns the next decade.

Example 1: serial number 226071081. Third digit = 6, decade reference = 1975. 6 > 5, manufacture year = 1976
Example 2: serial number 021070485. Third digit = 1, decade reference = 1967. 1 < 7, manufacture year = 1971

Taking a second look at the data that is dropping out, most of them cross over a decade. Many of them have a decade reference ending in zero like 1980, 1970. The decade reference for the serial number you posted above, 231009818, is 1980. The decade references for all the serial numbers that are dropping out are in the "Update Input Table."

- Pete

[Micron]

I was able to trap the error. Trust me when I tell you that the MonthName function was being passed "00" and I've provided the guilty serial number. You can see that for such a 10 digit serial, the 4th and 5th values (month) are zeros. Obviously, there can be no such month. When the error happens, no further code is executed because the error was not handled (obviously unforeseen). If code stops, all subsequent records in the table are not processed. I'm not saying there's nothing wrong with the other records; I leave that up to your expertise to decide. However, I'm suggesting the problem is due to the values where the month should be. Either the serial is wrong, or there are now exceptions to the pattern we started with. Using your example,

serial number 231009818. Third digit = 1, decade reference = 1980 : 1 > 0, manufacture year = 1981

which seems to fit the logic (I believe you are correct in your recollection of how it works - the notes spell it out & I could post them if need be). If that's the case, what would be the issue regarding the decade information? Not sure I'm following that part.

At this point, I have coded to trap a 00 month for either 8 or 9 digits, but I'm not understanding whether or not there is something else amiss. When I run it now, there are no errors and this is what's in the error table

SerialNum_FK	Message
021400270	Invalid Month
39360527	Invalid Month
660921743	Invalid Month
231009818	Month digits are zeros
8211002741	Not 8 or 9 chars

The "month digits are zeros" message is part of the current fix.
BTW, I don't think I got an email re your post even though this response is about 2 hours later than yours.

[LBPete]

Sorry, I missed the invalid month in the serial number you posted. Invalid month is a common error, I should have seen it. In this case, I pulled that serial number from an eBay auction and went back and checked the image with the serial number. It is indeed 00 which means this actual serial number does not follow the serial number standard. I've never seen that before with a Sansui turntable. The only other examples of non-compliant serial numbers are from equipment that was probably sub contracted to an outside supplier. In those cases, the serial numbers don't have any common elements with a standard format serial number.

This one however, follows the standard form except for the month. Very odd. The first two digits should indicate the production line. I've queried the almost 7000 serial numbers we have on file to see what else was produced on production line 23 with a year code of 1. All 21 hits on that query, occurred in 1971. No turntables. The Sansui logo that appears on the turntable in question is much later than 1971, so I'm assuming that this turntable was produced in 1981 and, despite it's Sansui logo, was not produced on a Sansui production line.

Aside from the possibility of this double zero happening again, I can't think of anything that needs to be addressed. I've processed several hundred serial number using your code and up to this point, it's worked flawlessly.

A nice to have would be if the model name carried forward to tblSerial Errors along with the serial number. I cross check these numbers to see if I can correct the error. It's not necessary to have the model carry over, I can use a search tool but with the model name, I could just pick out with a visual scan.

I do appreciate your help with this. Myself and the other Sansui enthusiast learn a lot from these serial numbers. This oddball turntable is an example of that.

- Pete

[Micron]

So for serial 021400270 from tblSerialErrors (because the month value is 40), the model can be found in the Decade Update table (800)? Or Update Input table? Or either?

Maybe the eBay tt isn't what it appears to be...

[LBPete]

When a serial number like that one, bounces to tblSerialErrors, I go back and look at the original source information to see if there is an obvious error. I'll reference the bad number against other serial numbers for that model looking for a pattern. To do that, I need the model name. The naked serial number isn't very descriptive, it makes more sense in context with it's model. Having the model name carry forward to tblSerialErrors, would take a couple of steps out of that process. If it's difficult to add that feature, don't worry about it. I'v already created a query to do it.

I've run the March data again with the offending serial number removed and it processes perfectly. No error code and all lines of data accounted for.

- Pete

[LBPete]

So for serial 021400270 from tblSerialErrors (because the month value is 40), the model can be found in the Decade Update table (800)? Or Update Input table? Or either?

Maybe the eBay tt isn't what it appears to be...

Either of those tables will have the serial number linked to a model name. The Decade Update table is created by a query that links the model name to it's decade reference. The decade reference is found in a table called Model List 1705041. That table resides in a different access database and is linked into this one.

[Micron]

I think you missed my point. I'm asking, where do I get the "model name"?

model can be found in the Decade Update table (800)? Or Update Input table? Or either?

Or are you saying it's not in this db and I can't get/have that table?

[LBPete]

So for serial 021400270 from tblSerialErrors (because the month value is 40), the model can be found in the Decade Update table (800)? Or Update Input table? Or either?

Maybe the eBay tt isn't what it appears to be...

Either of those tables will have the serial number linked to a model name. The Decade Update table is created by a query that links the model name to it's decade reference. The decade reference is found in a table called Model List 1705041. That table resides in a different access database and is linked into this one.

[LBPete]

The model name is tagged to the serial number in either the Update Input table or the Model update table. The Update Input Table is the table used by your date extraction code. When that code is run, it carries the serial number and newly decoded production date to tblTest. In that process, can the code be modified to include the model name along with the serial number in tblTest?

Update Input Table sample

Model	Serial Number	Production Date	Decade	Entry Date	Reporter	Notes
1000X	013072523		1968	3/31/2018	eBay

tblTest sample

Model	Serial Number	Production Date	Decade	Entry Date	Reporter	ID	Notes
	013072523	July, 1973	1968

[LBPete]

I'm having trouble inserting these table samples. Here's an example of what a revised tblTest would look like.

Model	Serial Number	Production Date	Decade	Entry Date	Reporter	ID	Notes
1000X	013072523	July, 1973	1968

The model name from the Update Input Table stays with the serial number when appending TblSansuiTest

[Micron]

Crap. I had error stuff on my mind and just coded to add the model to the errors table. Hang in there, will look over your latest request (again).