Problem with spreadsheet data importation and formatting with queries

[RuthDavis]

I have a problem with data importation and formatting.

I have a linked Excell Spreadsheet the contents of which I transfer to a holding table using a select Query. From this table the data is split up and appeneded into its final destinations, again, using queries.

My problem is that the data formats of the various items in the spreadsheet are different to the table that the imported data will ultimately end up in. For example, in the spreadsheet, numbers are stored as text but ultimately I need to store this in a Number Format.

So, Is there a way of formating the data as I import it. For example, the query changes a text field to a number using maybe an expression or a global modual/function.

I am honest to say that If this is possible, I need to ask you guys for the correct expression or code as my knowledge of Access is, at this stage, just a basic one.

Any help or suggestions would be great.

Ruth Davis. [sig][/sig]

 

[Robertd]

Hi Ruth,

try adding this to the query fields that append the data from these text fields

= Val(SourceField)

the val function converts the text string containing numerals and the period it will stop converting the string if it incounters anything else. but it may do for what you need.

other aspects are a little dificult with out knowing a bit more about the situation,... ie you might have to have a custom function to convert the source data just the way you want it ;-)

HTH
Robert
[sig][/sig]