how to call a standardNumber with 2digits

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
KongtaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
koutnyConnect With a Mentor Commented:
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
 
koutnyCommented:
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
 
koutnyCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
KongtaAuthor Commented:
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
 
KongtaAuthor Commented:
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
 
koutnyCommented:
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
 
DimitrisSenior Solution ArchitectCommented:
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
 
KongtaAuthor Commented:
I see the way but it does't work as it should. Maybe there is no proper way to combine
0
 
DimitrisSenior Solution ArchitectCommented:
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
 
DimitrisSenior Solution ArchitectCommented:
sorry Rounds, not rounds up 1,004 will be 1,00 and 1,004 will be 1,01
0
 
KongtaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.