Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to convert code into a Query? 1

Status
Not open for further replies.
robojeff

robojeff

Technical User
Dec 5, 2008
220
US
I have the following code which works well to create a checksum of a string of data when I insert a number into an edit box on my form, but I have a whole table of these numbers that I need to create checksums for and need to know if this can be done with a query or if I can modify this code to create this new checksum for all the strings that I have in the table.

Here is my code:

Code: 
Dim x As Integer
Dim y As Integer
Dim HIBC As String
Dim partnum As String
Dim totals As Integer
Dim slen As Integer
Dim digit As String
Dim HIBCvals(43) As String
Dim remaindr As Integer


HIBCvals(0) = "0"
HIBCvals(1) = "1"
HIBCvals(2) = "2"
HIBCvals(3) = "3"
HIBCvals(4) = "4"
HIBCvals(5) = "5"
HIBCvals(6) = "6"
HIBCvals(7) = "7"
HIBCvals(8) = "8"
HIBCvals(9) = "9"
HIBCvals(10) = "A"
HIBCvals(11) = "B"
HIBCvals(12) = "C"
HIBCvals(13) = "D"
HIBCvals(14) = "E"
HIBCvals(15) = "F"
HIBCvals(16) = "G"
HIBCvals(17) = "H"
HIBCvals(18) = "I"
HIBCvals(19) = "J"
HIBCvals(20) = "K"
HIBCvals(21) = "L"
HIBCvals(22) = "M"
HIBCvals(23) = "N"
HIBCvals(24) = "O"
HIBCvals(25) = "P"
HIBCvals(26) = "Q"
HIBCvals(27) = "R"
HIBCvals(28) = "S"
HIBCvals(29) = "T"
HIBCvals(30) = "U"
HIBCvals(31) = "V"
HIBCvals(32) = "W"
HIBCvals(33) = "X"
HIBCvals(34) = "Y"
HIBCvals(35) = "Z"
HIBCvals(36) = "-"
HIBCvals(37) = "."
HIBCvals(38) = " "
HIBCvals(39) = "$"
HIBCvals(40) = "/"
HIBCvals(41) = "+"
HIBCvals(42) = "%"

Me.pnumb = Replace(Me.pnumb, "-", "")
partnum = Me.pnumb

totals = 88                     ' 88 = +m25822
slen = Len(partnum)
For x = 1 To slen Step 1
    digit = Mid(partnum, x, 1)
    For y = 0 To 42 Step 1
       If HIBCvals(y) = digit Then
            totals = totals + y
            y = 42
       End If
    Next y
Next x

remaindr = totals Mod 43
digit = HIBCvals(remaindr)


HIBC = "+M25855" & partnum & "0" & digit
Me.HIBC = HIBC
 
Sort by date Sort by votes
Select getHIBC([partNum]) as HIBC from someTable ...

Code: 
Public Function getHIBC(partnum As Variant) As String
 Dim x As Integer
 Dim y As Integer
 Dim HIBC As String
 Dim partnum As String
 Dim totals As Integer
 Dim slen As Integer
 Dim digit As String
 Dim HIBCvals(43) As String
 Dim remaindr As Integer


HIBCvals(0) = "0"
HIBCvals(1) = "1"
HIBCvals(2) = "2"
HIBCvals(3) = "3"
HIBCvals(4) = "4"
HIBCvals(5) = "5"
HIBCvals(6) = "6"
HIBCvals(7) = "7"
HIBCvals(8) = "8"
HIBCvals(9) = "9"
HIBCvals(10) = "A"
HIBCvals(11) = "B"
HIBCvals(12) = "C"
HIBCvals(13) = "D"
HIBCvals(14) = "E"
HIBCvals(15) = "F"
HIBCvals(16) = "G"
HIBCvals(17) = "H"
HIBCvals(18) = "I"
HIBCvals(19) = "J"
HIBCvals(20) = "K"
HIBCvals(21) = "L"
HIBCvals(22) = "M"
HIBCvals(23) = "N"
HIBCvals(24) = "O"
HIBCvals(25) = "P"
HIBCvals(26) = "Q"
HIBCvals(27) = "R"
HIBCvals(28) = "S"
HIBCvals(29) = "T"
HIBCvals(30) = "U"
HIBCvals(31) = "V"
HIBCvals(32) = "W"
HIBCvals(33) = "X"
HIBCvals(34) = "Y"
HIBCvals(35) = "Z"
HIBCvals(36) = "-"
HIBCvals(37) = "."
HIBCvals(38) = " "
HIBCvals(39) = "$"
HIBCvals(40) = "/"
HIBCvals(41) = "+"
HIBCvals(42) = "%"

If Not IsNull(partnum) Then
   partnum = Replace(partnum, "-", "")

   totals = 88                     ' 88 = +m25822
   slen = Len(partnum)
   For x = 1 To slen Step 1
      digit = Mid(partnum, x, 1)
      For y = 0 To 42 Step 1
        If HIBCvals(y) = digit Then
            totals = totals + y
            y = 42
        End If
      Next y
   Next x

   remaindr = totals Mod 43
   digit = HIBCvals(remaindr)
   getHIBC = "+M25855" & partnum & "0" & digit
