Data Type Conversions

[specktur]

Good Afternoon everyone.

I am having a problem, Here is the scenario:
I have data that i get from a report run off an AS/400
I import that data into access
I have a table set-up - there are some fields that contain numbers but because of the AS/400 I have to set the data type to text otherwise the field data gets lost in the conversion.
I have tried many things to convert the data but I can't seem to do it. I can run a query to change the data using the VAL() statement - but I can't get the data type to change.

Can anyone suggest how to change the data type Please?

 

[lespaul]

what type of information in the field "gets lost"? Can you provide some sample information? How do you get the data into Access? INSERT query? Are you using linked tables?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[specktur]

in the fields are numbers, but the report prints so that there are spaces in the way. Access wont import the data as a number because of the spaces.

I use the File - Get External Data.

Then I Import to an already exsisting table.

No linked tables.

Thanks

 

[lespaul]

I'm still not sure exactly what you mean. Again, some sample data would be helpful.

In the AS400 the field is named _______ and is formatted like ########### but when I open the Access table named _______ the information looks like #### ## #####.

I'm going to assume that there's a query behind the "Get External Data".

Have you thought about writing your own query to insert the data or just using a linked table and running a pass through query?

 

[specktur]

Ok sorry let my try again...
the AS/400 dumps the report to a file. the file isn't formatted. it is just spaced out. The report is designed to goto a printer, but we send it to a file instead, so it looks like a printed report in the file

Whse W/O# Description Open-Qty
11 123 sample data 25
11 123 more data 10

that is what the text file looks like.

I have a table set up that I can use the get external data command and it imports straight into the table.

Open-Qty is always a number. But between sample data and 25 are spaces, so I have to import that field as text. I would like to import as a number or change the field afterwards. I can run a query to change the data but I can't get it to save as numbers so that I can use them in mathmatical functions.

I hope that clears things up.
Thanks for all your help.
Sean

 

[fredericofonseca]

I don't know how you are transferring you data from the AS400 into the PC so please tell us as this may leed to more options.

Assuming that you are given a file with fixed size fields, which seems to be what you have as per your quote

The report is designed to goto a printer, but we send it to a file instead, so it looks like a printed report in the file

So if you in access wish to import a file like this, one way is using the "Get external data".
With Access 2000 and + , when you are doing the import you have an option of creating a import specification where you define the field type. When dealing with this field you MUST define it as numeric. All spaces within that field will be ignored by Access.

This specification can be adjusted and saved by clicking the "advanced" button when within the Import Wizard.





Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[specktur]

The report generates to a file on the AS/400, then we FTP into the AS/400 and transfer the file to a pc.

I use the fixed field size just as you suggested, but for some reason it isn't ignoring the spaces, so I end up losing data in that field for about 300 records.

What we have done in the past is import the file into excel, do the conversion in excel then save it as a .xls. we then "Get External Data" in access and it works. I would like to import straight into access and save about 20 min of work a day.

Thanks,
Sean

 

[PHV]

Why not importing into a temp table and then playing with an Append query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredericofonseca]

On the import wizard go to the advanced button, and then see what it says about that particular field on the following columns.

field name
data type
start
width
indexed
and skip.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[specktur]

I will try those when I go back to work on monday! thanks for all of the help guys!

Sean

 

[specktur]

Thanks for all of the help.
I did get it to work,
I created a spec. for importing, so now when I get external data I click on the spec and it does the rest.

Thanks again,
Sean