Changing formats within link tables

[DDONNI]

Hi I'm stuck again with Access i obviosly don't know how to use it at all.

I've setup a link between a sheet and a table and the table is updated using an append query. All this works fine, what doesn't work is the formatting.

I have 1 column in particular that needs to be formatted as a currency with the euro symbol in front of the value. I've tried changing the format in Excel and it doesn't change it, i've tried going into the query to set the format there and that doen't work and i've tried in the link table design view and it won't let me change it there.

I'm sure i'm just missing something really simple but i can't figure it out and it really bugging me now. Can any please help me?!!!

 

[sfreeman@co.mer]

in Excel, are you sure you have formatted the whole column as Accounting, then show the Euro as the prefix?

Also, in Access, in the Query, get to the field properties, and make sure it is formatting as 'Euro'.

HTH

 

[DDONNI]

Ok i've just changed the formatting again, and updated all the tables and its now working, i think the problem was because i was formating the cells as currency instead of accounting. Cheers, i knew it would be simple.

 

[DDONNI]

Ok it only worked for a couple of the tables but not for others. I think its because the ones that it works for have a blank entry in when the tables are empty and the ones that it doesn't work for have no entries in at all when blank. I don't know why this is, i've tried creating the other tables so they have a blank entry in but i can't seem to do it. Can anyone help me out on this please.

 

[DDONNI]

Ok here's the update, i've run all the append queries that append linked tables, and none of the formatting is working its all set to text now. I have no idea whats going i didn't change anything as far as i know. Can anyone help with this one please. It's so frustrating.

Is there anyway to change the formatting in the table design view as at the minute it doesn't let you change it when its linked to Excel.

Any help on this would be great.

 

[sfreeman@co.mer]

If the Excel spreadsheet is already formatted to Accounting, then you link to it, the formatting in Access will be Currency.

I have tested this, and it works. Appending amounts from another Access table that contains currency data, WILL show up in the spreadsheet as Euros.

Make sure the spreadsheet is formatted as Accounting before you link to it, and make sure that the Access link data type shows up as Currency.

When you append data in this linked table, make sure you are appending Currency data.

 

[DDONNI]

Thanx loads i what your saying makes sense i'll give it a shot i know i didn't format the spreadsheet to accounting first so thats probably where i went wrong.

Cheers again.

 

[DDONNI]

Ok, this is getting stupid i deleted the link and created a new one, making sure the formats were the same on both sides, but when the link table is created it reverts all the columns to text. I'm now wondering if this is to do with the fact i have the heading already setup in the excel table and seeing as there text it takes the first line and adopts it in Access. I must be doing something fundamentally wrong. Can anyone please help me with this?