Welcome to C l e a r G l a s s

I’ve found this awesome string parsing function for SQL written by Nick Barclay from Australia. The function returns the character position of the character after the Nth instance of the search string.

SQL Code:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO
 
CREATE FUNCTION InString(
 @string varchar(200),
 @searchfor varchar(50),
 @position int
) returns int
 
AS
BEGIN
DECLARE @lenstring int, @poscount int, @stringpos int
SET @lenstring = datalength(@searchfor)
SET @poscount = 1
SET @stringpos = 1
WHILE @poscount < = @position and @stringpos <= len(@string)
 – string segment found
 BEGIN
  IF substring(@string, @stringpos, @lenstring)=@searchfor
  BEGIN
   – is the one we are looking for?
   IF @poscount = @position
    BEGIN
     SET @stringpos = @stringpos + @lenstring
     return @stringpos
    END
   ELSE – look for the next instance
    BEGIN
     SET @poscount = @poscount + 1
    END
  END
  SET @stringpos = @stringpos + 1
 END
 return null
END
GO
 
GRANT EXEC on dbo.InString TO PUBLIC
GO

Example 1:

Returns 18
select dbo.InString('123456 123456 123456 123456', '23', 3)

Example 2:

Returns 25
select dbo.InString('test1/test2/test3/test4/test5/', '/', 4)

Example 3:

Returns ‘test4’
declare @teststring varchar(50)
set @teststring = ‘test1/test2/test3/test4/test5/’
select substring(@teststring, dbo.Instring(@teststring,’/',3),5)

Example 4:

Returns ‘textxyz’
(variable length delimited fields
declare @teststring2 varchar(50)
set @teststring2 = ‘test123/test/testtestestest/testxyz/test/’
select substring( @teststring2,
dbo.Instring(@teststring2, ‘/’, 3),
(dbo.Instring(@teststring2, ‘/’, 4) -1)
- dbo.Instring(@teststring2, ‘/’, 3)
)

How do you do conditional if (), else if(), else statements in MS Access for the control source using the Expression Builder?

Here is the format:

=IIf(condition, true, false)

Example 1:

=IIf([isOfficer]="0","Non-Officers","Officers")

Pseudocode:

If (isOfficer = "0") Then
 Print “Non-Officers”
Else
 Print “Officers”
End If

Example 2:

What if you want to add another conditional statement? The only solution is to nest another IIf() function in the False part of the Condition.
=IIf([isOfficer]="0","Non-Officers",IIf([isOfficer]="-1","All Officers","Both"))

Pseudocode:

If (isOfficer = "0") Then
 Print “Non-Officers”
Else If (isOfficer = “-1″) Then
 Print “All Officers”
Else
 Print “Both”
End If