Formating Data in a Query

JohnLee

Registered User.
Local time
Today, 07:51
Joined
Mar 8, 2007
Messages
692
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:

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
 
Are you absolutely sure about what is required? This is the first time in my entire career that I hear of a requirement for a specific FORMAT af data for storage, especially since you also convert it from REAL/DOUBLE to STRING in the process. But there is a first time for everything, I guess.

AS to the decimals? Multiply each number by 100, and use the FORMAT function to get rid of decimals, and to display leading 0. If the number is dsiplayed as string in your input, convert it to a number, multiply, and then convert to your format.
 
Look at "DemoFormatingDataA2000.mdb" (attachment, zip).
Look "Table1", "Query1" (column x1).
Run Query1.
 

Attachments

Hi,

Thanks for your response, I'll let you know how I get on.

John
 
FORMAT(Csng(MyString)*100,"0000000") should do the trick
 
Hi Splkepl,

Thanks for that, it worked a treat.

And

Thank you MStef for your assistance as well, most helpful indeed.

Most appreciated.

John
 

Users who are viewing this thread

Back
Top Bottom