Sort or Max on CLng 2

[NMiller007]

I have a string type field that contains text, numbers and NULLs.

I am setting all the Null or strings (like *[a-z]*) to zero and CLnging the others. Then I need to get the max of the new values (or just sort them descending).

Whenever I add the sort or max, I get a "Data Type Mismatch" error. I tried excluding the nulls and values with letters. I've written a function to set those to zero and CLng the numbers. No matter what I've tried, I can't get the results I need.

Any suggestions?

 

[lespaul]

what's the SQL?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[NMiller007]

Here's one version:

SELECT dbo_coverage.loan_id, IIf([coverage_requirement] Is Null,0,IIf([coverage_requirement] Like "*[A-Z]*",0,CLng([coverage_requirement]))) AS [Number]
FROM dbo_coverage INNER JOIN dbo_accounts ON (dbo_coverage.loan_id = dbo_accounts.loan_id)ORDER BY dbo_coverage.loan_id, IIf([coverage_requirement] Is Null,0,IIf([coverage_requirement] Like "*[A-Z]*",0,CLng([coverage_requirement]))) DESC;

Here's another example using a Public Function:
SQL

SELECT dbo_coverage.loan_id, ConvertNumber([coverage_requirement]) AS [Number]
FROM dbo_coverage INNER JOIN dbo_accounts ON dbo_coverage.loan_id = dbo_accounts.loan_id
ORDER BY dbo_coverage.loan_id, ConvertNumber([coverage_requirement]) DESC;

Function

Public Function ConvertNumber(requirement As String) As Long

    If ((requirement Is Null) Or (requirement Like "*[A-Z]*")) Then
        ConvertNumber = 0
    Else
        ConvertNumber = CLng(requirement)
End Function

Thanks!

 

[PHV]

Why not simply use the Val function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NMiller007]

Unfortunately, some of the values are entered with commas. Val(3,000) gives me 3.

I suppose I could go through and eliminate the commas using an InStr loop and then Val the results...

I'll give that one a go.

Thanks!

 

[NMiller007]

Okay, here's what I was able to come up with using the Val function:

Public Function ToNumber(inputString As String) As Long

    If inputString Like "*[A-Z]*" Or inputString Like "*[a-z]*" Then
        ToNumber = 0
    Else
        Do While InStr(inputString, ",") > 0
            inputString = Left(inputString, InStr(inputString, ",") - 1) & Mid(inputString, InStr(inputString, ",") + 1)
        Loop
    
        ToNumber = Val(inputString)
    End If
    
End Function

It seems to work, but it is pretty slow. I'm assuming that is caused by the loop, but I guess I don't see anyway around it.

I suppose I could just count the commas and then guess what the number is. I'm going to try and speed it up using that method and see how accurate that is. Any other ideas to speed this up?

 

[lespaul]

why not use REPLACE instead? I think the syntax is:

SELECT Val(REPLACE(FieldName, ",", "")) FROM TableName

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[NMiller007]

That works great.

Inside the query, I am using:

Max(Val(Replace(field, ",", "")))

It seems much faster than using the loop inside of a function (obviously).

Thanks for the help!