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