Return amounts divisible by 12

[chris-uk-lad]

Hi all, im kinda new at this sort of thing so bear with me please.

I have a table which contains customer details (its a project based on loans). In a table i have various details, but whats important to me are a reference number that relates to the customer, and a total loan amount.

I can create the query which returns all the information i want, but within the query i want it to work out the loan into monthly amounts, i.e £1,800,00 divided by 12, but only if it is divisible by 12, otherwise return NULL to say it is not.

So say:
REF Loan
123456 £1,800.00 (which will return amount as can be divided)
234567 £1,633,01 (which will return NULL as cannot)

All help is greatly appreciated.

 

[pbaldy]

Welcome to the site. Take a look at the Mod function.

 

[namliam]

Look up the "mod" and "int" functions in the help. Either will do the trick

 

[chris-uk-lad]

i dont know if im understanding this right, the MOD function just provides a remainder, and sums up the numbers after the decimal. If the amount returned is longer than 2 decimal points (which will count as pence in my case) then it wont be divisible by 12 but will still round to a single number.

Sorry im not great at this, please help me understand more

 

[pbaldy]

You test for a remainder. This will provided the value if divisible by 12, Null otherwise:

IIf([FieldName] Mod 12=0,[FieldName],Null)

 

[chris-uk-lad]

Thanks for the quick response, much appreciated.

Ive just tested this on my values with the loan amount £24,479.73 which is not divisable by 12 (result = 2039.9775) yet shows as not being NULL where as £1,425.12 is divisible by 12, but shows NULL aswell as some i know to be NULL showing as NULL.

I must be missing something, sorry to be a pain.

 

[chris-uk-lad]

heres an image to assist, after running the query. Expr1 is the amount divided by 12, Expr2 is the result of the MOD function. As you can see, only REFNO 2 should not be NULL.

 

[KenHigg]

I'm not sure but I think you're going to have to multiply 'loan' by 100 first to drive the decimal over 2 places - ?

 

[raskew]

Or, depending on whether the pence portion figures einto the equation, you could use the Int() function, e.g.

? iif(int(1834.09) mod 12 = 0, "Winner", "Sorry")
Sorry 'returned 10

? iif(int(11220.70) mod 12 = 0, "Winner", "Sorry")
Winner 'returned 0

Bob

 

[pbaldy]

It does appear to round first, so I guess we need to clarify your goal. How is 1,425.12 divisible by 12? It does not result in a round number, which is how I would define it.

 

[chris-uk-lad]

Im wanting the to show the loan amount divided into monthly installments. And highlight which are immediately rounded to the nearest pence (e.g £100.34) and which dont immediately show as an accurate monthly amount. (e.g £100.3434).

Just want an initial readout to show which need to be looked at further and which are accurate amounts.

 

[pbaldy]

Have you tried Ken's suggestion?

 

[chris-uk-lad]

Yup it appears to work with what i have, wont know fully till i check the full table i have at college. Thanks for the suggestions, ill let you know if it works

 

[KenHigg]

I think you're ultimately going to have to take result of the mod and tack it onto the last payment as next to nothing is ever going to be exactly devisable(sp?) by 12 (or any number as far as that goes)...

Unless I'm missing something - As I frequently do

 

[MSAccessRookie]

My experience has been that they usually round it up and then the last payment is a little less (get the money up front).

 

[KenHigg]

... Or divide by 11 and have the mod result be the 12th payment ?

 

[raskew]

Over a 12 month period you're talking about a max of 12 pounds +/-. It'll all balance out. Give it a rest.

Bob

 

[chris-uk-lad]

it worked, thanks for your help guys