sql problem

[nokia3650]

i have an update querry that should pull number out of a string.the strings are standardized(same). i thought it would be good to pull out the start position of the nummber and the end position of the number.

here is the code for start position:

Public Function isPocetakBroja(ByVal IngFactor As String) As Long

Dim pocetakBroja, krajBroja, drugipocetakBroja As Long
'assumes that the FactorSum value is a number, and is a Long Integer.
Select Case Left(IngFactor, 8)
Case Is = "dov. zal"
pocetakBroja = InStr(10, IngFactor, "dana (", vbTextCompare) + 6
drugipocetakBroja = InStr(10, IngFactor, "dana (>", vbTextCompare) + 7
If drugipocetakBroja > pocetakBroja Then
isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)
Else
isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
End If
Case Is = "dovoljno"
pocetakBroja = InStr(5, IngFactor, "no (", vbTextCompare) + 4
drugipocetakBroja = InStr(5, IngFactor, "no (>", vbTextCompare) + 5
If drugipocetakBroja > pocetakBroja Then
isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)
Else
isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja) '...here is type mismatch
End If
Case Is = "sti¾e da"
isPocetakBroja = 5
Case Else 'use this if you need a default for records outside of all
isPocetakBroja = 999
End Select

End Function

the strings look like:

dovoljno (4 kom), poslije vi¹e ne.
sti¾e za 14 dana
dovoljno (11 kom)
dovoljno (>100 kom)

but i get a erron saying :
run time error 13

type mismatch

in line 20

where is the problem

how to solve it?

thx

 

[Ken Snell [MVP]]

Are you intending to declare these three variables as Long types in this
line:
Dim pocetakBroja, krajBroja, drugipocetakBroja As Long

If yes, your syntax is wrong. You must declare each variable specifically:
Dim pocetakBroja As Long, krajBroja As Long, drugipocetakBroja As Long

Your function "isPocetakBroja " is declared as Long. But in the code line
where the "type mismatch" is occurring, you're trying to set that variable
equal to a string:
isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)

You also have this mismatch in this line (just above the one that is
erroring):
isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)

So your code steps need to be changed so that the expressions on the right
will return numbers instead of strings.
--

Ken Snell
<MS ACCESS MVP>



i have an update querry that should pull number out of a string.the strings
are standardized(same). i thought it would be good to pull out the start
position of the nummber and the end position of the number.

here is the code for start position:

Public Function isPocetakBroja(ByVal IngFactor As String) As Long

Dim pocetakBroja, krajBroja, drugipocetakBroja As Long
'assumes that the FactorSum value is a number, and is a Long Integer.
Select Case Left(IngFactor, 8)
Case Is = "dov. zal"
pocetakBroja = InStr(10, IngFactor, "dana (", vbTextCompare) + 6
drugipocetakBroja = InStr(10, IngFactor, "dana (>", vbTextCompare) + 7
If drugipocetakBroja > pocetakBroja Then
isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)
Else
isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
End If
Case Is = "dovoljno"
pocetakBroja = InStr(5, IngFactor, "no (", vbTextCompare) + 4
drugipocetakBroja = InStr(5, IngFactor, "no (>", vbTextCompare) + 5
If drugipocetakBroja > pocetakBroja Then
isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)
Else
isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
'...here is type mismatch
End If
Case Is = "sti¾e da"
isPocetakBroja = 5
Case Else 'use this if you need a default for records outside of all
isPocetakBroja = 999
End Select

End Function

the strings look like:

dovoljno (4 kom), poslije vi¹e ne.
sti¾e za 14 dana
dovoljno (11 kom)
dovoljno (>100 kom)

but i get a erron saying :
run time error 13

type mismatch

in line 20

where is the problem

how to solve it?

thx

 

[nokia]

thx

can u tell me
if i have a string with number in it
and i know to substract only this part of string where the nummber is
how can i convert it into number?


thx

 

[Ken Snell [MVP]]

If you have a variable string that is "12345", use one of these functions to
convert it to number (depending upon what type of data you will have):
CLng (produces Long data)
CInt (produces Integer data)
CSgl (produces Single data)
CDbl (produces Double data)
CCur (produces Currency data)

--

Ken Snell
<MS ACCESS MVP>

 

[nokia]

i tried everything but i still get errors and type mismatch

here is a string:

"dovoljno (21 kom), poslije vi¹e ne"

how to get just "21" out of this string as a number?

thx

 

[Ken Snell [MVP]]

Ahhh.. sorry, I misunderstood you.

Here is a function that I've used to extract numbers from a text string and
build a single number string from them:

--------------------------------------
Public Function ExtractNumbers(strFieldValue As String) As String
' *** THIS FUNCTION EXTRACTS THE NUMBERS FROM A TEXT STRING BY
' *** CONCATENATING THE NUMBERS TOGETHER AND IGNORING ANY INTERSPERSED
' *** LETTERS.

Dim intLoop As Integer
Dim strHold As String, strX As String
strHold = ""
For intLoop = 1 To Len(strFieldValue)
strX = Mid(strFieldValue, intLoop, 1)
If IsNumeric(strX) = True Then _
strHold = strHold & strX
Next intLoop
ExtractNumbers = strHold
End Function
------------------------------


Put this function in a regular module. Then call it from your code:

MyNumberString = ExtractNumbers("dovoljno (21 kom), poslije vi¹e ne")
MyNumberValue = CLng(MyNumberString)

--

Ken Snell
<MS ACCESS MVP>