How to merge data fields into one bar code with horizontal tabs between each field in Crystal Reports?

We need to be able to write a formula in crystal which combines a job number, assembly number and material number into one bar code.  It must have horizontal tab characters in the bar code between each field of data, this is so that upon scanning, the reader can input the first data into the field on the ERP screen, automatically tab to the next and input the next data etc...

We are using Crystal Reports XI R2 with a Progress DB running the Epicor Vantage ERP Product.

Is this even possible to prevent spending too much time on it? See below for current work in progress:

Formula field in Crystal -

{JobHead.JobNum} + ChrW (9) +
totext({JobMtl.AssemblySeq}) + ChrW (9) +
totext({JobMtl.MtlSeq}) + ChrW (9)

This outputs: '10068 0.00 10.00' when it needs to be '10068 0 10'

I have added the 'totext' function as the syntax requires a string on the second and third DB fields.

Thanks for reading.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The Cstr() function will allow you to output the number as a string, without decimals.

I hope this helps.
I would recommend Code-128 instead of Code-39. Code-128 encodes tabs better. There is an example at the end of this tutorial using multiple fields with Code-128.
THELFBAuthor Commented:
Thanks for the speedy replies. I will try both in the morning and get back to you.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Crystal ToText defaults to 2 decimal places and , as the thousand deparator.

Try changing your formula to

{JobHead.JobNum} + ChrW (9) +
totext({JobMtl.AssemblySeq},0,'') + ChrW (9) +
totext({JobMtl.MtlSeq},0,'') + ChrW (9)


{JobHead.JobNum} + ChrW (9) +
cStr({JobMtl.AssemblySeq},0) + ChrW (9) +
cStr({JobMtl.MtlSeq},0) + ChrW (9)
THELFBAuthor Commented:
I have tried mimcc suggested solution but I am getting an error message (see screen shot)
also tried oliff suggestion but get a different error (see second screen shot)

try the below;
{JobHead.JobNum} + ChrW (9) + 
cStr(tonumber({JobMtl.AssemblySeq}),0,'') + ChrW (9) + 
cStr(tonumber({JobMtl.MtlSeq}),0,'') + ChrW (9)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
or this to build a stringvar and then test parts at a time, read hashing!
local stringvar output;

// if JobHead.JobNum is a number value, then unhash this and hash the
// stringvar below.

// local stringvar JobNum:=cStr({JobHead.JobNum},0,'');
local stringvar JobNum:={JobHead.JobNum});

local stringvar AssemblySeq:=cStr({JobMtl.AssemblySeq},0,'');
local stringvar MtlSeq:=cStr({JobMtl.MtlSeq},0,'');

output:=JobNum+ChrW (9)+AssemblySeq+ChrW (9)+MtlSeq;

// JobNum; // UNHASH LINE TO TEST JobNum;
// AssemblySeq; // UNHASH LINE TO TEST AssemblySeq;
// MtlSeq; // UNHASH LINE TO TEST MtlSeq;


Open in new window

THELFBAuthor Commented:
Thanks Oliff, this is working, however see the screen shot of the output.  The Barcode won't scan.  I am assuming where the gaps are, it is supposed to have bars representing what the reader can interpret as a horizontal tab?  It is the barcode on the right.

I think rather than ChrW (9) you'd need to use another character that the reader recognises as a break? Perhaps use +" "+ rather than +ChrW(9)+

(i dont know what ChrW(9) is!)
THELFBAuthor Commented:
Thanks for the info oliff, the problem now is another question altogether so I'm going to close this one off.
THELFBAuthor Commented:
Answered my question promptly with extra assistance.  Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.