Cannot format data from a pass through query from SQL server

[Tiger955]

Hi!

I get a result set from a pt qry from a sql server 2008 in vba.

Me.Total is an unbound field in the report foot( correct translation of german "Berichtsfuß"??)


Private Sub Berichtsfuß_Print(Cancel As Integer, PrintCount As Integer)

SELECT SUM((DATEPART(hour, Calctime) * 60 + DATEPART(minute, Calctime)) * Price / 60) AS TtlPrice ...

Set rs = CurrentDb.OpenRecordset("qryPT_TtlPrice")
Me.Total = rs("TtlPrice ")
..

On the access report I can format me.Total as currency, decimal, integer whatever I want (unless it makes no sense exept currency) and it works.

but

SUM(((DATEPART(hour, Calctime) * 60 + DATEPART(minute, Calctime)) * Price/ 60)* (case when CompanyID=1 then 1.66 else 1 end)) AS TtlPrice

The detail should be multiplied with 1,66 when the record belongs to company 1, for all others ist remains the same.

In the Management Studio the results of both looks similar, same in ´the debug window of VBA editor, but I cannot format the result or lets say, the formating is wrong for the second example.

Neither in VBA
Me.Total = format(rs("TtlPrice "), "€ 0.000,00") or
Me.Total = format(rs("TtlPrice "),"0.000,00")

nor in the report int the properties of the field I can avoid this result:

in the debug window of access the result looks OK, for example 29555.670000 (don't know where all the 0 come from), which should read as € 29.555,67.

But on the report the result with formated curreny in field property (currency, 2 decimals) shows

€ 29.555.670.000,00

Any idea?
Thanks your help.
Michael

 

[pr2-eugin]

Michael, you do realize that you have used a , (comma) after a . (dot)? Which is not how currencies work. Comma can never follow a decimal. You normally count from the dot right to left; Ones, Tens, Hundreds, Thousands, Ten Thousands etc. . Based on the regional settings, the comma or dot will be only once and that is the separator.

By the looks of your example it is considering the dot to be the separator. There might lie your problem.

 

[Tiger955]

Hi Paul!

A few minutes ago I found this grzzzzz...problem, which took me a long time!

Thank, the problem where somewhere there, BUT I have to do in access anyhow:

Me.Total= format(replace(rs("TtlPrice"),".",","), "Currency")
but no I got it.
Thanks yr help
Michael

 

[Galaxiom]

Michael, you do realize that you have used a , (comma) after a . (dot)? Which is not how currencies work. Comma can never follow a decimal. You normally count from the dot right to left; Ones, Tens, Hundreds, Thousands, Ten Thousands etc. . Based on the regional settings, the comma or dot will be only once and that is the separator.

In my understanding, at least some regions which use the comma as the decimal separator also use the dot as the thousands separator.

 

[pr2-eugin]

In my understanding, at least some regions which use the comma as the decimal separator also use the dot as the thousands separator.

That I was not aware of ! Out of the normal me thinks !

 

[Galaxiom]

That I was not aware of ! Out of the normal me thinks !

Depends how insulated your life has been.

The 67 countries where a comma "," is used to mark the radix point comprise roughly 24% of the world's population and include:
Albania, Andorra, Argentina, Armenia, Austria, Azerbaijan, Belarus, Belgium, Bolivia, Bosnia and Herzegovina, Brazil, Bulgaria, Cameroon, Canada (when using French), Chile, Colombia, Costa Rica, Croatia (comma used officially, but both forms are in use), Cuba, Cyprus, Czech Republic, Denmark, Ecuador, Estonia, Faroes, Finland, France, Germany, Georgia, Greece, Greenland, Hungary, Iceland, Indonesia, Italy, Kazakhstan, Kirgistan, Latvia, Lebanon, Lithuania, Luxembourg (uses both marks officially), Macau (in Portuguese text), Macedonia, Moldova, Mongolia, Morocco, Netherlands, Norway, Paraguay, Poland, Portugal, Romania, Russia, Serbia, Slovakia, Slovenia, South Africa (officially), Spain, Switzerland, Sweden, Tunisia, Turkey, Ukraine, Uruguay, Uzbekistan, Venezuela, Vietnam

http://en.wikipedia.org/wiki/Decimal_mark

It has an important effect on VBA. Since commas can occur in numbers the semicolon is used as the argument separator.