OutputTo returning odd number

[kaveman]

I'm using OutputTo to turn a report into and excel spreadsheet. Everything translates very nicely except for one of my fields. The field is called ID_Label, here is what is happening:

on the report the data returned for this field looks something like this

224 0005

when it is sent into XLS format it then looks like this

-612024

I'm at a loss here as to what is causing this...can anyone help me out?

 

[selevanm]

What is the data type for 224 0005? Also after you output the data to the Excel sheet, go to the cell that contains the -612024 and check the format of the cell to see how the number is being formatted, i.e. general, number, text, etc.

 

[kaveman]

Thanks for the reply selevanm.

The data type for the 224 0005 is the combination of two text fields that I have joined together on my report:

txtIDLabel = [stop_code]&" "&[locker_num]

When this hits the spreadsheet it is formatted as text. I tried changing this to general and number with the same result.

???

 

[selevanm]

Can you post your OutputTo code? If the format for the XLS cell that your string is going to is text and you are sending a string data type there shouldnt be any conversion happening. Do you do any sort of formatting of the XLS sheet after you output all of you data?

 

[kaveman]

Here is the code:

Private Sub Report_Activate()

DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS

End Sub

I am not doing the string formatting like you mentioned. I am doing this on the actual report where I have a field called txtIDLabel that I have set to control source to equal [stop_code]&" "&[locker_num]. Might this be the problem?

 

[selevanm]

That may be the problem. I have used OutputTo to export a table or query...basically DoCmd.OutputTo acOutputTable or DoCmd.OutputTo acOutputQuery where my data types are defined within the table or query. My suggestion would be to put a literal string within the control source, i.e. instead of [stop_code]&" "&[locker_num], put in "224 0005" and see if you get the same results. The problem may lie with how the data is Output using the acOutputReport parameter.

 

[kaveman]

I have tried this with no luck. By using an underscore in my control source ([stop_code]&"_"&[locker_num]) I am able to get my data to output to the spreadsheet as 224_0005 which accomplished what I need to happen.

For the time being I'll accept the underscore solution but I am still curious as to what the ultimate solution should be.

Thanks for your help selevanm.

 

[selevanm]

Then I think you have norrowed down where the problem lies...by placing the underscore between the fields you have a literal character there now....Access must be doing something odd when there is a space between the fields in the control source. Sorry I couldnt help more, I am glad you found a variant solution for the time being.