Query Ouput Gibberish

[firestorm998]

This sounds like an age old issue on here that can be fixed in part but haven't yet found a full solution:

I have a DB based on a linked excel table which updates daily. Having got half way through the development of a DB I now find that the format of numbers in the Excel spreadheet is giving me gibberish in queries( ie 20000000 or 20,000,000.000000 or 200E+407 when i should see 20,000,000.) All the usual 0.00 ideas or changing the format of the column in the query do not resolve it as I am not presented with the ususal Number format options that include the thousand separator. I've even tried using a form to view the calculation results and changing the property of the fields there but to no avail.

To try & resolve:

I have gone back to scratch and reformatted the columns in Excel to numbers.

When I create the linked table, fields which I need for calculations show as text in Access even after formatting in Excel as Numbers.

So I've tried using the create table query to transfer the linked table data to a new table where I can set my own properties. This works to a point. The data in the linked table changes daily, some additions some deletions so I need to append daily to a table I can reformat the fied properties on. But obviously once I have an exiting table I have used to correct field types I get type mismatch errors when trying to append the latest linked table data.


I'm really stumped as to how to get a field number format to stick!Getting the field format right at its origin seems to be the only way to get the full results I want in queries down the line.

Is there anything about the column names in the Excel sheet that may be confusing Access? Some have brackets like:'[OTC] CDS Premium (bp)', or "." & underscores like:'pm.PV01_CREDIT'?


many thanks in advance.

 

[Banana]

Re: Query OUtput Gibberish!

1) Please do not cross post in diffferent posts

2) The naming may be a concern. Normally you want name with no spaces or at least underscore. Brackets are used for naming that has spaces, but it's just better to not bother with it at all. (BTW, '[OTC] CDS Premium (bp)' can't be right- it should be [OTC CDS Premimum (bp)], and I wouldn't be surprised if () and other weird characters made it worse)

3) What exactly are those numbers? Were you expecting integers or decimals? You said you formatted in Excel to numbers... but which kind of numbers? It's best to be explicit as possible so Access will import them correctly, or if this is not feasible, import manually (e.g. reading the cell values using any conversion functions (e.g. CInt(), CLng(), CDbl(), CStr()...)

HTH.

 

[Brianwarnock]

I presume that Access has been told that row 1 contains headings, and is not therefore data.

Brian

 

[firestorm998]

Re: Query OUtput Gibberish!

Thanks for the response. Wasn't too sure where the post lay so hedged my bets in both forums.

When i format in excel I don't actually get the option of a type of number(integer,single,double etc) as in Access, just decimals,seprator etc.

Within the DB i need to use the fields to calculate and output standard format numbers, some with decimals some without.

Cheers

 

[firestorm998]

Indeed it has.