Sum of the Digits plus the number of Digits 2

[gahill]

Hello:
My report is a Sales Tax report for the state.
Below are the fields.
Gross Sales
Expempt Sales
Net Taxable Sales
Reportable Sales Tax
Tax Liability
Discount
Net Amount Due
The Net Amount due is Calculated =sum([Taxamt])-[Discount]
For this week that amount is 5277.34
The State wants you to calculate a verification number this way.

5+2+7+7+3+4 plus the number of digits which in this case is 6 and the answer comes out to 34.

Help please!!! how can I do this with an unbound calculated field on the report.

Thank You

Gary W. Hill

 

[Blorf]

You are going to need a user defined function. Put the following into a module, and add
=CheckDigit (sum([Taxamt])-[Discount]) to your report.

ChaZ


Function CheckDigit(X As String) As Integer
Dim I As Integer
Dim J As String
Dim RTV As Integer
RTV = 0
For I = 1 To Len(X)
J = Mid(X, I, 1)
If J >= "0" And J <= "9" Then
RTV = RTV + 1
RTV = RTV + Val(J)
End If
Next I
CheckDigit = RTV
End Function

 

[gahill]

Hi Chaz:

Thank you for your quick post.

I do have a problem though.

Unbound Text Box with this formula in the control source
=CheckDigit(Sum([TAXAMT])-[Discount])

Function as you sent to me.

Function CheckDigit(X As String) As Integer
Dim I As Integer
Dim J As String
Dim RTV As Integer
RTV = 0
For I = 1 To Len(X)
J = Mid(X, I, 1)
If J >= "0" And J <= "9" Then
RTV = RTV + 1
RTV = RTV + Val(J)
End If
Next I
CheckDigit = RTV
End Function

The report now pops up a parameter screen asking for the Checkdigit.

I can't see the forest for the trees here I guess.

Can you spot what is wrong?

Thanks again.

Gary W. Hill

 

[MichaelRed]

Function basChkDgt(strX As String) As Integer

    Dim Jdx As Integer
    Dim RtnVal As Long
    Dim strVals() As String

    strVals = Split(StrConv(strX, vbUnicode), vbNullChar)

    While Jdx <= UBound(strVals)
        RtnVal = RtnVal + Val(strVals(Jdx))
        Jdx = Jdx + 1
    Wend        'Jdx

    basChkDgt = RTV + UBound(strVals) - 1

End Function

MichaelRed

 

[Blorf]

Hi MichalRed.

Can you explain what you did with the Split function? I see that you loaded each charachter into an array and used a while loop to cycle through them, but it's not clear to me.

Either way, will using your function solve the report problem? I don't entirely know why the report is asking for a paramater. My test doesn't ask, just runs with my function.

Thanks,
ChaZ

 

[gahill]

Hi Chaz:
I solved the problem of the report asking for a parameter.
I started a new module and just copied the code from this thread.
Don't ask me why it works because they both look the same.
There is another problem that popped up though.
The field is defined as Currency with 2 decimals.
The answer is coming up 51 instead of 34.
After looking in the immediate window the answer is coming through as 5277.34257 instead of the 5277.34.
If you use the checkdigit function and physically type in 5277.34 the answer correctly displays 34.
I need your help again please as I don't know how to correct this in the function.

Thanks again for your prompt reply.

And thank you Michael Red, I have not tried your solution yet but I will after I solve it the other way.

 

[Blorf]

Hi.

try this

=CheckDigit(round(Sum([TAXAMT])-[Discount]), 2)

That should drop the extra digits.

ChaZ

 

[MichaelRed]

[/b]color blue]Blorf[/color][/b],

The report problem(s) are seperate form the procedure. Your procedure is is as good as any (mine included) for the actual processing. Per gahill's exchange, this is no longer the issue.

To respond to the isue re "Split", it is easily parsed and, perhaps, less confusing"

strVals = Split(StrConv(strX, vbUnicode), vbNullChar

is just the combiniation of:

strX = StrConv, StrA, vbUnicode)
strVals = Split(strX, vbNullChar)

assuming that StrA is the original "Arg" to the procedure.

strConv is an intrinsic function in VB(A), and you can rely on one of my favoriet characters "Mr. Ubiquitous" aka "H E L P", often invoked with {F1}. It merely transfers the string between ASCII and Unicode (at least for this exercise).

Th 'conversion to Unicode is significant because "english" ASCII will leave the second byte of the character as Chr(0) (also known as the null character, and co-repreasented by hte constant 'vbNullChar').

This, then, is used as the 'delimiter' character / seperator for the (again intrinsic) "Split" function. The remainder is the same as your procedure. This "Slight of Hand" was, for me. first exposed by one of the more capable members' of Tek-Tips (strongm, although it has beccome one of my favorite methods of parsing relatively short strings.







MichaelRed

 

[Blorf]

I think I understand.

16 bit ascii values are special charachters like the up arrow or what not. Your function fixes that and plops them in to place and references kinda like I would if I were back using C.

Thank you. I think the info will come in handy.

ChaZ

 

[gahill]

Chaz:
Access does not like the round function for some reason.
Going through help in the VBA and Access provides nothing.
Round doesn't even show up under their mathematical function list.
Now if I try running your function I always get a compile error.
I've tried everything I can think of with no success.
I tried MichaelRed's function but his returns the answer of 9 which is the number of digits in this answer.
Help please the State wants this report before Friday.
I could always put the answer in by hand but that defeats the purpose of the computer.
Thanks for your continuing help.

Gary W. Hill

 

[MichaelRed]

Function basChkDgt(strX As String) As Integer

    Dim Jdx As Integer
    Dim RtnVal As Long
    Dim strVals() As String

    strVals = Split(StrConv(strX, vbUnicode), vbNullChar)

    While Jdx <= UBound(strVals)
        RtnVal = RtnVal + Val(strVals(Jdx))
        Jdx = Jdx + 1
    Wend        'Jdx

    basChkDgt = [b][COLOR=red]RtnVal[/color][/b] + UBound(strVals) - 1

End Function

Also. "Round" is an intrinsic function of core VB, and thus a 'correct' procedure. You should check your implementation (e.g. check for typos).

Ity is not possible for (my version of) the procedure to work at-all wthout a recent vesion of Ms. A. as Split was introduced in ver 2K, but [colorblue]Blorf[/color]'s should work in any / all versions which have VBA installed (it was optional in some earlier versions).

If you continue to experience problems with code functions, as for some support re the installation details of your system.






MichaelRed

 

[gahill]

Michael:
Thank you and Chaz both for the excellant help.

Problem resolved.

Gary W. Hill