How do I display a certain value based on a table?

[Galphanore]

I have a table with three fields, StartDate, EndDate and Holiday Name. What I am trying to do is have a query search this table and determine if a date is between any of the startdate and enddate fields, and if it is have it output "Holiday", and have it output "Non-Holiday" if it does not.

 

[Jon K]

Holiday: IIf((Select Count("*") from [HolidayTableName] where [DataTableName].[DateFieldName] between [StartDate] and [EndDate]), "Holiday", "Non-Holiday")

replace with the correct table names and date field name.
.