How to check positioning of text in VBA? (1 Viewer)

[hclifford]

Hi all,

I am required to write a code to check the positioning of a text in a string.

E.g.

"~[Admin]~xxxxxxxxx~ACK~" = Acknowledged
"~xxxxxxxxx~[Admin]~ACK~" = Not Acknowledged
"~xxxxxxxxx~ACK~[Admin]~" = Not Acknowledged

As you can see, as long as the string starts with "~[Admin]~", it is counted as acknowledged. If the "~[Admin]~" appears anywhere else except the start, it is counted as not acknowledged.

I know I'll have to use If-else statements and EOF to cycle through my entire column, but how do I check the positioning of "~[Admin]~"?

Are there wildcard statements that Access VBA can use similar to SQL's "*"?

 

[smig]

IIf(InStr(YourTestString, "~[Admin]~") = 1, "OK", "Not OK")

Or

dim IsOK as Boolean

If InStr(YourTestString, "~[Admin]~") = 1
IsOK = True
Then
IsOK = False
End if

 

[hclifford]

IIf(InStr(YourTestString, "~[Admin]~") = 1, "OK", "Not OK")

Or

dim IsOK as Boolean

If InStr(YourTestString, "~[Admin]~") = 1
IsOK = True
Then
IsOK = False
End if

Hi!

I tried your boolean method, but I'm not sure if I'm doing it the way you intended me to do. The code is not working exactly right.

Private Sub Command2_Click()
    Dim IsOK As Boolean
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstInsert As DAO.Recordset
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Table5")
    Set rstInsert = dbs.OpenRecordset("Table5")
    
    If Not rst.EOF Then
        Do
            rst.AddNew
            If InStr(rst![Field1], "~[Admin]~") = 1 Then
                IsOK = True
                rstInsert![Field2] = "yes"
            Else
                IsOK = False
                rstInsert![Field2] = "no"
            End If
            rst.Update
            rst.MoveNext
        Loop Until rst.EOF
    End If
                        
End Sub

 

[smig]

The boolean is not required if you save it into another field.

Its not working because you add new record to rst not to rst Insert.
You need to update rstInsert not rst.

Marlan - you are using 2 functions (len & left) which are normaly slower then 1 function (inStr)

 

[hclifford]

The boolean is not required if you save it into another field.

Its not working because you add new record to rst not to rst Insert.
You need to update rstInsert not rst.

Marlan - you are using 2 functions (len & left) which are normaly slower then 1 function (inStr)

Hi Smig,

I did as you said and ended up with this code.

Private Sub Command2_Click()
    Dim IsOK As Boolean
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstInsert As DAO.Recordset
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Table5")
    Set rstInsert = dbs.OpenRecordset("Table5")
    
    If Not rst.EOF Then
        Do
            rst.AddNew
            If InStr(rst![Field1], "~[Admin]~") = 1 Then
                IsOK = True
                rstInsert![Field2] = "yes"
            Else
                IsOK = False
                rstInsert![Field2] = "no"
                rstInsert.Update
            End If
            rst.MoveNext
        Loop Until rst.EOF
    End If
                        
End Sub

However, I'm receiving error 3020 "Update or CancelUpdate without AddNew or Delete." I've tried rearranging it, but it doesn't work.

 

[smig]

it's not working because you AddNew to rst and not to rstInsert.
move the rstInsert.Update just before the rst.MoveNext, so you update both for Yes and No.

The IsOK variable is not required.

don't forget to clean up at the end of the procedure:
rst.close
set rst = nothing
rstInsert.close
set rstInsert = nothing

But wait !!!
Are both rst and rstInsert the same table ???
If YES you must edit (rst.Edit), not adding a new record.
You don't need rstInsert at all.

 

[hclifford]

it's not working because you AddNew to rst and not to rstInsert.
move the rstInsert.Update just before the rst.MoveNext, so you update both for Yes and No.

The IsOK variable is not required.

don't forget to clean up at the end of the procedure:
rst.close
set rst = nothing
rstInsert.close
set rstInsert = nothing

But wait !!!
Are both rst and rstInsert the same table ???
If YES you must edit (rst.Edit), not adding a new record.
You don't need rstInsert at all.

Thanks Smig!

I managed to get it running already! Learnt a lot about manipulating recordsets from you

 

[marlan]

Marlan - you are using 2 functions (len & left) which are normaly slower then 1 function (inStr)

Thank-you, I saw your method is better and deleted my post...

 

[smig]

glad I could help