Hi,
I am dowloading data from an AS/400. Data is loaded in a table formatted this way :
Dim tabResult (27) As String
Then I get my resulset from the AS/400 and add rows :
tabResult(0) = Cstr(result.GetValue(“COBILL”))
tabResult(1) = Cstr(result.GetValue("COCMPY"))
tabResult(1) = Cstr(result.GetValue(“COCMPY”))
…
Table is kept in a variable:
Dim aCUSTCOMP As Variant
Now If I want to retrieve the value of my 6th column, I just have to do :
Dim s30jours as string
s30jours = aCUSTCOMP(6)
in the log, I get this result (french system, that’s why there is a comma and not a dot as separator) :
s30jours = 321532,18
To me 321532,18 looks like a number, but I cannot do any conversion at all. It always returned me a Type Mismatch
I even tried to replace the comma by a dot, same result, the value is not recognized as a number. My problem is that I have to do calculation with some columns…
Isnumeric function always returned false…
I don’t know what else I can do…
Somebody has an idea?
Thanks,
Genevieve
Subject: problem with number format - decimal
The retrieved column value is probably returned as a Double, which id why you get a Type Mismatch when you try to assign it to the s30jours string.
Either declare d30jours as Double
Dim s30jours as Double
s30jours = aCUSTCOMP(6)
Or, if you need the decimal number as string, convert it to string:
Dim s30jours as string
s30jours = cStr(aCUSTCOMP(6))
If you want to display the data type of the column, try:
Messagebox "aCUSTCOMP(6) data type = " & datatype(aCUSTCOMP(6))
By the way, the functions cDbl() and cStr() will respect the NotesInternational settings with respect to Decimal Separator.
Subject: RE: problem with number format - decimal
The data type is a string, when I load the table I convert everything to this type.Ex.: tabResult(6) = Cstr(result.GetValue(“COAR5”))
I don’t understand why I cannot use the value and convert to double.
My result for column 6 is 321532,18 and cdbl(aCUSTCOMP(6)) returned a Type Mismatch…
Any other ideas???
Subject: RE: problem with number format - decimal
I am a little confused.
You are assigning a value to tabResult(6) and getting a value from aCUSTCOMP(6).
What did
Messagebox "aCUSTCOMP(6) data type = " & datatype(aCUSTCOMP(6))
report?
And where in you code was the…
cdbl(aCUSTCOMP(6))
Your original posting did not contain that code.
Try to include the code that is actually run, and indicate which line generates the Type Mismatch error.
Subject: RE: problem with number format - decimal
sorry, I will try to be clearer.
messagebox for data type returned 8 (string)
It is a very complex script, so I don’t want to post everything, but here is how it’s working :
Dim aCUSTCOMP As Variant
aCUSTMAST = F_CUSTMAST (“NNFILES”, sBillTo, “”)
part of code into F_CUSTMAST:
Function F_CUSTCOMP (sLib As String, sCompanyCode As String, sBillToNumber As String ) As Variant
Dim tabResult (27) As String
result.NextRow
tabResult(0) = Cstr(result.GetValue("COBILL"))
tabResult(1) = Cstr(result.GetValue("COCMPY"))
tabResult(2) = Cstr(result.GetValue("COAR1"))
tabResult(3) = Cstr(result.GetValue("COAR2"))
tabResult(4) = Cstr(result.GetValue("COAR3"))
…
Loop Until result.IsEndOfData
F_CUSTCOMP = tabResult
End Function
Back to my original script :
d30jours = Cdbl(aCUSTCOMP(6)) + Cdbl(aCUSTCOMP(7))
But I did some test with only :
Cdbl(aCUSTCOMP(6))
so that’s why I know my error message occured everytime I try to convert a value from the table to something numeric.
should be simple… can’t figured this out…
Subject: RE: problem with number format - decimal
So to recap.
You are saying that this code would fail:
Dim s as string
dim d as Double
s = “321532,18”
d = cdbl(s) ’ Type Mismatch here?
Works fine for me…
Could the aCUSTCOMP(6) contain some illegal characters?
Try
Messagebox “[” & aCUSTCOMP(6) & “]”
to see if you have some leading or trailing spaces.
Also, try:
Dim ses as new NotesSession()
msgbox "Decimal separator: " & ses.International.DecimalSep
To verify that you have set up your system to use decimal comma.
Subject: RE: problem with number format - decimal
I tried your simple code and i get the type mismatch error message…
I already checked fo illegal characters and there was any.
Here is the result of Msgbox "Decimal separator: " & ses.International.DecimalSep :
Decimal separator: ,
The agent is running on the web, could it be a setting on the domino server?
Thanks a lot Morten for your help, I really appreciated.
Genevieve
Subject: RE: problem with number format - decimal
If the code I supplied gives you a Type Mismatch error it is because the system it runs on is not using decimal comma. Did you run it on the server, or on the client?
Try running the Messagebox displaying the NotesInternational.DecimalSep in the code that executes on the server. It will be displayed in the server log.nsf, Miscellaneous events view.
The server is likely set up with US standard decimal separator (.), hence the problem.
Mind you, changing that setting on the server could perceivably cause problems in other code that expects current settings.
Subject: RE: problem with number format - decimal
I ran the script directly on the server. The response I gave you in my previous post was from the log.nsf on the server.
20/07/2004 13:14:43 Agent message: Decimal separator: ,
seems the server is using comma for decimal…
just does not make sense at all…
any other ideas?
thanks again for your help
Subject: RE: problem with number format - decimal
where can I see the setting on the server for decimal? I can’t find it…
thanks!
Subject: I may have a solution.
It’s an OS setting that Domino reflects. Don’t know how that is set on the iSeries.
Come to think of it… I seem to remember a Linux system exhibiting the exact same behaviour. Can’t recall if it was a bug in Domino or an OS setting that fixed it. But here are some postings that will possibly give you an answer http://www-10.lotus.com/ldd/46dom.nsf/Search?SearchView&Query=linux%20and%20decimal&SearchOrder=0&Start=1&Count=100
http://www-10.lotus.com/ldd/46dom.nsf/Search?SearchView&Query=iseries%20and%20decimal&SearchOrder=0&Start=1&Count=100
Try the following (on the server):
Dim s as string
dim d as Double
s = “321532,18”
d = myCdbl(s) ’ check this value, converting using custom cdbl()
function myCdbl(byval s as string) as Double
dim decpos as integer
decPos = instr(s, “,”)
if decPos > 0 then
s = left(s, decPos - 1) & “.” & mid(s, decPos + 1) ’ change to decimal point
end if
myCdbl = Val(s) ’ val() always uses decimal point (.). Alternatively, try cDbl()
end Function
Subject: RE: I may have a solution.
I tried your code but still not working, I get an “Illegal function call”. I looked at the postings for Linux, there are having same kind of problem, but unfortunately, no solution.
I already tried this morning to replace the comma with a dot, my function (replacesubstring) was running ok, but I could not convert to double my new value. - same error message - type mismatch
finally it’s not an easy one… probably a setting somewhere, I just have to find where…
thanks one more time for your help! I will post my solution when I will find one…
Subject: Try this cDbl() substitute that doesn’t use decimal points
Try this cDbl() substitute. It does not use decimal separators at all, but removes them from the string, and adjusts the result subsequently by a simple multiplication.
Let’s know if it works, and report the problem to IBM. It’s about time it gets fixed and/or properly documented.
Function myCdbl(Byval s As String) As Double
' cDbl() replacement for Linux/iSeries domino code, where cDbl() and Val() does not accept decimal separator (comma or point)
Dim decpos As Integer
Dim factor As Double
s = trim(s) ' removes any leading or trailing spaces that would cause fraction to be calculated wrongly
decPos = Instr(s, ",")
If decPos = 0 Then
decPos = Instr(s, ".")
End If
If decPos > 0 Then
factor = 0.1 ^ (Len(s) - decPos)
s = Left(s, decPos - 1) & Mid(s, decPos + 1) ' remove decimal separator
Else
factor = 1
End If
myCdbl = Cdbl(s) * factor
End Function
Subject: RE: Try this cDbl() substitute that doesn’t use decimal points
Thanks a lot Morten,
This new function fixed my problem!
I’m so happy!
Thanks!!!
Subject: Where there is a wíll, there is a way 