DLookUp to return all occurrences instead of only the first one

Question

I check if the date selected by the user matches the tblFestivity of the field Festivity_Date.
If these match I display a Message Box.

The field where the user inserts the date:

The table tblFestivity and the field Festivity_Date:

It works for the first record:

Private Sub Date_Flow_Exit(Cancel As Integer)
If Date_Flow = DLookup("[Festivity_Date]", "tblFestivity", "[Date_Flow]=Form![Date_Flow]") Then
    MsgBox "è un giorno festivo"
End If 
End Sub

This code shows the message box but only for the first record, it doesn't check the other records in the table tblFestivity.

Date_Flow is the name of the textBox in the first immage.

How can I check all the occurrences instead of only the first one?

The `DLookup` returns only the first occurrence when more records meet the criteria. Not sure what the question is. — Kostas K., Dec 29 '21 at 11:13
Sorry, I didn't posted any question in fact... My question is: how can I check al the occurrence instrad of only the first one? — ObiGuinnessBartowski, Dec 29 '21 at 11:50
It depends on what you are trying to achieve. If you want all the results as a list, you will have to open a recordset and loop through it. You can also use the `DCount` that will return a count of all the occurrences. — Kostas K., Dec 29 '21 at 12:52
What i'm trying to achieve is: I would like that if the user select one date , and if this date is on the table tblFestivity, I need a messagebox, not only for the first record like it is now. Thanks for the answers Kostas K. — ObiGuinnessBartowski, Dec 29 '21 at 12:59

score 1 · Answer 1 · answered Dec 29 '21 at 13:31

If I understand correctly, you want to display a message for each occurrence of the date entered by the user?

If that is the case, then I would suggest you to open recordset with results and iterate through each one and display message.

Private Sub Date_Flow_Exit(Cancel As Integer)
Dim rs As Recordset
Dim dt As Date
dt = Me.Date_Flow
Set rs = CurrentDb.OpenRecordset("SELECT tblFestivity.Festivity_Date FROM tblFestivity WHERE (((tblFestivity.Festivity_Date) =#" & dt & "#));") 'Opens recordset only with dates entered in textbox

With rs
    If .RecordCount > 0 Then
        .MoveFirst
        Do While Not .EOF
            MsgBox ("è un giorno festivo")
            .MoveNext
        Loop
    Else
        MsgBox ("There are no festivities on this day")
    End If
End With
End Sub

Let me know it that is what you wanted?

Thank you Milos I've found the solution, thank you for your time, I will also try to adapt yours! — ObiGuinnessBartowski, Dec 29 '21 at 14:06

June7 · Accepted Answer · 2021-12-29T13:23:40.693

0

It's not finding match because WHERE CONDITION syntax is wrong and not using field name in criteria. Correct syntax for referencing field or control on form is Forms!formname!fieldORcontrol name. Criteria should use name of field matching to value on form.

If Not IsNull(DLookup("[Festivity_Date]", "tblFestivity", "[Festivity_Date] = Forms!formname!Date_Flow")) Then

Or

If DCount("*", "tblFestivity", "[Festivity_Date]=Forms!formname!Date_Flow") > 0 Then

edited Dec 29 '21 at 13:23

answered Dec 29 '21 at 13:17

June7

19,874
8
24
34

Thank you June7, my syntax were wrong! It perfectly works now, really appriciate the help! – ObiGuinnessBartowski Dec 29 '21 at 13:53

DLookUp to return all occurrences instead of only the first one

2 Answers2