Length and substitute

[texasalynn]

I have a field that has data

S-TW--S Len=4
---WTF- Len=3
--T-T-- Len=2
etc

I need to query this to give me a "LEN" without the "-". I know how to do this in excel with the substitute function. How do I get this in access?

texasalynn

 

[EndersG]

Open up a new Module and cut, paste and save this snippet of code:

Public Function CharCount(strChr As String, strSearch As String) As Integer
Dim iCounter As Long
For iCounter = 1 To Len(strSearch)
If Mid(strSearch, iCounter, Len(strChr)) = strChr Then
CharCount = CharCount + 1
End If
Next
End Function

The above function will return the number of characters you want to extract from the specified string. As for the field in which you want to capture the final result, paste this piece of code as an expression in a new field of the Query design grid:

Len([String])-CharCount("-",[StringTemp])

HTH

 

[texasalynn]

That's great but I get an error:

Undefined function 'CharCount' in expression.

Did I miss something?

thanks. . .
texasalynn

 

[ColinEssex]

Hi

I've just checked this and found that if you delete the word "Public" then it works fine.

Col

 

[EndersG]

Thanks Colin. I wasn't as diligent as I should have been when testing the code. I hope it worked out for texasalynn.

 

[texasalynn]

Do I need to put in the field name somewhere.

Sorry like I said I'm not a VBA person. I tried putting in the field
len: Len([Invoice History Detail]![HDPUDY])-CharCount("-",[StringTemp])

Still get message:
Undefined function 'CharCount' in expression.

Or is there something in the module that needs to have the field name?

Thanks . . .

 

[EndersG]

Hi texasalynn,

Here's a sample of the solution in an Access 97 database. Enjoy.

 

[texasalynn]

Thanks! That was weird - I had to save as/export from yours into my database. I had everything the way yours was, but every time I got the same error message. After I exported yours into my database it worked.

That is great. Wish I knew how to do that. I am reading a book about VBA in Excel so maybe it will help.

Thanks again.
texasalynn