By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,374 Members | 1,768 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,374 IT Pros & Developers. It's quick & easy.

Counting Partial Field Matches

P: 1
Greetings,

I am searching a field that contains one or more instances of a 3-letter "error" code.

I am using a WHERE clause similar to:
((tblName.fldName) Like [tblErrorCodes].[fldErrorCode] & "*")

The above returns all the correct records.
A typical record may contain "ABC, DEF" within a single field.
My question is...How do I count the number of "errors" for each record returned? In the case above, I would like to return "2".

Thanks,
AdminSparky
Mar 9 '07 #1
Share this Question
Share on Google+
5 Replies


MSeda
Expert 100+
P: 159
well, ideally each error code would be stored in a separate record.
But assuming all error codes are three letters and separated by a comma and a space then (Len([fldErrorCode] - 3)/5 + 1 ought to return an accurate count.
Mar 10 '07 #2

ADezii
Expert 5K+
P: 8,627
Greetings,

I am searching a field that contains one or more instances of a 3-letter "error" code.

I am using a WHERE clause similar to:
((tblName.fldName) Like [tblErrorCodes].[fldErrorCode] & "*")

The above returns all the correct records.
A typical record may contain "ABC, DEF" within a single field.
My question is...How do I count the number of "errors" for each record returned? In the case above, I would like to return "2".

Thanks,
AdminSparky
I'm sure that there is an easier way, it just doesn't dawn on me now. You can use a Public Function within an SQL Statement to return the number of occurrences of an Error Code within a single Field.
Expand|Select|Wrap|Line Numbers
  1. Public Function fCountErrorCodes(strFieldValue As String, strErrorCode As String) As Integer
  2. Dim intCurrentPosition As Integer, intNextPosition  As Integer
  3. Dim intNoOfErrorCodes As Integer
  4.  
  5. intNextPosition = 0
  6. intNoOfErrorCodes = 0
  7.  
  8. intNextPosition = InStr(1, strFieldValue, strErrorCode)
  9.  
  10. If intNextPosition = 0 Then
  11.   fCountErrorCodes = 0
  12.     Exit Function
  13. Else
  14.   Do Until intNextPosition = 0
  15.     intNoOfErrorCodes = intNoOfErrorCodes + 1
  16.     intCurrentPosition = intNextPosition + Len(strErrorCode)
  17.     intNextPosition = InStr(intCurrentPosition, strFieldValue, strErrorCode)
  18.   Loop
  19.  
  20.   fCountErrorCodes = intNoOfErrorCodes
  21. End If
  22. End Function
Mar 10 '07 #3

NeoPa
Expert Mod 15k+
P: 31,436
Expand|Select|Wrap|Line Numbers
  1. intCount = (Len(fldErrorCode)+2)/5
MSeda's logic is correct also, but a misplaced closing parenthesis may have caused it to fail when you tried it.
Mar 10 '07 #4

NeoPa
Expert Mod 15k+
P: 31,436
I'm sure that there is an easier way, it just doesn't dawn on me now.
There certainly is one ADezii - and MSeda already had it posted :D
22:00 - must've been bed-time huh?
Mar 10 '07 #5

ADezii
Expert 5K+
P: 8,627
There certainly is one ADezii - and MSeda already had it posted :D
22:00 - must've been bed-time huh?
Without a doubt. I have this unique ability to find the most complicated solution to what otherwise would be a simple problem.
Mar 10 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.