IIF Statement Question 1

[webdevop]

I have a description varchar field with 3 possiblilites they look like:

AB.300X.300X.20
.150x.120ABCDEFGHIJK or
ABCDE.200HIJ

I need to create an IIF statement in a query to browse this field and count the number of periods. So 1 period=1, 2 periods=2, 3 periods=3. Since the periods are not in a static position or sequence can this be done ?

Any suggestions are much appreciated !!

 

[SgtJarrow]

What you'll need to do is create a function that will loop throught the string and count the periods....after counting the periods, return the number found. The evaulate against this function in your query to do whatever...

Public Function CountPeriods(strString As String) As Integer

Dim intCount As Integer
Dim intPos As Integer

intCount = 0
For intPos = 1 To Len(strString)
If Mid(strString, intPos, 1) = "." Then intCount = intCount + 1
Next intPos

CountPeriods = intCount

End Function

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[TonyJollans]

Hi webdevop,

If you have 2K or above, you can use the Replace function and should be able to do it inside an iif:

Iif(Len(Field)-Len(Replace(Field,".","&quot)=1,"One dot","Some other number&quot

(you don't say what you want to do subject to the number of periods).

Enjoy,
Tony

 

[webdevop]

I appologize if this is an easy question BUT
Once the function CountPeriod is created
how do I call it to create my new table column
with the results

Description Count
.124abcd.340 2

Thanks again ...

 

[SgtJarrow]

Add the column to your table.....
Create an update query based of this table....
For the Count column put the following in the UpdateTo line:

=CountPeriods([Description])

Run the query...

That should goive you the results you want.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[TonyJollans]

Hi webdevop,

Why do you want this on your table? Once you have coded the function you can use it whenever you need the result; that way you always have the correct current value.

As a general rule it's not good practice to keep derivable data on your table, not least because you have to make sure it's kept in line with any updates made to the data it's derived from, and I would recommend against doing it.

Enjoy,
Tony