Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Perth WA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display results of Access SQL Max Function in a form

    Hi,
    I have a form with 3 fields, one from each of three linked tables: table1.group, table2.trial, table3.Measurement. I am attempting to use a bit of vba to modify the forms record source: The query uses a value grabbed from a combo box to fish out the required records:

    Private Sub cboGroup_AfterUpdate()
    Dim StrSQL As String

    StrSQL = "SELECT Distinct Table1.Group, Table2.Trial, Max(Table3.Measurement) AS MaxMeasurement " _
    & "FROM Table1 INNER JOIN (Table2 INNER JOIN Table3 ON Table2.Genotype = Table3.Genotype) ON Table1.Genotype = Table2.Parent GROUP BY " _
    & "Table1.Group, Table2.Trial & "HAVING Table1.Group = " & "'" & cboGroup & "'"

    Me.RecordSource = StrSQL

    The first two fields work fine, but I get #NAME? in the measurement field on the form.

    How can i get the maxMeasurement value to display on the form?

    Thanks in advance

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Perth WA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok got it sorted
    Solution is to put a textbox on the form and type =[MaxMeasurement] in the control source

Posting Permissions

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