November 3rd, 2012, 05:50 PM
Odd issue with decimal recordsets and math functions
So here's the situation. I have a database with GPS coordinates. They are stored as nchar data (a string).
table1 (latitude, longitude)
I also have a second table with GPS coordinates stored as varchar(10) data - which is also a string.
table2 (latitude, longitude)
I'm not sure how I ended up with two different types of the same data, but I did. I call a script that calculates the distance between your home city (table1) to a destination city (table 2). It does this with a mathematical formula between the two sets of GPS coordinates.
SELECT latitude,longitude FROM table1
v1 = objrs("latitude")
v2 = objrs("longitude")
SELECT latitude, longitude FROM table 2
v3 = objrs("latitude")
v4 = objrs("longitude")
(Result: v1 = 44.23, v2= -78.44, v3 =43.33, v4= -78.44)
I could then add, subtract, multiply, etc.
Tonight I changed table2 from varchar(10) to decimal(10,8) format. All non-numeric characters were removed, all whitespaces removed.
After this change, my search results were all null. I tested the code and found that whenever I tried to perform a math equation between v1,v2 and v3,v4 it would result in a null value.
My first question is how is it that I could do math functions between NCHAR and VARCHAR strings which aren't actual numerical values but I can't do a math function between an NCHAR string and a DECIMAL value?
Now here's the weird Twilight Zone results...
The problem is in the decimal table (table2) not the nchar table.
strSQL = "SELECT * FROM table2"
v1 = (objrs("latitude"))
v2 = (objrs("longitude"))
h = v1 * 3
results in a "type mismatch" error.
I'm at a loss to understand how I can multiply an nchar value but I can't multiple a decimal value.
To solve the issue I do this:
SELECT * FROM table2"
w1 = cdbl(objrs("latitude"))
w2 = cdbl(objrs("longitude"))
h = w1 * 3
and it works!
I am very confused tonight. Why am I converting decimal records for them to work ... as decimal values, when it should be the nchar being converted into decimals, no?
November 5th, 2012, 04:16 PM
nchar is Fixed-length string when you multiplying nchar to decimal system give you error because it is not -78.44 it is " -78.44 ", so convert second table to decimal and everything should work...
if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
November 6th, 2012, 01:36 AM
yes I understand that but I'm more concerned/annoyed as to why converting the DECIMAL fields using CDBL works, when I think it should be the string being converted into a decimal that would be the fix.
It's like taking a string vs a decimal and converting the decimal to double precision, still leaving me with a string multiplied by a numeric variable. It works, I'm just more bothered as to why.