Calculating check digit - no MOD in Access

[Argi]

Hello All, i am trying to calculate check digit, have it working fine with 1 glitch, when check digit is 0 my formula brings back a value of 10 as 10-0 is 10. I will paste formula, i know it is way over kill but keep in mind i am very new to Access and i had to figure this one out on my own. It would have worked but Access did not let me enter in any more statements as i had used maximum length in formula field. There must be a better way than mine, any help appreciated. The idea is to take the last digit after all the additions and then do 10-last digit ehich gives you the check digit value always should be between 0-9. My sum will either be 3 digit or 2 digit in any cases this is why i used the Len and IIf. Hope this does not drive anyoen crazy :banghead:


=IIf(Len(((Mid([UPC Zeea Number],1,1)*3)+(Mid([UPC Zeea Number],2,1))+(Mid([UPC Zeea Number],3,1)*3)+(Mid([UPC Zeea Number],4,1))+(Mid([UPC Zeea Number],5,1)*3)+(Mid([UPC Zeea Number],6,1))+(Mid([UPC Product Code],1,1)*3)+(Mid([UPC Product Code],2,1))+(Mid([UPC Product Code],3,1)*3)+(Mid([UPC Product Code],4,1))+(Mid([UPC Product Code],5,1)*3)))>2,10-Mid(((Mid([UPC Zeea Number],1,1)*3)+(Mid([UPC Zeea Number],2,1))+(Mid([UPC Zeea Number],3,1)*3)+(Mid([UPC Zeea Number],4,1))+(Mid([UPC Zeea Number],5,1)*3)+(Mid([UPC Zeea Number],6,1))+(Mid([UPC Product Code],1,1)*3)+(Mid([UPC Product Code],2,1))+(Mid([UPC Product Code],3,1)*3)+(Mid([UPC Product Code],4,1))+(Mid([UPC Product Code],5,1)*3)),3,1),10-Mid(((Mid([UPC Zeea Number],1,1)*3)+(Mid([UPC Zeea Number],2,1))+(Mid([UPC Zeea Number],3,1)*3)+(Mid([UPC Zeea Number],4,1))+(Mid([UPC Zeea Number],5,1)*3)+(Mid([UPC Zeea Number],6,1))+(Mid([UPC Product Code],1,1)*3)+(Mid([UPC Product Code],2,1))+(Mid([UPC Product Code],3,1)*3)+(Mid([UPC Product Code],4,1))+(Mid([UPC Product Code],5,1)*3)),2,1))

 

[boblarson]

You need to create a function for this. Anything this long should be in a function.

 

[plog]

I second what bob says. Also, there is a way to Mod a number. It operates oddly, its not really a function--you simply put 'Mod' after your number and then the base. This would return 7 in an Access query:

ModNumber: 247 Mod 10

 

[Argi]

Guys i have no idea hwo to create a function, so maybe i need to try to get the Mod thing working. 247 Mod 10 does return 7 and this is the result i want however myexpression returns the sum of 89. If i do this Myexpression Mod 10 it returns 79 instead of 9

=(Mid([UPC Zeea Number],1,1)*3)+(Mid([UPC Zeea Number],2,1))+(Mid([UPC Zeea Number],3,1)*3)+(Mid([UPC Zeea Number],4,1))+(Mid([UPC Zeea Number],5,1)*3)+(Mid([UPC Zeea Number],6,1))+(Mid([UPC Product Code],1,1)*3)+(Mid([UPC Product Code],2,1))+(Mid([UPC Product Code],3,1)*3)+(Mid([UPC Product Code],4,1))+(Mid([UPC Product Code],5,1)*3) Mod 10

Any ideas?

 

[Argi]

Got it working, thanks. Created new field and referenced field using Mod 10 and it worked, thanks for the pointers.

 

[Galaxiom]

Functions are easy. Insert a Standard Module and put the code in there. It is then available throughout the Project.

Here is a trivial example of a function. Not much different from a Sub except it returns a value.

Public Function Times2 (somevariable as Long) As Long
whatever = somevariable * 2
End Function

The function allows you to break down the code into multiple lines and work through the logic in pieces.

One of the other great things about functions is the queries where they are used are live. You can have a query open and edit the function. The change in the function is immediately reflected in the query results without rerunning it, allowing you to test the function continuously while you work on it.

 

[G37Sam]

Public Function Times2 (somevariable as Long) As Long
whatever = somevariable * 2
End Function

Not sure this function would return anything though

 

[Argi]

Thank you for the great infromation.

 

[Galaxiom]

Not sure this function would return anything though

Quite right. I started with a function named whatever and then changed it without fixing the return line.

Public Function Times2 (somevariable as Long) As Long
Times2 = somevariable * 2
End Function