sql problem

  • Thread starter Thread starter nokia3650
  • Start date Start date
N

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
 
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
 
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
 
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>
 
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
 
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>
 
Back
Top