Solved

how to call a standardNumber with 2digits

Posted on 2009-04-10
11
241 Views
Last Modified: 2013-11-05
I do have following code

strSQL = "SELECT [Waehrung]&' '& [Kurs]  " & _
             "FROM dbo_GFHilfstabelleV1_Sicht255 " & _
             "WHERE dbo_GFHilfstabelleV1_Sicht255.Symbol = '" & (.Item("GF_V1_TA_Symbol_MainStock")) & "' "

            With .Item("GF_V1_TA_Kurs_MainStock")
                .Value = CurrentProject.Connection.Execute(strSQL)(0)
            End With
             
where I would like to have [Kurs] as a StandardNumber with 2digits. Haven't found any help on internet.
thx
Kongta
0
Comment
Question by:Kongta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 12

Expert Comment

by:koutny
ID: 24115178
Use the Format function:
Format(numeric_expression, "00")


strSQL = "SELECT [Waehrung]&' '& Format([Kurs],"00")  " & _
             "FROM dbo_GFHilfstabelleV1_Sicht255 " & _
             "WHERE dbo_GFHilfstabelleV1_Sicht255.Symbol = '" (.Item("GF_V1_TA_Symbol_MainStock")) & "' "

Open in new window

0
 
LVL 12

Expert Comment

by:koutny
ID: 24115205
Looking at your code in more detail now I can see that you are concatanating the fields within the SQL string. So the the Format function will only work if the dabase is MS Access.
0
 

Author Comment

by:Kongta
ID: 24115245
I do have MS SQL-Server backend and cant find any way to format the number in the code as I fusion text & numbers in my script
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Kongta
ID: 24115264
Sorry, I might have expressed not detailed enough, I meant with 2digits behind the comma if needed, but if I have a round number, it doesn't show. Same as I would choose StandardNumber with 2comma in Access
0
 
LVL 12

Expert Comment

by:koutny
ID: 24115386
Well, if it is SQL Server then you might be able to use the Replicate function. Here is an example which format the ID field of a Contacts table:

SELECT Replicate('0', 2 - Len(Cast(ContactID as varchar(10)))) + Cast(ContactID as varchar(10))
FROM Contacts

However, if you only use the value returened by your query in your code then I would say it might be easier to format the result after you have obtained the values from the database. What I mean is output the two fields seperately in the select statement and only then join them together. See the code below.



dim rst as ADODB.Recordset
 
strSQL = "SELECT [Waehrung], [Kurs]  " & _
             "FROM dbo_GFHilfstabelleV1_Sicht255 " & _
             "WHERE dbo_GFHilfstabelleV1_Sicht255.Symbol = '" & (.Item("GF_V1_TA_Symbol_MainStock")) & "' "
 
set rst = CurrentProject.Connection.Execute(strSQL)
 
            With .Item("GF_V1_TA_Kurs_MainStock")
                .Value = rst.Fields(0) & " " &  Format(rst.Fields(1)),"00") 
            End With
rst.Close
set rst = nothing
             

Open in new window

0
 
LVL 12

Expert Comment

by:Dimitris
ID: 24115435
Use the cast as decimal
You provide the precision and scale. By seting the scale to 2 all the numbers will have 2 decimals

strSQL = "SELECT [Waehrung], cast([Kurs] as Decimal(18,2)) as [Kurs] " & _
             "FROM dbo_GFHilfstabelleV1_Sicht255 " & _
             "WHERE dbo_GFHilfstabelleV1_Sicht255.Symbol = '" & (.Item("GF_V1_TA_Symbol_MainStock")) & "' "
0
 

Author Comment

by:Kongta
ID: 24115479
I see the way but it does't work as it should. Maybe there is no proper way to combine
0
 
LVL 12

Accepted Solution

by:
koutny earned 250 total points
ID: 24115485
The code snippet below should work too.

By the way, I am a bit confused here, shouldn't the SQL string for SQL Server back end use SQL Server SQL syntax? What I meant is the use of the & operator. Shouldn't it be +, like in my code below?
strSQL = "SELECT [Waehrung] + ' ' + Right('00' + Cast(Kurs as varchar(2)),2)  " & _
             "FROM dbo_GFHilfstabelleV1_Sicht255 " & _
             "WHERE dbo_GFHilfstabelleV1_Sicht255.Symbol = '" (.Item("GF_V1_TA_Symbol_MainStock")) & "' " 

Open in new window

0
 
LVL 12

Expert Comment

by:Dimitris
ID: 24115547
what u mean that it doesn't work as it sould?
It keeps the leading digits and rounds up any decimals to 2 digits.
0
 
LVL 12

Expert Comment

by:Dimitris
ID: 24115553
sorry Rounds, not rounds up 1,004 will be 1,00 and 1,004 will be 1,01
0
 

Author Closing Comment

by:Kongta
ID: 31568872
ok, got it done now, thx a lot.
I have no idea on the  &/+  issue but it works, I copy/pasted this out of an older Access.FrontEnd and it works.
Anyway, many thx and have a good time
Bye
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question