SQL criteria using multi-select Listbox

Question

I'm using Allen Browne's method that builds an SQL criteria string based on a form's unbound fields. I'm using the criteria to delete table records. I've gotten it pretty far, but ran into a roadblock. One of the fields i'm grabbing criteria from is a multi-select listbox. The list box contains the days of the week...Monday, Tuesday, etc.. The table in question contains information on hearings. It has a date field called, "HearingDate". Hearing dates can be on any (business) day of the week. I want to delete any hearings that occurred on certain day(s) of the week.

Example scenario: I want the user to input a date range...let's say, 4/1/2022 to 4/30/2022. In that date range, there may be hearings on each day of the week. But i only want to delete these hearings, if the hearing date lands on a Tuesday or a Wednesday.

One site said to use the IN clause which i tried (see below). However that didn't seem to work. I know i can loop through the recordset to figure out which ones to delete, but i imagine it's more efficient to delete using an sql command. I tried different variations of double quotes and single quotes with the IN clause, but nothing seems to work. It runs without error, but doesn't delete what is specified. Any help is appreciated.

This is as far as i got...

'***********************************************************************

'Look at each criteria dropdown and build up the criteria string from the non-blank ones.

'***********************************************************************

'--These are required fields, so we don't have to check if they are blank first before including in the strwhere

strWhere = strWhere & "([JudgeID] = " & Me.cboJudge & ") AND "

strWhere = strWhere & "([HearingDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "

strWhere = strWhere & "([HearingDate] <= " & Format(Me.txtEndDate, conJetDate) & ") AND "

If Not IsNull(Me.cboCourtClerk) And Me.cboCourtClerk <> "" Then

strWhere = strWhere & "([CourtClerkID] = " & Me.cboCourtClerk & ") AND "

End If

If Not IsNull(Me.cboCourtroom) And Me.cboCourtroom <> "" Then

strWhere = strWhere & "([CourtroomNum] = """ & Me.cboCourtroom & """) AND "

End If

If Not IsNull(Me.cboListType) And Me.cboListType <> "" Then

strWhere = strWhere & "([ListType] = """ & Me.cboListType & """) AND "

End If

If Not IsNull(Me.cboRecorder) And Me.cboRecorder <> "" Then

strWhere = strWhere & "([RecorderID] = " & Me.cboRecorder & ") AND "

End If

'See if the string has more than 5 characters (a trailing " AND ") to remove.

lngLen = Len(strWhere) - 5

strWhere = Left$(strWhere, lngLen)

'--For the multi-select listbox, assign chosen values into an array.

Dim i As Integer

Dim SelectedEntry As Variant

Dim DataEntryDays() As Variant

ReDim DataEntryDays(1 To Me.lstBoxDaysOfWeek.ItemsSelected.Count)

i = 1

For Each SelectedEntry In Me.lstBoxDaysOfWeek.ItemsSelected

DataEntryDays(i) = """" & Me.lstBoxDaysOfWeek.ItemData(SelectedEntry) & """"

i = 1 + i

Next

'--Build a string of whatever days were chosen.

strwhereDaysOfWeek = ""

For i = 1 To UBound(DataEntryDays)

strwhereDaysOfWeek = strwhereDaysOfWeek & DataEntryDays(i) & ","

Next

strwhereDaysOfWeek = Left(strwhereDaysOfWeek, Len(strwhereDaysOfWeek) - 1)

'--Combine everything together.

strWhere = strWhere & " AND (""" & WeekdayName(Weekday(HearingDate)) & """ IN (" & strwhereDaysOfWeek & "))"

mysql = "DELETE * FROM tblHearings WHERE " & strWhere

CurrentDb.Execute mysql

Answer 1

Try replacing the part

'--For the multi-select listbox, assign chosen values into an array.
Dim i As Integer
Dim SelectedEntry As Variant
Dim DataEntryDays() As Variant
ReDim DataEntryDays(1 To Me.lstBoxDaysOfWeek.ItemsSelected.Count)
i = 1
For Each SelectedEntry In Me.lstBoxDaysOfWeek.ItemsSelected
DataEntryDays(i) = """" & Me.lstBoxDaysOfWeek.ItemData(SelectedEntry) & """"
i = 1 + i
Next
'--Build a string of whatever days were chosen.
strwhereDaysOfWeek = ""
For i = 1 To UBound(DataEntryDays)
strwhereDaysOfWeek = strwhereDaysOfWeek & DataEntryDays(i) & ","
Next
strwhereDaysOfWeek = Left(strwhereDaysOfWeek, Len(strwhereDaysOfWeek) - 1)
'--Combine everything together.
strWhere = strWhere & " AND (""" & WeekdayName(Weekday(HearingDate)) & """ IN (" & strwhereDaysOfWeek & "))"

with

    '--For the multi-select listbox, assign chosen values into an array.
    Dim SelectedEntry As Variant
    strwhereDaysOfWeek = ""
    For Each SelectedEntry In Me.lstBoxDaysOfWeek.ItemsSelected
        strwhereDaysOfWeek = strwhereDaysOfWeek & ",'" & Me.lstBoxDaysOfWeek.ItemData(SelectedEntry) & "'"
    Next SelectedEntry
    strwhereDaysOfWeek = Mid(strwhereDaysOfWeek, 2)
    '--Combine everything together.
    strWhere = strWhere & " AND Format(HearingDate,'dddd') IN (" & strwhereDaysOfWeek & ")"

Answer 2

strWhere = strWhere & "([JudgeID] = " & Me.cboJudge & ") AND "

strWhere = strWhere & "([HearingDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "

strWhere = strWhere & "([HearingDate] <= " & Format(Me.txtEndDate, conJetDate) & ") AND "

If Not IsNull(Me.cboCourtClerk) And Me.cboCourtClerk <> "" Then

strWhere = strWhere & "([CourtClerkID] = " & Me.cboCourtClerk & ") AND "

End If

If Not IsNull(Me.cboCourtroom) And Me.cboCourtroom <> "" Then

strWhere = strWhere & "([CourtroomNum] = """ & Me.cboCourtroom & """) AND "

End If

Hi Matt,

I only use dynamical generated sql-strings, so I use these kind of constructs very frequently.

A few remarks.

• If you use HearingDate <= " & Format(Me,txtEndDate, conJetDate), you retrieve only the records that have a Time of 00:00:)). Better is to use HearingDate < & Format(Me.txtEndDate + 1,conJetDate)
• When controlnames have no exceptional characters then you don't need the square brackets.
• It is easier to read, and find spelling errors to place the "AND " in the front of the line. For each condition ypu start with an " AND ", and finally replace the first " AND " with a " WHERE ". This always works, even when there were no conditions used/met.
• To check for both a Null and a ZLS, you can use: (Me.cboCourtClerk > "")
• The general function As_text does all the 'terrible' and confusing quoting.

The above citation would then read as:

strWhere = strWhere & " AND JudgeID = " & Me.cboJudge _

                               & " AND HearingDate >= " & Format(Me.txtStartDate, conJetDate) \_

                               & " AND HearingDate < " & Format(Me.txtEndDate +\_1, conJetDate)

If (Me.cboCourtClerk > "") Then strWhere = strWhere & " AND CourtClerkID = " & Me.cboCourtClerk

If (Me.cboCourtroom > "") Then strWhere = strWhere & " AND CourtroomNum = " & As_text(Me.cboCourtroom)

Imb.

Answer 3

Brilliant!! Works great! Thanks so much for taking the time. I've already spent many hours on that one issue. You're a lifesaver!