How To Find a Date within Date Range?

[jennyg]

Hi,
As I am not able to search with the Forum, please let me know if my question has already been asked and answered.

I am quite rusty in using Access 2007, I'd like to get some help in finding a specific date is within a given date range.

E.g., Mr A in Australia and Mr B in China are taking leave from 23rd Dec to 8th Jan. I want to find out how many days are for Public Holidays accordingly.

I have a staff table has a field called location, I also have a PH table has date and location fields.

What would be ideal is, when I enter 23rd Dec and 8th Jan for Mr A and Mr B in database, PH dates can be automatically populated to next record.

Am I asking too much from Access?

Thanks heaps.

Jenny

 

[VRoscioli]

Please clarify exactly what you asking for.


-V

 

[jennyg]

Hi V,

I want to record staff's leave dates and types, and to report on how many days of leaves of each type during a year.

There are "Leave Starts" and "Leave Ends" fields. I'd like to know if Access can exclude weekends and the public holidays between the starts and ends dates.

Further more, I'd like to record these Public Holidays and report them as well.

Does this make sense?

Thanks for your time.

Jenny

 

[VRoscioli]

How do you determine what days are "public holidays"? Do you have a table that lists them? Are you looking for a query that will search through the StaffLeaveDates table and return what days match holidays or...??


-V

 

[jennyg]

Hi V,

Yes. I have a PH table, and I would like to search this table, if there are any Public Holidays falls in between LeaveStart and LeaveEnd. And, if that Public Holidays can be populated to the Leave Record table, as PH days need to be reported as well.

Thanks

Jenny

 

[VRoscioli]

The following will give you a list of records that should contain the information you want. I'm still not exactly sure what you are looking for, but this should be a good jumping off point for you:

SELECT L.*, PH.*
  FROM tblLeave L, tblPublicHolidays PH
  WHERE PH.HolidayDate Between L.StartDate and L.EndDate;

-V

 

[jennyg]

Hi V,

I will start from here. See how I go. Thanks very much.

Jenny