User Defined Functions 
Author Message
 User Defined Functions

I'm an experienced Access user but have never created a
user defined function using VBA.

Am using ODBC to download a list of drivers who work for
our company and am comparing that list with info provided
by an outside co.  The key field between my file and the
outside co. file is the driver's SSN.  The outside co.
stores the SSN in an 11 character text field with the
dashes ("-") in the field.  Our internal file stores the
drivers SSN in a numeric field.  When the SSN is stored in
our file the leading zeros are truncated.  When I download
the data I store it in an 11 character text field and am
wanting to add the previously truncated zeros.  I am using
an update query that calls the "SSN" field from the
downloaded file and then attempts to add truncated zeros,
insert the missing dashes ("-") and put the field back
into the same "SSN" field.

I'm getting a runtime error '424' when I execute the query
and the code line "SSN = Table![Driver File Data]!DRSS" is
highlighted in yellow.  I'm not sure why Access doesn't
like how I am referring to the "DRSS" field.  Any ideas?

The following is the balance of the function code,

Function AddZeros()
'Social Security Numbers are stored in a numeric
'field within the Drivers File.  Numeric fields
'suppress leading zeros which causes a problem
'for this application.  This function adds miss-
'ing leading zeros.

    Dim cnt As Integer
    Dim lnth As Integer
    Dim SSN As String

    SSN = Table![Driver File Data]!DRSS
    lnth = Len(SSN)
    cnt = 9 - lnth

        Do While cnt > 0

            SSN = "0" & SSN
            cnt = cnt - 1

        Loop

    Table![Driver File Data]!DRSS = SSN

End Function



Sun, 20 Nov 2005 01:32:22 GMT  
 User Defined Functions
Use:

new_field_name: format$([field_name],"000000000")

In a query instead.

P


Quote:
> I'm an experienced Access user but have never created a
> user defined function using VBA.

> Am using ODBC to download a list of drivers who work for
> our company and am comparing that list with info provided
> by an outside co.  The key field between my file and the
> outside co. file is the driver's SSN.  The outside co.
> stores the SSN in an 11 character text field with the
> dashes ("-") in the field.  Our internal file stores the
> drivers SSN in a numeric field.  When the SSN is stored in
> our file the leading zeros are truncated.  When I download
> the data I store it in an 11 character text field and am
> wanting to add the previously truncated zeros.  I am using
> an update query that calls the "SSN" field from the
> downloaded file and then attempts to add truncated zeros,
> insert the missing dashes ("-") and put the field back
> into the same "SSN" field.

> I'm getting a runtime error '424' when I execute the query
> and the code line "SSN = Table![Driver File Data]!DRSS" is
> highlighted in yellow.  I'm not sure why Access doesn't
> like how I am referring to the "DRSS" field.  Any ideas?

> The following is the balance of the function code,

> Function AddZeros()
> 'Social Security Numbers are stored in a numeric
> 'field within the Drivers File.  Numeric fields
> 'suppress leading zeros which causes a problem
> 'for this application.  This function adds miss-
> 'ing leading zeros.

>     Dim cnt As Integer
>     Dim lnth As Integer
>     Dim SSN As String

>     SSN = Table![Driver File Data]!DRSS
>     lnth = Len(SSN)
>     cnt = 9 - lnth

>         Do While cnt > 0

>             SSN = "0" & SSN
>             cnt = cnt - 1

>         Loop

>     Table![Driver File Data]!DRSS = SSN

> End Function



Sun, 20 Nov 2005 01:48:13 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Functions - User Defined Functions

2. Handling Nulls in User-Defined Functions

3. Pass ParamArray to User Defined Function

4. User defined function not recognized

5. User-defined functions suddenly aren't recognized

6. User-defined Function Procedure in Access Query

7. Criteria using a User-Defined function

8. HELP!! ASP, Access 97 and user defined functions

9. A97...User defined function not working in recordset

10. beginner question : user-defined function

11. Finding user-defined-functions

12. Enumeration in User-defined Functions

 

 
Powered by phpBB® Forum Software