Fixed Length

[Ann]

I have seven fields in my Table that need to be concatenated to create one
field used to create a 2D Barcode. Each of the seven fields has a fixed
length and the total lenght of all the fields has to be 100 characters.
These are the fields:

JobId (8 char)
Date (8 char)
RunNumber (2 char)
RecordNumber (8 char) - the key, starting with 1 and incrementing by 1 for
each record.
PageNumber (5 char)
PageCount (5 char)
Remaining (64 char)

I've done the Date field...that was the easy one...but how do I do the other
fields? The data will be entered and each field could use 1 character or all
the characters at the time it's entered. I need to have the remaining
character spaces fill in with zeros to the left of the data entered? Thanks
for the help in advance.

 

[Ann]

Thank you for the help. I'm not sure what event this should go on though. I
tried a few of them and those that didn't give me errors didn't do anything
but update the table with the number I entered. There weren't and leading
zeros.

The only thing I did was to put the JobId after strOrig =

 

[John Spencer]

Right(String(8,"0")

 

[dymondjack]

I'm *assuming* that you don't want to change the actual data in your table to
match these formats, but rather pull the existing, normal, formats from the
record, and run a function to build that information into a string that you
can utilize with whatever barcode label you are working with.

I'm also assuming that this barcode label will be on a report so that it can
be printed? If so, you would ideally run the function from the report, as a
calculated field on the report.

If you could verify this, and give the datatypes for your fields, I should
be able to come up with a function to handle it.

*If* by chance you were going to update the actual table values to match
this desired format, I would strongly advise against it. All fields would
have to be Text fields in order for this to work accurately (no matter what
method you use), and for a number of other reasons, its generally a bad idea.
The barcode output string can be easily handled by two functions, and
reading values back from the string can be parsed easily as well, especially
due to the fixed lengths, and will require no adverse interference with the
core data in your tables.

So, let me know what the datatypes are and we'll go from there.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[John W. Vinson]

I have seven fields in my Table that need to be concatenated to create one
field used to create a 2D Barcode. Each of the seven fields has a fixed
length and the total lenght of all the fields has to be 100 characters.
These are the fields:

JobId (8 char)
Date (8 char)
RunNumber (2 char)
RecordNumber (8 char) - the key, starting with 1 and incrementing by 1 for
each record.
PageNumber (5 char)
PageCount (5 char)
Remaining (64 char)

I've done the Date field...that was the easy one...but how do I do the other
fields? The data will be entered and each field could use 1 character or all
the characters at the time it's entered. I need to have the remaining
character spaces fill in with zeros to the left of the data entered? Thanks
for the help in advance.

You can use the Format() function to recast each number into a fixed length
string field:

Format(JobID, "00000000") & Format([Date], yyyymmdd) & Format([RecordNumber],
"00000000") & Format(PageNumber, "00000") <and so on>

You can also use the String() function to generate a string to fill
"remaining" - it's not quite clear what you want there.

 

[John Spencer]

Well, that reply got chopped off, but I see that John Vinson gave you
essentially the same answer.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Albert D. Kallal]

You can use format:

eg:

format(Jobid,"00000000")

It not clear if you building this resulting string in code, or in a report,
but format should be of help either way...

 

[Ann]

Thank you all for the help. I'm sorry, I'm not a programmer so I don't know
the easiest way to do this.

The original file is being imported from Excel into an Access table (these
are addresses). All the new fields I'm trying to create except one, Record
Number, will be the same data for each record. I'm not really sure the best
way to do this but I was hoping I could use the Default Value. This way when
I import all the records into the table the fields are filled in for me with
the extra zeros to the left of the data but none of the extra zeros appear no
matter what data type I use.

Once that's done I have to do a Word merge to create the 2D barcode on the
merged letter using the 100 characters and also export it to a .txt file to
be used with the printer. The printer's .txt file will match up with the 2D
barcode on the letter making sure all that were processed were sent out.

I did try what you sent me in a query and it worked exactly as I had hoped.
I guess I could run an update query after I import the table. I'm going to
give that a try and see what I get but if anyone has a better way please let
me know.

Again, I appreciate all the help I am receiving.

 

[Ann]

Thanks everyone for the help. I'm sorry, I'm not a programmer but appreciate
that everyone will still help.

The file starts out as an Excel file. Each record is an address. All the
fields I am trying to create, except Record Number, are exactly the same for
all the records. Since I will be importing the Excel file into Access I was
hoping to use the Default Value to add these fields to each record, but no
matter what data type I use (I've tried text and numbers) the extra zeros to
the left are always dropped by Access.

Once this file is created I will do a Word merge that creates the 2D barcode
on the letter from the 100 character field I created. Then the file will be
exported as a .txt file for the printer to use to match up each letter with
the appropriate 100 character code to make sure all the letters are sent.

I did try what you gave me in a query and that worked the way I wanted it
to. I guess I can do a make table query after I do the import to get all the
extra zeros added but if anyone has a better idea I would really welcome it.

Thanks again so much I really appreciate all the help.

 

[John W. Vinson]

I did try what you sent me in a query and it worked exactly as I had hoped.
I guess I could run an update query after I import the table. I'm going to
give that a try and see what I get but if anyone has a better way please let
me know.

You do NOT need to create a new table, or even store the concatenated field
into a table field, in order to do a mail merge. Just base the mail merge *on
the Query* including the calculated concatenated field.