comparing strings

[JohnE]

Hello. I have a situation in which I need to compare a text string in the
front end to the db field in the back end sql 2005 table. An example is if
the front end text field is 6.0.1, the table could have 6.0.2. This will
throw an error. What I would like to do is compare only up to the second
dot. But, a caveat here is that the numbers could be double digits (6.15.5).
Only need to compare the 6.15. I am lost on this. I used Left() which is
only going to work for the single digits. Need something more flexible.

Any help is appreciated.

Thanks.
John

 

[Stuart McCall]

Hello. I have a situation in which I need to compare a text string in the
front end to the db field in the back end sql 2005 table. An example is
if
the front end text field is 6.0.1, the table could have 6.0.2. This will
throw an error. What I would like to do is compare only up to the second
dot. But, a caveat here is that the numbers could be double digits
(6.15.5).
Only need to compare the 6.15. I am lost on this. I used Left() which is
only going to work for the single digits. Need something more flexible.

Any help is appreciated.

Thanks.
John

Assuming there will always be two dots:

Dim pos As Long, result As String

'this finds the index of the 2nd dot
pos = InstrRev(BackEndString, ".")

'which we use to extract the numerics
result = Left$(BackEndString, pos - 1)

 

[JohnE]

Used what you sent, tweaked things a bit and it is working. My headache is
going away.

Thanks.

.... John

 

[John W. Vinson]

Hello. I have a situation in which I need to compare a text string in the
front end to the db field in the back end sql 2005 table. An example is if
the front end text field is 6.0.1, the table could have 6.0.2. This will
throw an error. What I would like to do is compare only up to the second
dot. But, a caveat here is that the numbers could be double digits (6.15.5).
Only need to compare the 6.15. I am lost on this. I used Left() which is
only going to work for the single digits. Need something more flexible.

Any help is appreciated.

Thanks.
John

There's a rather obscure and peculiar optional FIRST argument to Instr which
will help here:

Left([fieldname], InStr(InStr([fieldname], ".")+1, [fieldname], ".")-1)

The "inner" InStr specifies where in the string to start looking for the
period (e.g. right after the first period).