How to sum text numbers in SQL?

[Rudi]

Hi,

I have a colleague who needs some advice on an SQL statement.
Any assistance is appreciated. TX

The question:
I need to total 2 text fields and deduct 2 test fields in a sting.
The deduction works perfectly but the Sum is “Concatenating” like Excel's function because the field is text.
Do you perhaps know what can be done to convert to numbers before I add them together.

Code: Select all

If daoRst.fields(i).Value = "Paid amount" Then
    strInsertSQL1 = strInsertSQL1 & ", (" & daoRst.fields(i).Name & " + " & daoRst.fields(i + 4).Name & ")"
ElseIf daoRst.fields(i).Value = "Outstanding amount" Then
        strInsertSQL1 = strInsertSQL1 & ", (" & daoRst.fields(i).Name & " - " & daoRst.fields(i + 2).Name & ")"
Else...

[BenCasey]

Hi,
The deduction works perfectly but the Sum is “Concatenating” like Excel's function because the field is text.

It is possible that it is a null field, so try wrapping them in the NZ() function.
Something like:

strInsertSQL1 = strInsertSQL1 & ", (" & nz(daoRst.fields(i).Name,0) & " + " & nz(daoRst.fields(i + 4).Name,0) & ")"

[Rudi]

If I read the statement correctly, it seems to be collecting the name of a field? If that IS the case, I doubt it will be null as a table requires a field name.
I cannot be sure though and I cannot test it but I will relay the information back to the user who is querying about it.
TX for the input. Cheers

[HansV]

The code inserts the names of fields into a SQL string. When this SQL string is executed, the values of those fields will be evalutated. Another option:

Code: Select all

If daoRst.fields(i).Value = "Paid amount" Then
    strInsertSQL1 = strInsertSQL1 & ", (Val(" & daoRst.fields(i).Name & ") + Val(" & daoRst.fields(i + 4).Name & "))"
ElseIf daoRst.fields(i).Value = "Outstanding amount" Then
        strInsertSQL1 = strInsertSQL1 & ", (Val(" & daoRst.fields(i).Name & ") - Val(" & daoRst.fields(i + 2).Name & "))"
Else...

One wonders why numeric values are stored in text fields though...

[BenCasey]

The code inserts the names of fields into a SQL string. When this SQL string is executed, the values of those fields will be evalutated. Another option:

Code: Select all

If daoRst.fields(i).Value = "Paid amount" Then
    strInsertSQL1 = strInsertSQL1 & ", (Val(" & daoRst.fields(i).Name & ") + Val(" & daoRst.fields(i + 4).Name & "))"
ElseIf daoRst.fields(i).Value = "Outstanding amount" Then
        strInsertSQL1 = strInsertSQL1 & ", (Val(" & daoRst.fields(i).Name & ") - Val(" & daoRst.fields(i + 2).Name & "))"
Else...

One wonders why numeric values are stored in text fields though...

1. I don't think val() will do the trick if the field is null.
2. Sometimes data is imported and comes through as a delimited string, etc.

[HansV]

If the values are text, in particular imported text, empty values are probably zero-length strings "", and Val("") = 0.

But Rudi's colleague will have to test and decide which option works best.

[BenCasey]

If the values are text, in particular imported text, empty values are probably zero-length strings "", and Val("") = 0.

But Rudi's colleague will have to test and decide which option works best.

Yes, we are just guessing here on scant knowledge of the underlying data stream.

[Rudi]

My colleague has replied with the good news that the VAL(...) function has done the job and the statement is working "beautifully".
TX again guys for your valuable input.

[HansV]

Thanks for the feedback, good to hear that it worked.