Like Operator

[Brian Martin]

I'm trying to say in my code if the field in recordset T is like Me!txt* Then add 1 to count. eg. would be True if T!

 = aa and Me!txt = aabbcc  My code is wrong and I'm not sure how to word the Me!txt* part.  

If ( T![code] Like Me!txt"*")= True Then
    Count = Count + 1

What's the correct way to write this?

 

[Brian Martin]

Update

This is the second attempt I've had which is closer to what I really want but it still isn't right!

If (Me!txt Like T!

&"*") = True Then
    Count = Count + 1

Any ideas?

 

[Brian Martin]

Another Clue!!!!!

If Me!txt = any value then count is incremented but if Me!txt is blank then no matches are found. Just another clue!!

 

[IfButOnly]

Brian, are you after something like this?

IF Left(Me!txt,len(T!code) = T!code then ...

 

[Brian Martin]

Any other ideas

Thanks for the reply but I don't think that is what I'm looking for! I think I've almost got what I want but I've just got quotes in the wrong place or missing brackets or something! Any more ideas??

 

[Jon K]

If T!

 Like Me.txt & "*" Then
  Count = Count + 1
.....
End If

 

[Brian Martin]

Thanks for the reply!! Sorry but I must've put you off because my first post was actually wrong! It is the other way round I need the code. eg code = aaa and Me!txt = aaauuurrr. Ur code is exactly the same as mine except the other way round but mine still doesn't seem to work!

 

[Brian Martin]

The Code

here is the whole code! See if this helps!

Private Sub Command21_Click()
Dim D As Database
Dim T As Recordset
Dim C As Boolean
Dim Count As Integer

Set D = CurrentDb()
Set T = D.OpenRecordset("tblPartCodes")
Count = 0
T.MoveFirst
Do Until T.EOF = True
If Me!txt Like T!

 & "*" Then
    Count = Count + 1
    T.MoveNext
    Else
        T.MoveNext
    End If
Loop

If Count = 0 Then
    MsgBox "No Code"
End If
End Sub

 

[Jon K]

Your code seems to run fine on my system.


I created a table tblPartCodes, added two records of Code aaa, and then added MsgBox Count in the code after the loop.

When I typed a string that began with at least three a's in Me!txt on the form and clicked on the command button, the message box returned the correct Count of 2.

If the string began with less than three a's or was blank, it returned 0. So the Count was again correct.

 

[Brian Martin]

Thanks for going to all that trouble! I'll try making a new form and using the same code and I'll let you know how i get on!!

 

[Brian Martin]

Jon K! Can you try typing a completely different code like qqq and see what happens!

 

[Jon K]

qqq returned 0.

 

[pdx_man]

Be sure you are including the ampersand:

Like Me!txt & "*")=

---------- ^

 

[Brian Martin]

Sorted

I have sorted the problem! After hours of trying different things with the code it turned out that it wasnt working properly because I had a blank record in my part codes table. Unbelievable but atleast its sorted. Anyone explain why this stopped it from working properly? Thanks everyone for all your help!

 

[dennis g]

Brian:

Not by any means am I a VBA pro, but I believe your problem lies in the area of null values. Read up on that to see if you need to code for a null value possibility.