Changing values

[ChristopherL]

Hi there!
I dont really know where to post this, but im thinkin this could be fixed in a report, if not, I am sorry for posting in the wrong section

but I got this column representing the maturity

sens_test_nameEUR, maturity 0.002739726EUR, maturity 0.083333333EUR, maturity 0.019230769EUR, maturity 1EUR, maturity 2EUR, maturity 0.25EUR, maturity 3EUR, maturity 4EUR, maturity 5EUR, maturity 0.5EUR, maturity 7EUR, maturity 0.75EUR, maturity 10EUR, maturity 15EUR, maturity 30EUR, maturity 40EUR, maturity 50SEK, maturity 0.002739726SEK, maturity 0.083333333SEK, maturity 0.019230769SEK, maturity 1SEK, maturity 2SEK, maturity 0.25SEK, maturity 3SEK, maturity 4SEK, maturity 5SEK, maturity 0.5SEK, maturity 7SEK, maturity 0.75SEK, maturity 10SEK, maturity 15SEK, maturity 30

In this column the numbers after maturity means the following

'0.002739726':'1d',
'0.019230769':'1w',
'0.083333333':'1m',
'0.25':'3m',
'0.5':'6m',
'0.75':'9m',
'1':'1y',
'2':'2y',
'3':'3y',
'4':'4y',
'5':'5y',
'7':'7y',
'10':'10y',
'15':'15y',
'30':'30y'}

How can I fix this? so that the values represent the proper day/week/year?

Sorry for long post! :banghead:

 

[ChristopherL]

http://img835.imageshack.us/img835/1618/nre2.jpg

image of column

 

[ChristopherL]

or maybe I can fix it in a query?

 

[Mihail]

but I got this column representing the maturity

What you mean with this ? From where you got this ? And, most important, how you wish to become this ?
Give us the necessary information if you wish some help, because I like what you "got" as is. I think that no changes are needed.

 

[ChristopherL]

What you mean with this ? From where you got this ? And, most important, how you wish to become this ?
Give us the necessary information if you wish some help, because I like what you "got" as is. I think that no changes are needed.

I have in a column named maturity which is linked in a reply after!
The fields in them are named "maturity 0.002739726" for example.

I was wondering about the code or something to change this field "maturity 0.002739726" to just "1d"?

 

[Mihail]

I.m afraid that still I don't understand.
The entire text can be copied in MS Word and, using Find and Replace tool you can transform all that numbers:

Find What ? maturity 0.002739726EUR
Replace With: 1d

First time you do this can record a macro then, next time you can run this macro.

 

[Cronk]

What is the base date?

If 0.25 represents 3 months, then if the base date was 1 January 2013, I take it you want to display it as 1 April 2013

 

[ChristopherL]

I.m afraid that still I don't understand.
The entire text can be copied in MS Word and, using Find and Replace tool you can transform all that numbers:

Find What ? maturity 0.002739726EUR
Replace With: 1d

First time you do this can record a macro then, next time you can run this macro.

Okay that can be done aswell I guess.. However I'll try to explain this again.

I have a column named sens_test_name
In column the value in the fields are named the following:

EUR, maturity 0.002739726
EUR, maturity 0.083333333
EUR, maturity 0.019230769
EUR, maturity 1
EUR, maturity 2

What I want to do is change these values so that:

EUR, maturity 0.002739726 = 1 day
EUR, maturity 0.083333333 = 1 month
EUR, maturity 0.019230769 = 1 week
EUR, maturity 1 = 1 year
EUR, maturity 2 = 2 year

I know that this can be done with find and replace tool in word, but I want to do it in access so that I can print it out on a report!

So I want to change the values in fields.

 

[Mihail]

This is another explanation, isn't it ?
This can be done very ease in a query by using as parameter an external function like this:

Public Function ReturnPeriod (Period As String) As String
  Select Case Period
    Case "EUR, maturity 0.002739726"
      ReturnPeriod = "1 day"
    Case "EUR, maturity 0.083333333"
      ReturnPeriod = "1 month"
    Case "EUR, maturity 0.019230769"
      ReturnPeriod = "1 week"
    Case "EUR, maturity 1"
      ReturnPeriod = "1 year"
    Case "EUR, maturity 2"
      ReturnPeriod = "2 year"
  End Select
End Function

 

[ChristopherL]

This is another explanation, isn't it ?
This can be done very ease in a query by using as parameter an external function like this:

Public Function ReturnPeriod (Period As String) As String
  Select Case Period
    Case "EUR, maturity 0.002739726"
      ReturnPeriod = "1 day"
    Case "EUR, maturity 0.083333333"
      ReturnPeriod = "1 month"
    Case "EUR, maturity 0.019230769"
      ReturnPeriod = "1 week"
    Case "EUR, maturity 1"
      ReturnPeriod = "1 year"
    Case "EUR, maturity 2"
      ReturnPeriod = "2 year"
  End Select
End Function

Woah thanks! This definetly seems like it would do the trick! So do I only put this in the parameter field or where do I put it?

 

[Mihail]

No

First, you should create a module:
Open the VBA editor, close all other child windows then insert a new module.
Copy the code I give you in that module.
CTRL+S (save)
Close the VBA editor.

Then start to create a query.
Add your table and from this table necessary fields.
Of course you must add the incriminated field. I'll name it "Maturity"
After all this is done, in a new field write this:
ConvertedMaturity: ReturnPeriod([Maturity])
(of course that you must replace the word "Maturity" to your field name)
CTRL+S (save)

Run the query.