IIf([N/I]='N','YES')
IIf([N/I]='N',[N/I],'YES')
IIf([N/I]='N','NO','YES')
SELECT AGE, Count(IIf([N/I]='N',[N/I],'YES')) AS Non, Count(IIf([N/I]='I',[N/I],'YES')) AS Inst, Count(IIf([N/I]='O',[N/I],'YES')) AS Outpt, Count(ICN) AS Total, 'Tdefic' AS CONTRACT, '6/3/2014 7:02:56 AM into FROM tbl_allexcel INNER JOIN Tdefic_LOCS ON tbl_allexcel.LOC = Tdefic_LOCS.LOC WHERE ((((tbl_allexcel.[LOC]) = [Forms]![frm_Main]![combolocation]) AND (Tdefic_LOCS.DEPT) =CLMS RES)) GROUP BY AGE ORDER BY AGE DESC;
Option Compare Database
Option Explicit
'vars set in load
Dim currHRes As Long
Dim currVRes As Long
Dim currBPP As Long
'var set in mnuModes
Dim currMenuItem As Long
'array of valid resolutions & colour depths
Dim resArray() As Long
'const for the members of the array
'i.e.resArray(resWidth, Index) = 1024
'i.e.resArray(resHeight, Index) = 768
'i.e.resArray(resDepth, Index) = 16 'Bits per pixel
Const resWidth = 1
Const resHeight = 2
Const resDepth = 3
Private Sub Form_Load()
' retrieves the current screen resolution for
' later comparison against DEVMODE values in
' CompareSettings.
currHRes = GetDeviceCaps(hdc, HORZRES)
currVRes = GetDeviceCaps(hdc, VERTRES)
currBPP = GetDeviceCaps(hdc, BITSPIXEL)
Dim maxItems As Long
InitializeDisplayMenu maxItems
FinalizeDisplayMenu maxItems
End Sub
Private Sub FinalizeDisplayMenu(maxItems As Long)
' This adds a separator and a final menu item,
' providing the ability to open the control panel
' display settings page from the app.
If maxItems > 0 Then
Dim hMenu As Long
Dim r As Long
' add the separator
maxItems = maxItems + 1
Load mnuModes(maxItems)
mnuModes(maxItems).Caption = "-"
' add the final item
maxItems = maxItems + 1
Load mnuModes(maxItems)
mnuModes(maxItems).Caption = "Show Display Settings"
' finally, bold the newly-added menuitem
hMenu = GetSubMenu(GetMenu(Me.hWnd), 0)
Call SetMenuDefaultItem(hMenu, maxItems - 1, True)
End If
End Sub
Private Sub InitializeDisplayMenu(maxItems As Long)
Dim DM As DEVMODE
Dim dMode As Long
' 36 should be enough to hold your settings.
' It 's trimmed back at the end of this routine.
ReDim resArray(1 To 3, 0 To 35)
' set the DEVMODE flags and structure size
DM.dmFields = DM_PELSWIDTH Or DM_PELSHEIGHT Or DM_BITSPERPEL
DM.dmSize = LenB(DM)
' The first mode is 0
dMode = 0
' call the API to retrieve the values for the
specified dMode
Do While EnumDisplaySettings(0&, dMode, DM) > 0
' if the BitsPerPixel is greater than 4
' (16 colours), then add the item to a menu
If DM.dmBitsPerPel >= 4 Then
Call MenuAdd(DM, resArray(), maxItems)
End If
' increment and call again. Continue until
' EnumDisplaySettings returns 0 (no more settings)
dMode = dMode + 1
Loop
' trim back the resArray to fit the number of actual entries.
ReDim Preserve resArray(1 To 3, 0 To maxItems)
End Sub
Private Function CompareSettings(DM As DEVMODE) As Long
' compares the current screen resolution with
' the current DEVMODE values. Returns TRUE if
' the horizontal and vertical resolutions, and
' the bits per pixel colour depth, are the same.
CompareSettings = (DM.dmBitsPerPel = currBPP) And _
DM.dmPelsHeight = currVRes And _
DM.dmPelsWidth = currHRes
End Function
Private Sub MenuAdd(DM As DEVMODE, resArray() As Long, mnuCount As Long)
Dim mType As String
' used to determine when the colour depth has
' changed, so we can add a separator to the menu.
Static lastBitsPerPel As Long
' select the appropriate text string based on
' the colour depth
Select Case DM.dmBitsPerPel
Case 4: mType = "16 Color"
Case 8: mType = "256 Color"
Case 16: mType = "High Color"
Case 24, 32: mType = "True Color"
End Select
' if this is the first item, we can't load the menu
' array item, and it will not require a separator.
If mnuCount > 0 Then
' load a new menu item to the array
Load mnuModes(mnuCount)
' determine if the colour depth has changed. If so,
' make the caption a separator, and load a new item
' to hold the item.
If lastBitsPerPel <> DM.dmBitsPerPel Then
mnuModes(mnuCount).Caption = "-"
mnuCount = mnuCount + 1
Load mnuModes(mnuCount)
End If
End If
' create the menu caption
mnuModes(mnuCount).Caption = DM.dmPelsWidth & "x" & _
DM.dmPelsHeight & " [" & _
DM.dmBitsPerPel & " bit " & _
mType & "]"
' see if this is the current resolution,
' and if so, check the menu item
mnuModes(mnuCount).Checked = CompareSettings(DM)
If mnuModes(mnuCount).Checked Then currMenuItem = mnuCount
resArray(resWidth, mnuCount) = DM.dmPelsWidth
resArray(resHeight, mnuCount) = DM.dmPelsHeight
resArray(resDepth, mnuCount) = DM.dmBitsPerPel
' save the current DEVMODE value for depth
' and increment the menu item count, ready for
' the next call
lastBitsPerPel = DM.dmBitsPerPel
mnuCount = mnuCount + 1
End Sub
Private Sub Command1_Click()
Dim maxItems As Long
InitializeDisplayMenu maxItems
Command1.Enabled = False
FinalizeDisplayMenu maxItems
End Sub
Private Sub mnuModes_Click(Index As Integer)
Dim DM As DEVMODE
Select Case Index
Case mnuModes.Count
' show the display control panel
Call Shell("rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,3", 1)
Case Else
' change the current resolution, no prompting
' BE CAREFUL .. you could set your system to a
' setting which renders the display difficult to read.
With DM
.dmPelsWidth = resArray(resWidth, Index)
.dmPelsHeight = resArray(resHeight, Index)
.dmBitsPerPel = resArray(resDepth, Index)
.dmFields = DM_PELSWIDTH Or DM_PELSHEIGHT Or DM_BITSPERPEL
.dmSize = LenB(DM)
End With
If ChangeDisplaySettings(DM, CDS_FORCE) <> 0 Then
MsgBox "Error! Perhaps your hardware is not up to the task?"
End If
' indicate the current menu selection
mnuModes(currMenuItem).Checked = False
mnuModes(Index).Checked = True
currMenuItem = Index
End Select
End Sub
DoCmd.RunSQL (sql_criteria)
Call DoCmd.RunSQL(sql_criteria)
DoCmd.RunSQL sql_criteria
Comment