String in quotes with like function

[Thompyt]

Dim Cnt As Integer, Letr As String, CLike As String, D As Integer



For Cnt = 1 To 2
Letr = Chr(64 + Cnt)
CLike = " '" & Letr & " *'"
Next Cnt

It comes out:
Clike = 'A *' or 'B *' which I thought was equivalent to "A *" or "B *".

If rv![Netbase] Like CLike And rv![Echelon] = "CO" Then
D = Cnt

Should only select something in [Netbase] = "A 1BN26IN" or "B 1BN26IN."

D would = 1 for A and 2 for B

Yet it selects all records as if it = [Netbase]Like "*"


I tried this - Like "[A-J] *" and I will get the total count of CNT, not where I would get the count of the letter where it equals.

Netbases: IIf([Netbase] Like "[A-J] *" and Echelon]="CO",[Netbase],"Wrong") does single out what I need, but doesn't count the letter position of A through J and stop on the letter count where the criteria is met.


What am I missing?

Steve, your suggestion helped earlier. I thought I had this part hacked out already.

Thanks

[CJ_London]

not sure what you are trying to achieve like is not a function

It comes out:
Clike = 'A *' or 'B *' which I thought was equivalent to "A *" or "B *".

needs to come out like this
Clike= "A*" or CLike like "B*"

or you could try

left(netbase,1) in ("A","B")

with regards this

Netbases: IIf([Netbase] Like "[A-J] *" and Echelon]="CO",[Netbase],"Wrong"), but doesn't count the letter position of A through J and stop on the letter count where the criteria is met.

there is a missing square bracket so would error out but assuming that is a typo then the letter (A...J) will always be the first character in netbase otherwise it would not be returned

[Thompyt]

This works, but I was trying to make a shorter more eloquent way of doing this.

Private Sub COLevel_Click()

Dim rv As DAO.Recordset, Z As Integer

Set rv = CurrentDb.OpenRecordset("Units")

If rv.BOF And rv.EOF Then
                rv.Close
            Else
                rv.MoveLast
                rv.MoveFirst
    Do While Not rv.EOF
           
      If rv![Netbase] Like "A *" And rv![Echelon] = "CO" Then
                 Z = 1
      Else
      If rv![Netbase] Like "B *" And rv![Echelon] = "CO" Then
                 Z = 2
      Else
      If rv![Netbase] Like "C *" And rv![Echelon] = "CO" Then
                 Z = 3
      Else
      If rv![Netbase] Like "D *" And rv![Echelon] = "CO" Then
                 Z = 4
      Else
      If rv![Netbase] Like "E *" And rv![Echelon] = "CO" Then
                 Z = 5
      Else
      If rv![Netbase] Like "F *" And rv![Echelon] = "CO" Then
                 Z = 6
      Else
      If rv![Netbase] Like "G *" And rv![Echelon] = "CO" Then
                 Z = 7
      Else
      If rv![Netbase] Like "H *" And rv![Echelon] = "CO" Then
                 Z = 8
      Else
      If rv![Netbase] Like "I *" And rv![Echelon] = "CO" Then
                 Z = 9
      Else
      If rv![Netbase] Like "J *" And rv![Echelon] = "CO" Then
                 Z = 10
      Else
                 Z = 0
         End If
         End If
         End If
         End If
         End If
         End If
         End If
         End If
         End If
         End If
            rv.Edit
            rv!COID = Z
            rv.Update
            rv.MoveNext
  Loop

End If
       rv.Close
       Set rv = Nothing

End Sub

[CJ_London]

since I don't know what you are trying to achieve I don't think I can help you

[Thompyt]

Ajax,
I was trying to enumerate each company by its alpha designation where Echelon = "CO". The last code I posted works, but I was looking for a simplified code for it

I planned on using the first style code I posted.

Instead of having to replace the Alphabet Character A with the next sequence letter until J.

Example:
If rv![Netbase] Like "A *" And rv![Echelon] = "CO" Then
If rv![Netbase] Like "B *" And rv![Echelon] = "CO" Then
If rv![Netbase] Like "C *" And rv![Echelon] = "CO" Then
I could run a loop around it

For Cnt = 1 to 10
If rv![Netbase] Like "A *" And rv![Echelon] = "CO" Then
SequenceNumber = Cnt
Next N

Substituting a character set such as Letters = Chr(64 + Cnt) This gives me A through J

Then developing a code something like this:

Private Sub COLevel_Click()
Dim rv As DAO.Recordset, Cnt As Integer, Z As Integer, Letters As String


Set rv = CurrentDb.OpenRecordset("Units")


If rv.BOF And rv.EOF Then
                rv.Close
            Else
                rv.MoveLast
                rv.MoveFirst
    Do While Not rv.EOF
        For Cnt = 1 To 10
            Letters = Chr(64 + Cnt)
                
                If rv![Netbase] Like "'" & Letters And " * '" And rv![Echelon] = "CO" Then
                            Z = Cnt
                    Else
                            Z = 0
                rv.Edit
                rv!COID = Z
                rv.Update
                rv.MoveNext
                End If
        Next Cnt
  Loop


End If
       rv.Close
       Set rv = Nothing
End Sub

But I get a type mismatch 13 error on:
If rv![Netbase] Like "'" & Letters And " * '" And rv![Echelon] = "CO" Then



COID Update.accdb

I am gathering it has something to do with the quotes

[CJ_London]

why not use a simple update query?

UPDATE Units
SET COID=asc(left(netbase,1))-64
WHERE Echelon="CO"

[Thompyt]

UPDATE Units
SET COID=asc(left(netbase,1))-64
WHERE Echelon="CO"

That works where there are all O's as a default setting for COID and the data has never been manipulated. if all the fields were wiped somehow, then this puts back all but the 0 value. I do like the idea though. Thanks

I changed the code to this: UPDATE Units SET Units.COID = IIf([Echelon]="CO",Asc(Left([Netbase],1))-64,0);