Access 2K bug

I don't know if any of ya'll do Access programming, but I am going to put forth my question anyway.

I have two fields in a table that get concatenated into one field. In doing this it also drops the leading zeros and adds a hyphen between the numbers, (which is what they want). So 00005321    00001 becomes 5321-1. My first problem is that when I email a report out using the sendoject/report the numbers come out weird (-3929043) or something like that. I have found several articles on the Microsoft site that explain this a being a bug, and they also give remedies.


Remedy #1. Force an input mask on Access table in order to hold formatting. I tested this and it works. Problem is the table is not an Access table and it doesn't work on the SQL table. I offered to write data to a temporary Access table to accomplish this task and... well lets just say, they said this was unacceptable (for reasons no one will ever understand)

Remedy #2. Add apostrophe to the beginning of the string to force excel to read the field as a text field. They are willing to go this route as long as I can have Access attach a macro that will clean the apostrophe out. (I am not that good, yet).This was not a Microsoft idea - it was my bosses.

Remedy#3. The Microsoft article states that if you send the query, or table rather than the report, that it will export properly. This works well except that I loose all my report and page headers, which they insist they need.

I have been able to create an excel object to write the headers and then add the data from the query. This works even better, except that if a number looks like a date, it makes it a date. So if the number started as 000009    00001 it should look like 9-1 but excel shows it as 1-Sep. I even tried formatting it when writing the data to the spreadsheet.

I have spent countless hours trying to fix this and, although I keep getting closer, I just can't figure this out.

If anyone has any ideas it would be greatly appreciated.

Thanks

[wizbones]

Man oh man,

I have no answer for you but thought it would be nice to let you know I did read it all  ;D

This is definately out of my league. I wouldn't want your job, that's for sure.

Am I encouraging or what? I kill me. Hang in there, take a break and keep at it bit by bit and it'll come to you.

Hi Propig,
Have to agree with all wizbones said. I always read your questions  but don't ever know any answers for the type work you do and problems you encounter. Sure wish I did.
Since it bugs me no end to see questions un-answered, bad as I hate to, I will refer you to another forum that will surely have an answer if you wish- just email me. I post on several forums but this one obviously don't have the technical support in this particular field.
But good luck on what ever you decide !

Hi,
This looks like an interesting problem.  How and where are you concatenating the fields?  Are you doing it in the query for the report or in a field on the report?  Have you tried creating a temporary table to run the report from with the fields already joined into a character type field?  These are just some ideas you may have already tried.

I would be interesed in hearing your solution, I'm sure you will find one.

John

Thanks for the support all. I did have an excellent site to go to for help with this stuff, but because some "software" says it is a chat room, I am not allowed to go to it anymore.

To answer John,
I have tried concatenating it in the table, the query and the report. It all comes out the same in the end.

[wizbones]

Propig,

What is the link to the site you go to for help that you no longer have access to from work?

I'd be interested in reading up on that site. Can never learn or "know" too much right?  ;D

Thanks

Resources, Resources . . . can never have enough Resources

I have lost the full link. But the main entry is www.helptalk.net. They have help for Visual Basic, Access, SQL, and more. I have found the Access forums to be very interactive. They seem to have excellent sources.