Access 2007 - Concatenating Leading Zeros, and Storing an 11-Digit Num

[cwindoma]

Hello Access Gurus, I have seen posts regarding this topic, and so far it's not working for me. Hoping someone out there 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.)

Do you have any suggestions for either of these problems?

Thanks,
Cindy

 

[fneily]

Change FinalField to Double.
I tested your concatenation and appended it to FinalField as a Double. Worked.

In QBE, in the field row, left of the : is the column heading (Exper1), right is the expression. You do not type an =.

However, even though you can do the above operation, you do not save calculated fields in Access. It violates normalization rules. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Since the FinalField can be created on the fly in a query, form, report, it is not needed to store it. Depends if you want good or bad database design.

 

[JBinQLD]

I would be tempted to store the value as text and write 4 simple functions, IncrementPart1, IncrementPart2, etc.

I assume these parts have relevance to different people, perhaps part 1 is a supplier code, part 2 is a date sent, whatever, you could also write functions to simply retrieve these particular parts which could then be queried for meaningful data

 

[cwindoma]

Thank you...on the Double, I tried that before and it didn't work...does now...can't explain why, but thanks.

On the formatting though...I am still getting a compile error when I try to run it.

This is the way it's written:
Expr1: Format([Field1],"00") & Format([Field2],"00000") & Format([Field3],"000") & Format([Field4],"0")

This is the error:
Compile error.in query expression 'Format([Field1],"00") & Format([Field2],"00000") & Format([Field3],"000") & Format([Field4],"0"'.

I did notice when the error pops up, that the last ) is missing, but when you look at it in Zoom, Expression Builder, or SQL it's there.

Did you get that error at all?

 

[cwindoma]

JBinQLD....

That's actually pretty close to what I'm trying to do...for example, ultimately, I need to get Field2 to increment by 10, and Field3 to increment by 1....concatenate just those two fields, then calculate a MOD10 to get the value of Field4.

Didn't think I could pull that off with a text field. Is there a way to increment values in a text field?

 

[JBinQLD]

Too easy mate

Write your function something like this: (pseudo code)

Function IncrementPart1(strOrigCode as string, intToAdd as integer) as string

dim strBitImInterestedIn as string
dim intBitImInterestedIn as integer

'Get the string we love
strBitImInterestedIn=mid(strOrigCode,4,5)

intBitImInterestedIn=cint(strDitImInterestedIn)
intBitImInterestIn=intBitImInterestedIn+intToAdd

IncrementPart1=cstr(format(intBitImInterestedIn,"0000"))

end funtion

Naturally you could have one function and pass it the start and length positions for the mid statement and do something funky like:

dim intFormatLength as integer
dim strFormatTo as string
dim intSillyLooper as integer

intFormatLength=len(strBitImInterestedIn)
strFormatTo="0"

for intSillyLooper=1 to intFormatLength
strFormatTo=strFormatTo & "0"
next intSillyLooper

and then adjust your format statement to format to strFormatTo....

As always, there are many ways to skin a cat, depends how flash you want your funky functions to be

 

[fneily]

In the QBE, I have

Expr1: Format([Part1],"00") & Format([Part2],"00000") & Format([Part3],"000") & Format([Part4],"0")

same as yours. No Errors. Switch to SQL view and check the SQL. It should look something like

SELECT Format([Part1],"00") & Format([Part2],"00000") & Format([Part3],"000") & Format([Part4],"0") AS Expr1
FROM Number_concatenation;

Make sure you have no typos such as extra spaces.

 

[fneily]

Also try a simple Format. If that doesn't work, then you're probably missing a library reference (in VBA, Tools - References)

 

[cwindoma]

Thank you both so much for your help!!

fneily...
I followed your troubleshooting suggestion, and as it turns out after I started fresh with a brand new database, it worked like a charm...even though the expression and library references appeard to be the same between the databases. Anyway, thanks for the help.

JBinQLD...
Thanks for the incrementing advice...haven't gotten far enough to try it out yet, but will let you know how it turns out.

For my own education, I'm trying this both ways, fields as numeric, and fields as text to see which works best for what I'm doing. Will post results so you both can see them if you care to.

Also...just to clarify why I'm so set on that 11-byte field being stored rather than relying solely on the other fields to calculate it. 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.)

Thanks again!
-Cindy