Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    max and min (Access 2000 VBA)

    I have a control called txtSampleNumber in a report. In the open event I have the following code.

    sqlstr = "SELECT * FROM [qryReport] WHERE [SamplePickedUp] = " & Forms![frmMain]!txtSamplePickedUp & ""
    Set str = db.OpenRecordset(sqlstr)

    The select statement should return 1, 2 or 6 records in column SampleNumber of the qryReport.

    If one record is returned I need to txtSampleNumber = str![SampleNumber]
    If multiple records are returned I need to have txtSampleNumber = Min(str![SampleNumber]) & Max(str![SampleNumber])

    SampleNumber is a string field with numbers like GO-2005-1000

    Thanks,

    Deni

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: max and min (Access 2000 VBA)

    Try this (it doesn't open a recordset, but uses functions such as DCount):

    Dim strWhere As String
    Dim lngCount As Long

    strWhere = "[SamplePickedUp] = " & Forms![frmMain]!txtSamplePickedUp
    lngCount = DCount("SampleNumber", "qryReport", strWhere)
    If lngCount = 1 Then
    txtSampleNumber = DLookup("SampleNumber", "qryReport", strWhere)
    Else
    txtSampleNumber = DMin("SampleNumber", "qryReport", strWhere) & " - " & _
    DMax("SampleNumber", "qryReport", strWhere)
    End If

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: max and min (Access 2000 VBA)

    Hans,

    Do if have to format the Forms![frmMain]!txtSamplePickedUp (short) date to the same format as the query (long mmmm dd, yyyy).

    lngCount is returning zero

    Deni

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: max and min (Access 2000 VBA)

    If it is a date field, change it to

    strWhere = "[SamplePickedUp] = #" & Forms![frmMain]!txtSamplePickedUp & "#"

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: max and min (Access 2000 VBA)

    Now I get an error that I can't assign a value to this object.

    Should this code be in the report_open or reportheader_format?

    Thanks for the help

  6. #6
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: max and min (Access 2000 VBA)

    Report PageHeader Format worked.

    Thanks Hans.

Posting Permissions

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