Concatenate Leading Zeros, and Storing 11 Digits in Numeric Field

[cwindoma]

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...

Ultimate Goal:
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:
Field1: 97
Field2: 00123
Field3: 001
Field4: 4
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?
Thanks,
Cindy

 

[raskew]

Hi -

x = 99999999999
field1 = int(x/1000000000)
? field1
99
field2 = int(x/1000000)
? field2
99999
field3 = int(x/100000000)
? field3
999
field4: Don't have a clue what you're talking about

Here's the real problem -- 'real' numbers don't have preceding zeros.
So, instead of talking about numbers, we're talking about a string.

A little more explanation would be helpful.

Bob

 

[cwindoma]

Thanks for trying to help me out Bob....I admit that's over my head though. Is that VBA? I'm not familiar with VBA...not opposed to trying it though....how would I plug this info into that Query Design screen in Access?

 

[raskew]

Hi -

I'm kind of dense. Don't have a clue what you're trying to do.

The examples I provided were straight from the immediate (debug) window. No SQL, no VBA. This worked OK from field1 thru field3, then, all of a sudden we're into preceding zeros. I'll repeat, 'real numbers' don't have preceding zeros, only strings do.

Please provide more info, real slow considering my density.

Bob

 

[cwindoma]

True that real numbers don't have leading zeros, but you can format them to display leading zeros. That's all I'm trying to do, is retain those leading zeros when I concatenate.

So using the example above, I have four fields two of which are formatted to display leading zeros (fields 2 and 3).

Field1: 97
Field2: 00123
Field3: 001
Field4: 4

When I attempt to concatenate them using a Query, the result (FinalField) looks like this: 9712314....and I need it to look like this: 97001230014.

What I'm using in that top row in Query Design to concatenate at all is:
FinalField: [Field1] & [Field2] & [Field3] & [Field4]
...that drops those zeros and gives me 9712314.

I've seen other posts where you can format those fields to retain those leading zeros (example above in my original), but I get a compile error.

Does that make more sense what I'm trying to do?

I'm doing all this in Access 2007's Query Design window....I don't see an immediate (debug) window anywhere in Access except for in the Module window.

 

[georgedwilkinson]

Try:

SELECT string(2 - len(Field1), "0") & Field1 & string(5 - len(Field2),"0") & Field2 & string(3 - len(Field3),"0") & Field3 & Field4
FROM WhateverYourTableNameIs;

This is totally air code right off the top of my head, in no way tested. Just here to demonstrate a potential solution to your problem and done strictly for the intellectual pursuit.

You may need to enclose the references to your fields in the function(s) Trim(Str()), like this:

SELECT string(2 - len(trim(str(Field1))), "0") & trim(str(Field1)) & string(5 - len(trim(str(Field2))),"0") & trim(str(Field2)) & string(3 - len(trim(str(Field3))),"0") & trim(str(Field3)) & trim(str(Field4))
FROM WhateverYourTableNameIs;

Again, air code, not tested, just for fun. And this'll break big time the moment somebody decides the smart code needs to be expanded.

I must say I'm appalled you're even doing this, but that's another story.

 

[Banana]

I'm not exactly what you are doing with numbers, but if you were going to use them like any numbers: e.g. you want to do calculate with them, I'd just store them as strings and reference an arbitrary precision library that enables me to do any calculations with as much precision as what my system has available resource.

Also as already mentioned above, string is always formatted the way you want it. Entering "0001" will give you "0001" whereas entering 0001 as a number will give you 1.

Yet another alternative is to use Double which can hold much larger number, but you should expect rounding errors. Given that your final field will be 11 digits long, making the largest number 99999999999 which when converted to binary will be 37 digits long. The fraction can be 52 bits long, so I'm certain that any rounding errors will be truncated when you store it as a string with 11-digit length.

 

[cwindoma]

George...

Thanks for the info, I kept coming up with a syntax that I need to dig a little deeper to find, so can't tell you if it worked or not yet. However, I did re-try my original on a fresh database and it worked...so something 'unseen' must have been tripping me up on that original database.

Also, I'm guessing you are appalled because this isn't a good way to design...agreed. But in this case, this database is not the 'system of record', I'm merely using it to derive and produce the value for that FinalField. That FinalField will then be loaded into the true system of record where it will be meaningful...so once it's 'produced' in Access, I don't want it to ever change...if it did, it would be out of sync with the real system of record. (Just so you know why I'm bending the rules of good design.)


Banana (that's a fun name to say)...
I was able to get that Double option to work...not really sure why rounding would be an issue since these are all whole numbers, no decimals allowed (restricted to 0 in the Decimal field).
Using a string is a good idea as well, I'll try a version that way as well.


Thanks to both of you!
-Cindy

 

[Pat Hartman]

Your original method -
=Format([Field1],"00") & Format([Field2],"00000") & Format([Field3],"000") & Format([Field4],"0")

is the correct way to do what you are trying to do. Use the correct field names and make sure that each is defined as numeric.

 

[georgedwilkinson]

Also, I'm guessing you are appalled because this isn't a good way to design...

Cindy, I guess I'm really appalled at the business for thinking it's a good idea. You'd think they would have learned something from the Y2K "bug".

Let us know if there's anything else we can do to help.