Hi,
I have recently taken over a process that has been spreadsheet based for a number of years now, and I in the process of converting that process into Access 2000, because it will remove the hundreds of spreadsheets that have to be maintained daily.
Importing the csv files into access has not been the problem, but the data within it has to be formatted to a specific requirement set out by our IT department for uploading to our backend system.
I have created a table that meets the specifications laid down and I'm in the process of converting the imported data to meet those specifications.
I have a query that will append that data to the required table, but I'm having problems working out how to convert a specific fields data, because of the way it has been submitted.
I have a field named "item_price" and the data in this field can be presented as follows:
180
343.49
348.4
1893.44
In my process I need to convert them to look like the following:
180 becomes 0018000
343.49 becomes 0034349
348.4 becomes 0034840
1893.44 becomes 0189344
I have the following expression in my Field part of my query grid for this particular field:
I'm only part way there and I'm struggling to workout how to achieve the complete convertion. The results I get from the above is as follows:
only those that have the full stop in them appear in my field as required i.e
343.49 appears as 34349 and 348.4 appears as 3484, all the rest have #Error showing.
So what I would like to achieve is for the leading zero's to fill in where required and in the case of data like 348.4 to have leading and trailing zeros.
There will never be more than 7 characters in this particular field. I think I'm in the right direction, but can't seem to work out how to get the rest of the way.
Any assistance would be most appreciated.
John
I have recently taken over a process that has been spreadsheet based for a number of years now, and I in the process of converting that process into Access 2000, because it will remove the hundreds of spreadsheets that have to be maintained daily.
Importing the csv files into access has not been the problem, but the data within it has to be formatted to a specific requirement set out by our IT department for uploading to our backend system.
I have created a table that meets the specifications laid down and I'm in the process of converting the imported data to meet those specifications.
I have a query that will append that data to the required table, but I'm having problems working out how to convert a specific fields data, because of the way it has been submitted.
I have a field named "item_price" and the data in this field can be presented as follows:
180
343.49
348.4
1893.44
In my process I need to convert them to look like the following:
180 becomes 0018000
343.49 becomes 0034349
348.4 becomes 0034840
1893.44 becomes 0189344
I have the following expression in my Field part of my query grid for this particular field:
Code:
Purchase Price: Replace(Left([item_price],InStrRev([item_price],".")-1) & "" & Right([item_price],Len([item_price])-InStrRev([item_price],"."))," "," ")
I'm only part way there and I'm struggling to workout how to achieve the complete convertion. The results I get from the above is as follows:
only those that have the full stop in them appear in my field as required i.e
343.49 appears as 34349 and 348.4 appears as 3484, all the rest have #Error showing.
So what I would like to achieve is for the leading zero's to fill in where required and in the case of data like 348.4 to have leading and trailing zeros.
There will never be more than 7 characters in this particular field. I think I'm in the right direction, but can't seem to work out how to get the rest of the way.
Any assistance would be most appreciated.
John