Formatering af tal

Så vidt jeg husker styres dette af den "nationale" opsætning i styresystemet. Det er da ihvertfald et sted at starte :o)

Kontroler at feltet er et valutafelt. Start i tabellen og ændr også formatet.

mlhave: Det har jeg også tænkt på, men problemet er at rapporten findes i flere forskellige sprog, så derfor skal det ikke udskrives ens hver gang.

mugs: Feltet er allerede af typen valuta.

Måske man skal ud i noget "fusk" med en stringreplace funktion !?

Snowball

as mlhave correctly states this is controlled by the regional settings. So changing them to English will do the trick.

snowball, your comment to mlhave! If you use "fusk" then you will more than likley end up with the same format for all languages. So by using the PC's regional settings does exactly what you want. Or have I missunderstood your comment?
"...så derfor skal det IKKE udskrives ens hver gang."!!

terry: I can't change the regional settings because then many other things will be changed besides the fields on my report. A solution might be to just change the regional settings at the time of report creation, and then change it back again when Access has created the report. Is that possible in Access?

Isn't it possible to add a "dummy" field to the report, give it the value of the original field, do a "string replace" so the dummy field has the correct format, hide the original field and at last show the "dummy" field !?

Snowball

I have never had the need to change the regional settings in Access so I cant answer that. But what I dont understand is you say
"...så derfor skal det IKKE udskrives ens hver gang."

IF it is NOT to be printed the same for each language then what decides HOW th ereport i sto be printed? Or is it to be the same for each language?

It is because I can print the report in several different languages, and all countrys dont use the same character for decimals and for thousand seperator. Fx. here in Denmark we write like this: 12.345,67kr. but in England they write like this: 12,345.67kr.

Snowball

I'm lost!

How do you decide when you have to print the report in Danish or English?

I still suggest you use the regional settings. Otherwise you will need to converting the currency field to text and then convert your characters , and . to what ever you want depending on the language. This could be done in a query wich calls a function and then use the query in the report.

terry: I can now see that it's not quite true what I've been telling you guys, sorry. The database this question is based on is a database I received sortly before I made this question, and I didn't have the full overview of the complete database - but I do now.

There is only 1 report, and that report will always be printed in english.

So, I think the best solution is to do what I mentioned in my comment 27/11-2003 11:29:22 about the "string replace" function and what you (terry) mentioned in your comment 28/11-2003 17:54:46. I would like to avoid changeing the reginal settings.

Terry, can you make an query/funcion example that changes , to . and vice versa ?

Snowball

I can more than likley make a function which converts a danish formatted text to english, but IF the regional settings are alreadt english it will not work correctly!

terry: I can't understand that it in Access apparently is so difficult to do a simple string replace, show the new value in a new textbox and hide the original textbox!? That's all I want right now, and I really can't see how it cannot be done because of the regional settings!?

Snowball

lets say´the PC's regional settings are DK!
lets take a number 123.456,78 and then I make a string replace where it ends up as you want it in English 123,456.78

OK thats great, but now you have a PC where the regional settings are English, but my routine doesnt know that, so the number is already formatted 123,456.78
but the string replace routine now goes and convertes it to 123.456,78 which is NOT what you want!

So it isnt just a "simple" string replace, it has to take into account how it is already formatted!

Anyway try looking at the function REPLACE, MID and LEN can also be used for this too.

You're right - I understand what you're talking about now ;)

But anyway, I know that all the PC's where this Access application will be used, has Danish regional settings, so it won't really be a problem.

I'm aware of the function you just mentioned. My problem is that I can't figure out how to get the text from a text field on a report and copy it to a new field. I've tried, but I can't get i working because I have very little knowledge about Access as a frontend ;)

Could you please make a quick example showing how to do it?

Thanks in advance.

Snowball

Make a function in a module

Function FormatGB(CurIN As Currency) As String
Dim str As String
Dim i As Integer

    str = FormatNumber(CurIN)
   
    For i = 1 To Len(str)
   
        If Mid(str, i, 1) = "." Then
            Mid(str, i, 1) = ","
        Else
            If Mid(str, i, 1) = "," Then Mid(str, i, 1) = "."
        End If
       
    Next i
   
    FormatGB = str
   
End Function

Now in your query make a calculated field which calls this function with the currency field

EXAMPLE:

SELECT ThisIsTheCurrencyField, FormatGB([ThisIsTheCurrencyField]) AS fmtGB
FROM test;


Now you use the query in the report where you now have your currency in GB format

Thank you terry. I'll try it at work tomorrow :)

Snowball

Terry: I can't get it to work. I get the folloing error when I try to open my report:

Compile error:

Sub or Function not defined

It is the function "FormatNumber" that it cannot find !? I use Access97.

Snowball

Jeg tror, at FormatNumber er navnet på dit felt. Kontroller at det er rigtigt og stavet korrekt

str = FormatNumber(CurIN)

FormatNumber kan da næsten kun være en funktion når det er skrevet på den måde. Hvilket også bekræftes hvis man søger lidt på MSDN. Funktionen findes så åbenbart ikke i Access97 !?

Snowball

Terry's eksempel virker fint i Access2000, men ikke i Access97 hvor jeg skal bruge den ;/

Snowball

Man kan snyde den ved at lave sin egen FormatNumber funktion som nedenstående:

Function FormatNumber(Num)
  FormatNumber = Format(Num, "#.##0,00")
End Function

Problemet er så bare lige og finde det rigtige format. Den vil ikke godtage ovenstående, og hvis man bare skriver "0,00" så fjerner den tusindtals seperatoren.

Snowball

Har fået det til at virke nu. Dog ikke direkte i query'en, men ved at ligge den formaterede værdi over i et nyt tekst felt.

Function FormatNumber(Num)
  FormatNumber = Format(Num, "#,##0.00")
End Function

Function FormatGB(CurIN As Currency) As String
Dim str As String
Dim i As Integer

    str = FormatNumber(CurIN)
    For i = 1 To Len(str)
      If Mid(str, i, 1) = "." Then
        Mid(str, i, 1) = ","
      Else
        If Mid(str, i, 1) = "," Then
          Mid(str, i, 1) = "."
        End If
      End If
    Next i
    str = str + " DKK"
    FormatGB = str
End Function

Tak for hjælpen.

Snowball

Sorry, I'm not so active during working hours! Anyway glad to hear you got it working.

I have a feeling that FormatNumber is 2002 specific, didnt think of that!

Terry: It's ok :)

FormatNumber works in Access2000 or newer.

Snowball