Vba between Filter (1 Viewer)

[Minty]

Could you upload a stripped down version of your database?
Can you also include an example of the output format you are expecting.
Your query strings are complex and I'm sure they can be simplified, but it's much easier to play with actual data.

 

[giddyhead]

Thanks. Please give me a few.

 

[giddyhead]

Sir/Ma'am,
This email is to notify you that they are currently 3 Personnel that Checked out Items between « 2016-11 » And « 2016-12 » in the HR Department .
The Following are the break down in Numbers:
Reporting: 3
24 Months: 0
Biennial: 0
36 Month: 0
48 Month: 0
60 Month: 0
72 Month: 0

The Following are the break down in Personnel: Mr. Test & vbcr & Ms. Test & vbcr & Dr. Lee all have checked out items.
If any questions please let me know.
Thank you in advance for your support in this matter.
Thank you for coming..

On the files Admin DB.zx01.zip Please remove the extension .zip and on Admin DB.zipx.zip please remove the zip extension.

 

[giddyhead]

Let me know if additional information is needed.

 

[giddyhead]

The Following is the code I came up with however when ran I get a Run-Time error '3075' Syntax error(missing operator)in query expressing "Where Recordset = Admin'. Request your assist thanks.

strSQL2 = "SELECT ItmsCheckOutbyMonthtbl.Office, ItmsCheckOutbyMonthtbl.Title, ItmsCheckOutbyMonthtbl.Name, Count(ItmsCheckOutbyMonthtbl.Office) AS [# of Personnel], Sum(IIf([Reason]=""Reporting"",""1"",""0"")) AS Reporting, Sum(IIf([Reason]=""24 Month"",""1"",""0"")) AS [24 Month], Sum(IIf([Reason]=Left([reason],8)=""Biennial"",""1"",""0"")) AS Biennial, Sum(IIf([Reason]=""36 Month"",""1"",""0"")) AS [36 Month], Sum(IIf([Reason]=""48 Month"",""1"",""0"")) AS [48 Month], Sum(IIf([Reason]=""60 Month"",""1"",""0"")) AS [60 Month], Sum(IIf([Reason]=""72 Month"",""1"",""0"")) AS [72 Month], Sum(IIf([Required Date]=""2016-12"",""1"",""0"")) AS [2016-12], Sum(IIf([Required Date]=""2017-01"",""1"",""0"")) AS [2017-01], Sum(IIf([Required Date]=""2017-02"",""1"",""0"")) AS [2017-02], Sum(IIf([Required Date]=""2017-03"",""1"",""0"")) AS [2017-03], Sum(IIf([Required Date]=""2017-04"",""1"",""0"")) AS [2017-04], Sum(IIf([Required Date]=""2017-05"",""1"",""0"")) AS [2017-05], " & _
"Sum(IIf([Required Date]=""2017-06"",""1"",""0"")) AS [2017-06], Sum(IIf([Required Date]=""2017-07"",""1"",""0"")) AS [2017-07], Sum(IIf([Required Date]=""2017-08"",""1"",""0"")) AS [2017-08], Sum(IIf([Required Date]=""2017-09"",""1"",""0"")) AS [2017-09], Sum(IIf([Required Date]=""2017-10"",""1"",""0"")) AS [2017-10], Sum(IIf([Required Date]=""2017-11"",""1"",""0"")) AS [2017-11], Sum(IIf([Required Date]=""2017-12"",""1"",""0"")) AS [2017-12], Sum(IIf([Required Date]=""2018-01"",""1"",""0"")) AS [2018-01], Sum(IIf([Required Date]=""2018-02"",""1"",""0"")) AS [2018-02], Sum(IIf([Required Date]=""2018-03"",""1"",""0"")) AS [2018-03], Sum(IIf([Required Date]=""2018-04"",""1"",""0"")) AS [2018-04], Sum(IIf([Required Date]=""2018-05"",""1"",""0"")) AS [2018-05], " & _
"Sum(IIf([Required Date]=""2018-06"",""1"",""0"")) AS [2018-06], Sum(IIf([Required Date]=""2018-07"",""1"",""0"")) AS [2018-07], Sum(IIf([Required Date]=""2018-08"",""1"",""0"")) AS [2018-08], Sum(IIf([Required Date]=""2018-09"",""1"",""0"")) AS [2018-09], Sum(IIf([Required Date]=""2018-10"",""1"",""0"")) AS [2018-10], Sum(IIf([Required Date]=""2018-11"",""1"",""0"")) AS [2018-11], Sum(IIf([Required Date]=""2018-12"",""1"",""0"")) AS [2018-12], ItmsCheckOutbyMonthtbl.LeadAddress " & vbCrLf & _
"FROM ItmsCheckOutbyMonthtbl " & vbCrLf & _
"WHERE (((ItmsCheckOutbyMonthtbl.[Required Date]) Between '" & [Forms]![CDB_INDV]![Begin-Date] & "' And '" & [Forms]![CDB_INDV]![End-Date] & "')) " & vbCrLf & _
"GROUP BY ItmsCheckOutbyMonthtbl.Office, ItmsCheckOutbyMonthtbl.Title, ItmsCheckOutbyMonthtbl.Name, ItmsCheckOutbyMonthtbl.LeadAddress;"

Dim Titles As String

  
  If Not rs2.EOF Then
    Do
   
      Titles = Titles & rs2!Office & " " & rs2![Name] & " " & vbCr
    
  'Retrieve the name of the first city in the selected rows
    strOffice = rs2!Office

    'Now filter the Recordset to return only the customers from that city
    rs2.Filter = "Office = '" & strOffice & "'"
    Set rs2Filtered = rs2.OpenRecordset
     MsgBox DLookup("[name]", "ItmGrpCheckbyMonthWnamesQry", "WHERE RECORDSET = " & strOffice & "")
      rs2.MoveNext
    Loop Until rs2.EOF
   ' MsgBox Titles
   
     End If