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?
 
koutnyCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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.