How to extract string using delimiter 3

[bearfish]

I have this field InvID which is auto increment primary key.

In this lstRecord_DblClick function, i try to use the Left() function to extract the InvID from the program interface which is a lstRecord field. But when the my InvID is 1, or 2 which is one digit, i get the error message: Syntax error(comma) in query expression 'InvID=1,'. I realise i can't use the length as 2, because, this number can grow to more than 2 digit. So i try to use the Split function using the "," delimiter, but i get: Runtime error '13': Type mismatch.
do anyone know whats wrong?

Private Sub lstRecord_DblClick()
Dim selItem As Variant

selItem = selItem + Me.lstRecord.Text
'sInputDat = Left(selItem, 2)
sInputDat = Split(selItem, ",", 0, 1)

moCommand.CommandType = adCmdText
moCommand.ActiveConnection = moConnection
moCommand.CommandText = _
"SELECT ControlNo, CategoryType FROM Inv WHERE InvID = " & sInputDat & ";"

Set rsMain = moCommand.Execute
Me.cboCategoryType = rsMain!CategoryType
Me.txtControlNo = rsMain!ControlNo

End Sub

 

[LPlates]

sInputDat = Split(selItem, ",", 0, vbTextCompare) or ..

sInputDat = Split(selItem, ",&quot

 

[Kryzsoccer]

I'm assuming sInputDat is not an array which is why u are recieving the error
try something like this:

Private Sub lstRecord_DblClick()
    Dim selItem As Variant
     Dim MyArray() as string
       
    selItem = selItem + Me.lstRecord.Text

    MyArray = Split(selItem, ",", 0, 1) 'Split must be used with an ARRAY
sInputDat=MyArray(0) 'Assuming u want what is to the left of the first comma   
        
    moCommand.CommandType = adCmdText
    moCommand.ActiveConnection = moConnection
    moCommand.CommandText = _
       "SELECT ControlNo, CategoryType FROM Inv WHERE InvID = " & sInputDat & ";"
        
    Set rsMain = moCommand.Execute
    Me.cboCategoryType = rsMain!CategoryType
    Me.txtControlNo = rsMain!ControlNo
        
End Sub

 

[Kryzsoccer]

And LPlates is right,

MyArray = Split(selItem, ",", 0, 1) 'Split must be used with an ARRAY

should be

MyArray = Split(selItem, ",&quot 'Split must be used with an ARRAY

 

[LPlates]

Kryzsoccer, I believe the error due to the fact that the 1 in bearfish's split code refers to vbBinaryCompare which is giving a mismatch

 

[bearfish]

Hi guys,
Thanks alot. It works by using the Array().

 

[johnwm]

Your original Left was fine - you don't need all the complication! Just look for the comma:

sInputDat = Left(selItem, instr(1,selItem,",&quot-1)

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[bearfish]

Thanks johnwm. It works too. I didnt realise Left() function can be extended.

 

[johnwm]

You're just passing:
instr(1,selItem,",&quot-1
as the second argument to the Left function. Most functions will take the result of other functions as arguments, as (in general) the bits inside the brackets are evaluated first.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'