Instr Error trapping

[Thales750]

If there is no comma, the following line produces an error

 Me.txtProjectAddNew = Left(Me.cboSelectCustomer.Column(1), InStr(1, Me.cboSelectCustomer.Column(1), ", ") - 1)

What is the best practice for skipping this, if it will fail?

Thanks

 

[CJ_London]

if there is no comma - or to be more precise from your code a comma and space - then instr will return 0 so the error is caused by trying to return the left -1 characters of your string.

you can use an if statement to check for your string first of all

if InStr(1, Me.cboSelectCustomer.Column(1), ", ") <> 0 then 
    Me.txtProjectAddNew =Left(Me.cboSelectCustomer.Column(1), InStr(1, Me.cboSelectCustomer.Column(1), ", ") - 1)
end if

 

[Thales750]

I see, it returns a "0" because there is no position along the string.

Anyone interested in learning more about instr() Please check this out

http://office.microsoft.com/en-us/access-help/instr-function-HA001228857.aspx

 

[stopher]

If you want to return the whole string when there is no comma then what you want is a function that makes zero becomes the length of the string. You can use MOD to do this:

(InStr([x],",")+Len([x])) MOD (Len([x])+1)

where x = Left(Me.cboSelectCustomer.Column(1)

As an example, take the following text: x = "abcd,e"

(InStr([x],",")+Len([x]))= 11

so (InStr([x],",")+Len([x])) MOD (Len([x])+1) = 11 MOD 7 = 4


Another example: x = "abcdefg"

(InStr([x],",")+Len([x]))= 7

so (InStr([x],",")+Len([x])) MOD (Len([x])+1) = 7 MOD 8 = 7

hth

 

[vbaInet]

Good logic there Chris. Remember to add 1 to the result because Instr() would return 5 as the position of "," in "abcd,e".

 

[stopher]

Good logic there Chris. Remember to add 1 to the result because Instr() would return 5 as the position of "," in "abcd,e".

The examples I gave are the number to be used in the LEFT function. So 4 is the required number of charaters for "abcd,e". Sorry I didn't make that clear.

 

[vbaInet]

Ah alright!