+ Reply to Thread
Results 1 to 2 of 2

Query a Access database that has a module from Excel

  1. #1
    Oggie
    Guest

    Query a Access database that has a module from Excel

    I'm very new to most SQL, VB, etc. and I think this is pretty simple so I
    hope someone can help!!!

    I have a database that I store all of my research data in, then I query it
    in Excel to do analysis. I've put in a function that I found from the
    Microsoft KnowledgeBase to find the minimum value across various fields in
    ONE record. However, when I try to query the database now it gives me a
    "Undefined function in expression" message.

    I've read around and now realize that the modules aren't part of the
    Microsoft Jet Database (or something like that!!) and thus cannot be seen.
    I've also read that the likely solution is to use SQL to do the same thing,
    unfortunately I'm having trouble finding any resource that will explain SQL
    at my level!!!!

    Can anyone help me write the same function in SQL??? Below is the code that
    I was using. Thank you so much in advance.

    Oggie

    Function Minimum(ParamArray FieldArray() As Variant)
    ' Declare the two local variables.
    Dim I As Integer
    Dim currentVal As Variant

    ' Set the variable currentVal equal to the array of values.
    currentVal = FieldArray(0)

    ' Cycle through each value from the row to find the smallest.
    For I = 0 To UBound(FieldArray)
    If FieldArray(I) < currentVal Then
    currentVal = FieldArray(I)
    End If
    Next I

    ' Return the minimum value found.
    Minimum = currentVal

    End Function

  2. #2
    onedaywhen
    Guest

    Re: Query a Access database that has a module from Excel


    Oggie wrote:
    > I'm very new to most SQL, VB, etc. and I think this is pretty simple

    so I
    > hope someone can help!!!
    >
    > I have a database that I store all of my research data in, then I

    query it
    > in Excel to do analysis. I've put in a function that I found from

    the
    > Microsoft KnowledgeBase to find the minimum value across various

    fields in
    > ONE record. However, when I try to query the database now it gives

    me a
    > "Undefined function in expression" message.
    >
    > I've read around and now realize that the modules aren't part of the
    > Microsoft Jet Database (or something like that!!) and thus cannot be

    seen.
    > I've also read that the likely solution is to use SQL to do the same

    thing,
    > unfortunately I'm having trouble finding any resource that will

    explain SQL
    > at my level!!!!
    >
    > Can anyone help me write the same function in SQL??? Below is the

    code that
    > I was using. Thank you so much in advance.
    >
    > Oggie
    >
    > Function Minimum(ParamArray FieldArray() As Variant)
    > ' Declare the two local variables.
    > Dim I As Integer
    > Dim currentVal As Variant
    >
    > ' Set the variable currentVal equal to the array of values.
    > currentVal = FieldArray(0)
    >
    > ' Cycle through each value from the row to find the smallest.
    > For I = 0 To UBound(FieldArray)
    > If FieldArray(I) < currentVal Then
    > currentVal = FieldArray(I)
    > End If
    > Next I
    >
    > ' Return the minimum value found.
    > Minimum = currentVal
    >
    > End Function


    All implementations of SQL should have a MIN (minimum) set function
    e.g.

    SELECT MIN(MyCol) AS min_value FROM MyTable;

    Jamie.

    --


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1