Use of java script Formula in the Access 2003

K

Kamila

Hi
I am new to use with access but have some idea how to use. I have question
I have access table ID and it contain 3000 ID numbers. Length of ID number
is must be 11 and i have one formula for checking of right IDs and i want to
use that formula in Access to check right ID and give me result of wrong or
right IDs.

How can i use that forumula in the access? I make formula in the Java script
and dont know how to use in access 2003.

<html>
<head>
<title> JScript for checking ID no </title>
</head>
<body>
<form action="" name="pnform">
<input type="button" value="Persnr" onclick="ValidateID()">
</form>

<script type="text/jscript">

function ValidateID()
{
var ID="22037035991" //!! ID no must be 11 sifer, it is right ID no.
var TmpStr
var Tmpr
var vekt1 = "376189452"
var vekt2 = "5432765432"
var tot = 0
var base = 11
var value
var vekt
var TmpStr2
var Tmpr2
var vekt2 = "5432765432"
var tot2= 0
var base = 11

// for first number
for (i=0;i<9 ;i++)
{
value = ID.substr(i,1)
vekt = vekt1.substr(i,1)
tot = tot + (parseInt(value) * parseInt(vekt))
}
i=tot/11
i=tot-(parseInt(i)*11)
i=11-i
TmpStr = ID
TmpStr = ID.substr(9,1)
Tmpr=(ID.length)

// for second number
for (y = 0; y < 10 ; y++)
{
value = ID.substr(y,1)
vekt = vekt2.substr(y,1)
tot2 = tot2 + (parseInt(value) * parseInt(vekt))
}
y=tot2/11
y=tot2-(parseInt(y)*11)
y=11-y
TmpStr2 = ID
TmpStr2 = ID.substr(10,1)
Tmpr2=(ID.length)

// integer check

if (base!=Tmpr)
{
document.write("You must enter 11 number for ID (0 - 9) ")
}
else if ( parseInt(i)==TmpStr && parseInt(y)==TmpStr2)
{
document.write("Thanks for right ID no.")
}
else
{
document.write("Wrong ID no.")
}

}

</script>
</body>
</html>

best regards,
 
A

Albert D. Kallal

Are you looking to "test",or verify the existing 3000 numbers, or
are you looking to verify data during input into a form???


the code could be something like:

Public Function ValidateID(id As String) As Boolean

Const vekt1 As String = "376189452"
Const vekt2 As String = "5432765432"

Const base As Integer = 11

Dim tot As Long
Dim tot2 As Long


Dim i As Integer
Dim y As Integer
Dim intPtr1 As Integer
Dim intPtr2 As Integer

Stop

If Len(id) <> 11 Then
MsgBox "You must enter 11 number for id (0-9)", vbExclamation, "11
digits"
Exit Function
End If

' for first number

For i = 1 To 9
tot = tot + (Mid(id, i, 1) * Mid(vekt1, i, 1))
Next i

intPtr1 = tot / 11
intPtr1 = tot - (intPtr1 * 11)
intPtr1 = 11 - intPtr1

For i = 1 To 10
tot2 = tot2 + (Mid(id, i, 1) * Mid(vekt2, i, 1))
Next i

intPtr2 = tot2 / 11
intPtr2 = tot2 - (intPtr2 * 11)
intPtr2 = 11 - intPtr2

If Len(id) <> 11 Then
MsgBox "You must enter 11 number for id (0-9)", vbExclamation, "11
digits"
Else
If (intPtr1 = Mid(id, 10, 1)) And (intPtr2 = Mid(id, 11, 1)) Then

' ok
ValidateID = True
Else
MsgBox "wrong ID no", vbExclamation, "Bad number"
End If
End If


End Function

I just not up to speed on java...for example, you have:

TmpStr = ID
TmpStr = ID.substr(9,1)
Tmpr=(ID.length)

Any reason the above is not written as:

TmpStr = ID.substr(9,1)
Tmpr=(ID.length)

(is the first assignment for casting the data type perhaps????).

Also, I have assumed that id.substr(9,1) actually means to get the 10
character (ie: java is zero based in these cases).

Access will convert (cast) a string to a number for you...and my example
code uses this.

As to "how" to use the above code, that will depend if you are looking to
test/find bad numbers in a file, or are looking for data entry.

I also don't have any numbers to "test" my routine, but trying it with
"22037035991" did not work....
 
K

Kamila

Thanks for detail reply.
First i will use this forumla to check and verify existing 3000 numbers and
find out wrong numbers for me after that i will use it during input from a
form.

