Access Queries

[AndrewKent]

Hi folks,

Was wondering if anyone could help with a couple of issues I have passing data from Access to Excel. Firstly, I have written a function in Access VBA that allows me to return the maximum value from several fields for any given record. Now, I can extract data from Access to Excel by adding the SQL for my query to the VBA procedure in Excel.

The query won't work properly as the function is in Access, and I can't put the function into Excel as it won't work.

I want to know therefore is there a way to extract data from Access to Excel by "calling" a pre-built query. I also want to pass variables into this query.

Is this possible?

Andy

 

[AndrewKent]

Building from this, this is the SQL query I want to run in Excel VBA to import data...

    If strQueryType = "PredictionPlus_Database" Then
        strQuery = "SELECT tblData_MatchData.Data_MatchID AS [Match ID], tblData_MatchData.Data_Season AS Season, tblDefinitions_Division.Division_DivisionName AS " _
        & "Division, tblData_MatchData.Data_Date AS [Date], tblData_MatchData.Data_HomeTeam AS [Home Team], tblData_MatchData.Data_AwayTeam AS [Away Team], " _
        & "tblData_MatchData.Data_FTHG AS HGF, tblData_MatchData.Data_FTAG AS AGF, tblData_MatchData.Data_FTR AS FTR, " _
        & "Maximum([Data_B365H],[Data_BSH],[Data_BWH],[Data_GBH],[Data_IWH],[Data_LBH],[Data_SBH],[Data_SJH],[Data_VCH],[Data_WHH]) AS [Max HO], " _
        & "Maximum([Data_B365D],[Data_BSD],[Data_BWD],[Data_GBD],[Data_IWD],[Data_LBD],[Data_SBD],[Data_SJD],[Data_VCD],[Data_WHD]) AS [Max DO], " _
        & "Maximum([Data_B365A],[Data_BSA],[Data_BWA],[Data_GBA],[Data_IWA],[Data_LBA],[Data_SBA],[Data_SJA],[Data_VCA],[Data_WHA]) AS [Max AH], " _
        & "tblData_MatchData.Data_HC AS HC, tblData_MatchData.Data_HST AS HST, tblData_MatchData.Data_HS AS HS, tblData_MatchData.Data_AC AS AC, " _
        & "tblData_MatchData.Data_AST AS AST, tblData_MatchData.Data_AS AS [AS] FROM tblDefinitions_Division INNER JOIN tblData_MatchData ON " _
        & "tblDefinitions_Division.Division_Code = tblData_MatchData.Data_Division WHERE (((tblData_MatchData.Data_Season) = '" & strSeason & "') And " _
        & "((tblDefinitions_Division.Division_DivisionName) = '" & strDivision & "')) ORDER BY tblData_MatchData.Data_Date;"
        Exit Sub
    End If

but "Maximum" is a function in Access:

Function Maximum(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 largest.
     
    For I = 0 To UBound(FieldArray)
        If FieldArray(I) > currentVal Then
            currentVal = FieldArray(I)
        End If
    Next I
     
    ' Return the maximum value found.
    Maximum = currentVal
 
End Function

How do I run my routine in Excel AND call that function in Access?

Andy

 

[RoryA]

You can't. You would have to output the query results in Access into a table and then query that table from Excel.

 

[AndrewKent]

Could I do this with an IIF expression?

For example IIF Field1 > Field2, display Field1 else Field2?

 

[RoryA]

Yes, but it would get very complicated if you have more than two fields!