6A = 0.25 When exporting to Excel? 1

[BTilson]

I have a report in one of my databases that has a header of "6A" minus quotes. This looks fine when viewed in Access, but when exported to Excel, it comes out as 0.25.

Another example, 13A comes out as .541666667.

For whatever reason, it seems to be dividing the number value by 24.

Does anyone have any clue what is going on here? I have never seen such a thing. It is something to do with Excel, as I tested an export to a text file, and it worked fine there.

Any ideas?

Thanks in advance!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[SkipVought]

Hi,

There's a Date/Time conversion happening. .25 is 6:00 AM and .54166667 is 1 PM or 13:00.

What kind of data is in this column?

How is the data IMPORTED into Excel?

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[SkipVought]

I just noticed that you started off, "I have a report..."

Why export a report to Excel? Why not the source data that the report is based on? I personally, have an aversion to using reports as source data in Excel, as there are usually formatting issues that get in the way. And that's the question at hand... -- ...what is this 6A and 13A data in your report heading? Is it some TIME value that's been FORMATTED to DISPLAY like you are seeing?

Enquiring minds want to know! ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[BTilson]

The 6A and 13A (as well as a few thousand other variants) are all location codes for our main warehouse. We store millions of pounds of raw steel in our warehouse, and this report prints out locations and a current inventory of each location.

That's brilliant though, I never would've thought about a time conversion. The original data is just a text format. Some other location ID's are like ZZZ1, 1A81, etc.

I will look into exporting the data directly, that would make more sense. This was one of those quick things where the end user requested the ability to dump the report to excel at the click of a button, so I did just that. Never even crossed my mind to dump the data that the report is based on instead. In all reality, that would probably suit him a lot better, because he was asking about various annoying formatting issues, just as you were saying.

I imagine that exporting the data directly may solve the problem, but I would still be curious as to why this conversion is happening.

Thanks very much!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[SkipVought]

Well, the Time thing was a shot in the dark. It certainly makes no sense, given the stated facts.

We still do not know HOW the data is exported to Excel.

We still do not know the context of these data elements in the surrounding data, especially the data directly above these report field headings. Perhaps a copy 'n' paste?



Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[BTilson]

Here is the code behind the export button, check4 is a checkbox "Include Zero Quantity Locations":

Private Sub Command22_Click()

On Error GoTo errorHandler

If Check4.Value = False Then DoCmd.OutputTo acOutputReport, "Specific Location"
If Check4.Value = True Then DoCmd.OutputTo acOutputReport, "Specific Location Zero Included"

Exit Sub
    
errorHandler:

If Err.Number = 2501 Then GoTo skipError
MsgBox "Error in Command22_Click sub: " & Err.Number & " - " & Err.Description

skipError:

End Sub

Here are some of the invalid records from the excel version of the report:

LOCATION_ID	PART_ID	QTY	STOCK_UM	WAREHOUSE_ID	type	PRODUCT_CODE
0.541666667						
	140-14-0054	1	EA	T1	Rings/Forgings	CSM TRF
	123-51-0001	24	EA	T1	Rings/Forgings	CSM TRF
0.708333333						
	300003204	3	EA	T1	Bar	CSM TTR
0.75						
	120-51-9301	10	IN	T1	Rings/Forgings

Location .541666667 shows in Access as location 13A, .70833333 is location 17A, and location 0.75 is location 18A.

Also, I just ran a test, and exporting directly from the data, instead of from the report, seems to work fine. 18A exported to excel as 18A.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[SkipVought]

Thanks for taking the time to post these examples. Unfortunately, I cannot attribute anything to this phenomenon.

But I am glad that you were able to find a way to transfer valid data to Excel.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[BTilson]

No problem, thank you for taking the time to think about it! I'll just chalk it up to "Yet Another Bizarre Moment In DB Development".

Thank you!

Brooks Tilson
Database Development
Tilson Machine, Inc.