These are correct ID Numbers
06084718767
05056021762
10065032304
02077539088

This number is also correct "22037035991"

These are wrong ID Numbers
31074227037
09055119719
30468331842
15196221735

Where i will write this function or code in the access?
 
K

Kamila

Thanks for your time and reply. Actually i totaly new with Access programing
and i dont know wher can i put.
My table name is "UserId" and it contain only one column "Id" if you give me
one great faviour then you can email one access file at bawafa_dost (@) hotma
il com then i will copy all ID in the Columen Id.

regards,
--
Kamila


Albert D. Kallal said:
Kamila said:
Thanks for detail reply.
First i will use this forumla to check and verify existing 3000 numbers
and
find out wrong numbers for me after that i will use it during input from a
form.
Click to expand...

Ok, so, the above is really two differnt goals, or tasks...., but, we can
write a rouinte that will be usefull for both...
These are correct ID Numbers
06084718767
05056021762
10065032304
02077539088

This number is also correct "22037035991"
Click to expand...

Atually, I just cut/pasted the above numbers...I found with the routine as
given:
These are correct ID Numbers
06084718767 - true
05056021762 - false
10065032304 - false
02077539088 - true

This number is also correct "22037035991" -- true
Click to expand...
These are wrong ID Numbers
Click to expand...

The wrong ones where all false...
31074227037
09055119719
30468331842
15196221735

Where i will write this function or code in the access?
Click to expand...

You place your code in a code module. Thus, instert a standard module...give
that modlue a name (that name must be differnt then the code or function
name that we will palce inside of this code module).

Since we want to use this for a "test" of all existing numbers, we need to
remove out "prompts" etc from the code. We can write some sepeate code that
calls our custom function to give out the error messages in our form.

ok...so, here is our code:

Public Function ValidateID(ID As String) As Boolean

Const vekt1 As String = "376189452"
Const vekt2 As String = "5432765432"

Dim tot As Long
Dim tot2 As Long

Dim i As Integer

Dim intPtr1 As Integer
Dim intPtr2 As Integer

If Len(ID) <> 11 Then
Exit Function ' false
End If

' for first number
For i = 1 To 9
tot = tot + (Mid(ID, i, 1) * Mid(vekt1, i, 1))
Next i

intPtr1 = 11 - (tot - (Int(tot / 11) * 11))
intPtr1 = Left(intPtr1, 1)

' for 2nd number
For i = 1 To 10
tot2 = tot2 + (Mid(ID, i, 1) * Mid(vekt2, i, 1))
Next i

intPtr2 = 11 - (tot2 - (Int(tot2 / 11) * 11))
intPtr2 = Left(intPtr2, 1)

If (intPtr1 = Mid(ID, 10, 1)) And (intPtr2 = Mid(ID, 11, 1)) Then
' ok
ValidateID = True
End If

End Function

save the module...clsoe it...

Now, we can simply use the sql query builder, and drop in a bunch of fiields
into the table. (I don't know the name of your table, but, lets just assume
you fired up the query builder...dropped in your table wth the data..and
hten simply drag and dropped seveal fields into the query grid..


id TestDate user MyValID

We then add a 5th collum in the query builder, but this collum will be based
on our code fruncton (this is one those really wonderful and amzing features
that makes ms-accss so power - that feature bing you can use custom functons
in your sql).

type in:

GoodNumber:ValidateID([MyValID])

So, our query grid now has:

id TestDate user MyValID GoodNumber:ValidateID([MyValID])

You can same this query. Now, if you open the query, you see all your
collums of data, and a true, or false for the GoodNumber colluum.

You can even flip the query back into desint mode, and in the critera box,
somply type in true for the GoodNumber collum. Now, when you view the query,
it will ONLY return good numbers. And, you can also do this for "false",and
then simply print out the query to your pritner...or do whatever.....
(really smple..and really amzing that once we written our code...we can use
that for reports, quiers...etc).

The validation code in our form is really simple, and you would use the
contorls "before update" event, and simply go:


if len(me.MyIdTextBox) <> 11 then
msgbox "must be 11 characters long"
cancel = true
end if

' now test....

if ValidateID(me.MyIDTextBox) = false then
msgbox "bad id"
Cancel = true
end if


That is it.....
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

challenge! javascript function into excel function 2
Javascript funchtion converting to Excel 7
Print page button 3
Include a clock in the top header of the next version of Outlook. 1
Can one include data from javascript in form 1
newbie trying to use data access web page (Access 2003) 0
looks different in firefox then explorer 1
Data Access Page Help 1

Top