Desperate--AGAIN

[Neiko]

I have run into a problem. My application used to take ID codes like IT00087. I have changed it to accept 0000087. Now I'm running into some validation problems. For example, when I remove the following line,
Elseif IsNumeric(strStatement) then
Valid SQL = strStatement
The section that I was having problems with works, but it disables another section. When I step through both options, they both get to this point:

Public Function ValidSQL(ByVal strStatement as String) As String
Dim strOUT As String, strCur As String
Dim intMax As Long, intLVC As Long

'function handles potential dangerous character combinations in a string to be used as a value in an sql statement
'returns "NULL" or single-quoted padded string with valid character combinations for an sql statement

strOUT = vbNullString
If strStatement = vbNullString then
ValidSQL = "NULL"
ElseIf IsDate(strStatement) Then
Valid SQL = "#" & Format(DateValue(strStatement), "Short Date") & "#"
Elseif IsNumeric(strStatement) Then
Valid SQL = strStatement
Else
intMax = Len(strStatement)
For intLCV = 1 to intMax
strCur = Mid(strStatement, intLCV, 1)
If strCur = " ' " Then
strCur = " ' "
ElseIf strCur = " | " Then
strCur = vbNullString
End if
strOUT + strCur
Next
ValidSQL = " ' " & strOUT & " ' "
End if
End Function

I am fresh out of ideas with this one. Hopefully I've givin enough information so that someone can help me. Thanks so much

[Cimperiali]

'as your id and a number now have no difference, the only way I can see is
'to tell it when it is an Id
private Sub Command1_Click()
MsgBox ValidSQL("This is a string id:") & ValidSQL(2000303, true) & ValidSQL("This is a number:") & ValidSQL(2000303)

End Sub

private Function ValidSQL(byval strStatement as string, optional blnId as Boolean = false) as string
'public Function ValidSQL(byval strStatement as string,optional blnId as Boolean = false) as string

Dim strOUT as string, strCur as string
Dim intMax as Long, intLVC as Long

'function handles potential dangerous character combinations in a string to be used as a value in an sql statement
'returns "null" or single-quoted padded string with valid character combinations for an sql statement

strOUT = vbNullString
If strStatement = vbNullString then
ValidSQL = "null"
ElseIf IsDate(strStatement) then
ValidSQL = "#" & Format(DateValue(strStatement), "Short date") & "#"
ElseIf IsNumeric(strStatement) And blnId = false then
ValidSQL = strStatement
else
intMax = len(strStatement)
for intLVC = 1 to intMax
strCur = mid(strStatement, intLVC, 1)
If strCur = " ' " then
strCur = " ' "
ElseIf strCur = " | " then
strCur = vbNullString
End If
strOUT = strOUT & strCur
next
ValidSQL = " ' " & strOUT & " ' "
End If
End Function


'Hope this may be of help





Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater