Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2002
    Posts
    44

    Unanswered: Help with a simple docmd.runsql string.

    I am running this as an onFormat event for a group in a report. I think my syntax is all jacked up, but hopefully it is clear what I am trying to do..

    any assistance is appreciated

    Code:
    Private Sub GroupHeader4_Format(Cancel As Integer, FormatCount As Integer)
    DoCmd.RunSQL ("select distinct dorms.room count(dorms.room) as me![RoomCount.value] where (dorms.group=me![group.value]) and (dorms.unit = me![unit.value]) and (dorms.bldg = me![bldg.value]) from Dorms")
    End Sub

  2. #2
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    I have an idea of what you are trying to do. If you are trying to return values, work with a recordset. It appears you try to update the RoomCount text box.

    Give this a try, it is a start, but you probably need to tinker with it a little.

    dim db as database
    dim rs as recordset
    dim strSQL as string

    strSQL = "SELECT count(dorms.room) FROM Dorms WHERE (dorms.group = " & me![group] & ") and (dorms.unit = " & me![unit] & ") and (dorms.bldg = " & me![bldg.value] & ");"

    set db = currentdb
    set rs = db.openrecordset(strSQL)
    rs.movefirst

    if not rs.eof then
    me![RoomCount] = rs.fields(0)
    endif

    rs.close
    set db = nothing

  3. #3
    Join Date
    Nov 2001
    Posts
    336
    Another approach would be to use DCount() function as a ControlSource for RoomCount textbox.

    =DCount("room","Dorms","(group = " & me![group] & ") and (unit = " & me![unit] & ") and (bldg = " & me![bldg.value] & ")" )

    Igor

  4. #4
    Join Date
    Apr 2002
    Posts
    44
    okay, I follow where you all are going, however, I want to eliminate duplicate record entries from the count (if room 568 is listed 4 times I only want to count it once)
    that is why I was trying to use a "select Distinct" along with count, but I think the only way I am going to pull this off is to run my select, and then do a recordcount on that when I get done...

    unless you know of a better way..

  5. #5
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    in that case I suggest that you create a query with unique records, call it qryDistictRooms and then strSQL looks like this:

    strSQL = "SELECT count(room) FROM qryDistictRooms WHERE (group = " & me![group] & ") and (unit = " & me![unit] & ") and (bldg = " & me![bldg.value] & ");"

    Thanks IGelin for your suggestion..

    Hope this helps..

  6. #6
    Join Date
    Apr 2002
    Posts
    44
    Thank you for all of your help...here is the solution I ended up writing...

    It takes quite a while for it to run (the table has about 7000 records) I was wondering if what I have done is the most efficient way of doing it, or if I have made some gross programming/style errors (I am still new to VB)

    I appreciate any comments/criticism

    Code:
    Private Sub GroupHeader4_Format(Cancel As Integer, FormatCount As Integer)
    Dim RoomTotalRS As DAO.Recordset
    Dim RoomAssgRS As DAO.Recordset
    Dim SQLstr As String
    Dim BedSQLstr As String
    Dim BedSQLstrAssg As String
    SQLstr = "SELECT Distinct Room FROM Dorms WHERE "
    BedSQLstr = ""
    If (Not IsNull(Me!GROUP)) Then
        SQLstr = SQLstr & "(Group = """ & Me!GROUP & """) and "
        BedSQLstr = BedSQLstr & "(Group = """ & Me!GROUP & """) and "
    Else
        SQLstr = SQLstr & "(Group is Null) and "
        BedSQLstr = BedSQLstr & "(Group is Null) and "
    End If
    If (Not IsNull(Me!UNIT)) Then
        SQLstr = SQLstr & "(Unit = """ & Me!UNIT & """) and "
        BedSQLstr = BedSQLstr & "(Unit = """ & Me!UNIT & """) and "
    Else
        SQLstr = SQLstr & "(Unit is Null) and "
        BedSQLstr = BedSQLstr & "(Unit is Null) and "
    End If
    If (Not IsNull(Me!BLDG)) Then
        SQLstr = SQLstr & "(Bldg =""" & Me!BLDG & """)"
        BedSQLstr = BedSQLstr & "(Bldg =""" & Me!BLDG & """)"
    Else
        SQLstr = SQLstr & "(bldg is Null)"
        BedSQLstr = BedSQLstr & "(bldg is Null)"
    End If
    Set RoomTotalRS = CurrentDB.OpenRecordset(SQLstr, dbOpenSnapshot)
    SQLstr = SQLstr & " and ((Rank is not Null) or (first is not null)" & _
        "or ([name last] is not null)) Order by Room"
    BedSQLstrAssg = BedSQLstr & " and ((Rank is not Null) or (first is not null)" & _
        "or ([name last] is not null))"
    Set RoomAssgRS = CurrentDB.OpenRecordset(SQLstr, dbOpenSnapshot)
    RoomCount = RoomAssgRS.RecordCount & "/" & RoomTotalRS.RecordCount
    RoomTotalRS.MoveFirst
    Dim CurrentRoom As Integer
    Dim BedTotalDetailRS As DAO.Recordset
    Dim BedAssgDetailRS As DAO.Recordset
    Dim BedSQLstrFront As String
    Dim BedDetail As String
    While Not RoomTotalRS.EOF
        CurrentRoom = RoomTotalRS.Fields(0)
        BedDetail = BedDetail & CurrentRoom & " - "
        BedSQLstrFront = "SELECT COUNT (Bed) FROM Dorms WHERE (room = " & CurrentRoom & ") and "
        Set BedTotalDetailRS = CurrentDB.OpenRecordset(BedSQLstrFront & BedSQLstr, dbOpenSnapshot)
        Set BedAssgDetailRS = CurrentDB.OpenRecordset(BedSQLstrFront & BedSQLstrAssg, dbOpenSnapshot)
        If Not BedAssgDetailRS.EOF Then
            BedDetail = BedDetail & BedAssgDetailRS.Fields(0) & "/" & _
                BedTotalDetailRS.Fields(0) & vbNewLine
        Else
            BedDetail = BedDetail & "0/" & BedTotalDetailRS.Fields(0) & vbNewLine
        End If
        RoomTotalRS.MoveNext
        
    Wend
    RoomDetail = BedDetail
    Set BedTotalDetailRS = Nothing
    Set BedAssgDetailRS = Nothing
    Set RoomTotalRS = Nothing
    Set RoomAssgRS = Nothing
    End Sub

Posting Permissions

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