check a field to see if it is divisible by 10

[joeythelips]

Hi,

i need to check whether a value in a filed called multiplier is divisible by 10 ie there should be no decimal points after the division. Basically, i am looking for numbers like 10, 20, 30, 40 etc.

I already have a check on this field to make sure its value is numeric. (see code below)
Sometimes the multiplier will be 1. Obviously 1 divided by ten does not give you a whole number, but i want this to be accepted.

Could someone help me with the code please.

'quality check:checking for blank multiplier
strsql = "SELECT count(*), min([MPRN]) from startupbills WHERE Isnumeric([Multiplier])=False"
Set rs2 = CurrentDb.OpenRecordset(strsql, dbOpenForwardOnly)
If rs2(0) > 0 Then
MsgBox "The file '" & startfilename & "' has " & rs2(0) & " Invalid [Multiplier] record(s). The first occurance is '" & rs2(1) & "'", vbExclamation
GoTo ExitHandler
End If
rs2.Close

Thanks in advance.

Joe

 

[jrbarnett]

Joe

To check if a number is divisible by 10 with no remainder:

If number Mod 10 = 0 Then
' it is
Else
' it isn't
End If

Your recordset code will only produce 1 record in the results because you only use statistical information and don't group by on any records.
As I don't know exactly what data you want to retrieve, I can't really help until I know more.

John

 

[joeythelips]

Hi JOHN,

i currently output a message box to the user saying there are some records with invalid data. The first record no is then listed.

Is it possible to build in the check to see if a number is divisible by ten into my code?

Joe

 

[jrbarnett]

Joe

You can do this, just add a new column to the query thus:

strsql = "SELECT count(*), min([MPRN]), IsDivTen: (Multiplier Mod 10 = 0) from startupbills Group by (Multiplier Mod 10 = 0)"

This will add an extra calculated column called "IsDivTen" with a True or False value dependent on the Multiplier value as appropriate.
I have removed the where clause, because this will filter out non numeric values - which will exclude any that are valid as well as non valid numbers.
You then have to test for a value being = True in this column or = 1 for your alternative value.

John