ps

[Cath]

I've noticed that all the columns that are text (that should be number) have no entries for the first few rows.

I can put in a dummy row with numbers/ text in to force it.

However now I have another problem. While the text columns in access seem to able to read simple numbers, eg, revision numbers can be 1, 2, 3, etc, or A, B, C. These are set to text and this works OK.

But the data which is usually in the format 5.001, 4.996, etc, has to be in number format to be able to read these. But occasionally the operator will put in OK. If I set these columns to text I can't read the numbers (which is essential) but if I set it to text it can't read the OK. Surely there must be some way round this??



Thanks again

Cath

 

[Norie]

Cath

Is this connected to your other post?

If it is I'm afraid that I don't get what you mean from either post.

Are you importing to Access?

Why are you allowing text in a numeric field?

 

[Cath]

Hi

Not sure if you're still reading this but its still not been completed resolved. I have a lot of inspection results so mostly numeric, but occasionally there may be notes in there, like 'ok' or something similar.

I've spent months doing the macros to take all this information and put it in seperate excell sheets for storage.

Now I wanted to put them into access, and it seems the only way to do this is by using linked tables.

However I'm having trouble with access. If I 'force' the fields in access to be numeric, then the text can't be read at all. So I've 'forced' them all to be text, which means at least the text and numbers can be read. (I've converted them all by column to text) in excell so they are all text).

However now I've found if I export the information from access to excell for calculations, I can't do anything with the data as it thinks its all text.

Would love to know if there is an easy answer to all this but I'm thinking maybe there isn't.

All in all I'm finding access very limiting!!

Cath

 

[pritcham]

Hi Cath

Not sure how you're importing/exporting to and from Access if your trying to do calculations on the exported data in Excel, and the data is exported as text, could you not use Excel's Val() function to convert numerical data stored as strings back into 'real' numerical data? You may need to add some sort of validation in excel so it's not trying to convert "OK" into a number etc but it may get you part of the way there.

The other alternative you have might be to do the conversion during the import/export (i.e. convert to text when importing to Access, and convert to numbers when exporting back to Excel) but to be honest that's not really dealing with your problem of your data being of an inconsistent type. Perhaps you should look at introducing some form of validation so that you can either only store text, or only store numbers in your source data.

As for Access being limiting - it's actually incredibly powerful, but it does need to know the type of data you're storing and from the sounds of it, you're trying to store two potentially different data types in the same field and to be honest, I don't know of any DB system that will allow you to do that.

Anyway, hope that helps
Martin

 

[Cath]

Thanks for that. I'm using linked tables to import to access. Which to be honest, is working OK now. However I know various people in the company who like to take the info from access and export to excel for calculations. I will try your suggestions though,

Cath