Hello All, I have seen some posts regarding this topic, and so far it's not working for me. Hoping someone can help me.
Here's the roadblock(s) I'm running into...
I need to take an 11 digit numeric code, calculate a MOD10 check digit on it, and store it in the database. (Parts of this
code need to increment as well which is why I have this as numeric instead of text.)
I have broken the number down into it's meaningful parts on a table which I am using to calculate, then using an append query
it to add the finished code to a 'master' table for storage.
Here's the breakdown:
FinalField: 11 Digit value up to 99999999999
Field1: 2 digit field, valid values are 97 or 99
Field2: 5 digit field, valid values from 00000-99999
Field3: 3 digit field, valid values from 000-999
Field4: 1 digit field, this is the MOD10 check digit calculated using Fields 2 and 3 only.
So far, I have two issues I can't get around:
Problem #1 Field Size:
These are all Long Integer fields, including FinalField. When I attempt to concatenate the 4 fields that make up FinalField,
I get an error since Long Integer's max size is a value of 2147483647...lower than the 99999999999 I need. Attempts to make
this into another type of field result in it flipping scientific and/or zeroing out the last several digits.
Surely there has to be a way to store an 11 digit numeric in the database...what's the secret?
Problem #2 Leading Zeros:
I have seen the recommendations on formatting, for example:
Concatenate to FinalField: 97001230014
=Format([Field1],"00") & Format([Field2],"00000") & Format([Field3],"000") & Format([Field4],"0")
When I try this, I'm getting a Compile Error.
Specifically, I'm doing this in Access 2007, Query Design screen, and I'm placing my cursor in the "Field" field (top row).
When I type the above expression in there, it defaults to:
Expr1: Format([Field1],"00") & Format([Field2],"00000") & Format([Field3],"000") & Format([Field4],"0")
...and...I get that pesky Compile Error when I try to run it.
(Should also mention that this is an Append Query, it's supposed to append the concatenated value to a field (Long Integer)
on a table. I have tried pulling out Field2 so that the concatenated value is only 6 digits (small enough to fit in the
destination field), but I still get that Compile error.)
Any suggestions for either of these problems?