Count Capital Characters

[lovelornloser]

I was able to do this in excel, but sadly the formula doesn't work within Access.

We are migrating our excel database over which displays scheduled tasks (all 1000 of them) to Access. Certain tasks can be perform on different days, or daily - basically, it's more complex to create various different selections ( "Monday", "Monday, Tuesday" "Monday, Wednesday" etc) than it is for the user to place in a simple string ("M" "M, Tu" "M, W" etc). This worked well within excel as I was able to count only capital letters, though sadly I'm unable to perform this action within Access.

Sadly my VBA knowledge limited at best, and would prefer not to go down that route, if possible.

Any help is appreciated.

 

[Isskint]

I would suggest creating your own function - CountCapitals. All you would need do is pass the string to the function and then loop through each character of the string and check the ASC value (65-90 being capital letters)

Public Function CountCapitals(strInpect As String) As Integer
CountCapitals = 0
If strInpect & "" = "" Then Exit Function
Dim StrLn As Integer
For StrLn = 1 To Len(strInspect)
Select Case Asc(Mid(strInpect, StrLn, 1))
Case 65 To 90
CountCapitals = CountCapitals + 1
End Select
Next StrLn
End Function

 

[lovelornloser]

Will look into creating my own functions tomorrow.

Many thanks,

 

[lovelornloser]

This worked, thank you very much!

 

[gemma-the-husky]

with regard to text, access is by default, case insensitive.

monday, MONDAY, mOnDaY, are all the same.

however, in true database style, maybe you ought to be thinking in terms of replacing the day text with the numeric code that actually represents that day.

Then you can test a date by saying

if weekday(somedate)=vbmonday then ...

and by implication

if weekday(somedate)="required day code which is stored in my table" then ....

you may be able to use format - format(date,"dddd") returns a full text day

if format(somedate,"dddd") = "required full text day which is stored in my table" ....