End If
End Function
 
Upvote 0 Downvote
Thank you MajP-

I am not familiar with this method... How do I incorporate the function in with the query?

 
Upvote 0 Downvote
Assume I have a very simple function that adds the letter M to a string and returns the new string.

public Function addM(someFld as variant) as string
if not isnull(someFld) then
addM = "M"& someFld
end if
end if


to call this function from a query, I pass in the value from the field by placing the field name inside the function. Assume the table is called "tblOne" and the field is "partNum". Then

"Select addM(partNum) as HIBC from tblOne"

addM(partNum): is the field passed into the function
HIBC: is the alias for the new field
 
Upvote 0 Downvote
Thanks MajP-

Your example is easy to follow but I have not created a Public function before in Access. I have created subs under events to controls on forms but not sure how or where to place this public function.

Sorry this is new to me...
 
Upvote 0 Downvote
Thanks

The link you sent shows how to make a new module in access 97 but I can not find this option in access 2007.

Is this the same as create a new macro?

 
Upvote 0 Downvote
I added the public function with my VB code but when I run the query I get the following error message

Undefined function 'getHIBC' in expression
 
Upvote 0 Downvote
Is the function in a standard module (not a form's module)?
Is the function public?
Is the name of the function still getHIBC?

You need to ensure that in a standard module you have a function that looks like

Public Function getHIBC(partnum As Variant) As String
some code here
getHIBC = someValueToReturn
end function
 
Upvote 0 Downvote
The public code shows up in the general area as shown below:
I have also attached a link to a jpg of how it appears...


Code: 
Public Function getHIBC(partnum As Variant) As String
 Dim x As Integer
 Dim y As Integer
 Dim HIBC As String
 Dim partnum As String
 Dim totals As Integer
 Dim slen As Integer
 Dim digit As String
 Dim HIBCvals(43) As String
 Dim remaindr As Integer


HIBCvals(0) = "0"
HIBCvals(1) = "1"
HIBCvals(2) = "2"
HIBCvals(3) = "3"
HIBCvals(4) = "4"
HIBCvals(5) = "5"
HIBCvals(6) = "6"
HIBCvals(7) = "7"
HIBCvals(8) = "8"
HIBCvals(9) = "9"
HIBCvals(10) = "A"
HIBCvals(11) = "B"
HIBCvals(12) = "C"
HIBCvals(13) = "D"
HIBCvals(14) = "E"
HIBCvals(15) = "F"
HIBCvals(16) = "G"
HIBCvals(17) = "H"
HIBCvals(18) = "I"
HIBCvals(19) = "J"
HIBCvals(20) = "K"
HIBCvals(21) = "L"
HIBCvals(22) = "M"
HIBCvals(23) = "N"
HIBCvals(24) = "O"
HIBCvals(25) = "P"
HIBCvals(26) = "Q"
HIBCvals(27) = "R"
HIBCvals(28) = "S"
HIBCvals(29) = "T"
HIBCvals(30) = "U"
HIBCvals(31) = "V"
HIBCvals(32) = "W"
HIBCvals(33) = "X"
HIBCvals(34) = "Y"
HIBCvals(35) = "Z"
HIBCvals(36) = "-"
HIBCvals(37) = "."
HIBCvals(38) = " "
HIBCvals(39) = "$"
HIBCvals(40) = "/"
HIBCvals(41) = "+"
HIBCvals(42) = "%"

If Not IsNull(partnum) Then
   partnum = Replace(partnum, "-", "")

   totals = 88                     ' 88 = +m25822
   slen = Len(partnum)
   For x = 1 To slen Step 1
      digit = Mid(partnum, x, 1)
      For y = 0 To 42 Step 1
        If HIBCvals(y) = digit Then
            totals = totals + y
            y = 42
        End If
      Next y
   Next x

   remaindr = totals Mod 43
   digit = HIBCvals(remaindr)
   getHIBC = "+M25855" & partnum & "0" & digit
End If
End Function

[\code]
[attach_link]http://www.mediafire.com/imageview.php?quickkey=r1ct21mb5134443&thumb=4[/attach_link]
 
Upvote 0 Downvote
Your code is in a form's module. I can tell by the Print_Click event procedure. You need to look at the link provided and put it in a Standard module. It should be the same in 2007 and 97 because the Visual basic editor has not changed much.
 
Upvote 0 Downvote
Thank you MajP-

I appreciate your patience and I have learned a valuable new method with Access!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

C
  • Question
Query expression negative to a 0
Replies
6
Views
370
dhookom
dhookom
serino
  • Locked
  • Question
Help with a Query. 1
Replies
13
Views
872
strongm
strongm
C
  • Question
Run-time error '3075': Syntax error (missing operator) in query expression 'Birthday = and Birthmonth ='.
Replies
3
Views
251
OzFoxy
OzFoxy
btamsgn
  • Locked
  • Question
how to create a crosstab query 1
Replies
1
Views
2K
dhookom
dhookom
BugZap13
  • Locked
  • Question
Need help creating a date from two fields to be used in where clause
Replies
5
Views
570
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top