How to get all records from a start date to an end date.

[SGT]

Hi,

I have a form (unbound) with a start date and an end date to get a report to give me the members on leave for that period. Every thing works fine but when a member is on leave from 2014/06/01 to 2014/06/10, and I enter a start date of 2014/06/03 and end date of 2014/06/04, I get a blank report. What I want instead is for the report to give me the members name because he was on leave in that time frame. I hope it makes sense. I want access to store all the days between 2014/06/01 and 2014/06/10 and not just the two dates entered in the start and end dates.

I have a table [tbl_Leave_Records] with the [leavestartdate], [leaveenddate], [leavetype] and [paynumberFK]. I have a query [qry_Leave_Records] from that table, the unbound form [frm_Leave_Dates] to filter by start date, end date and paynumber and a report [rpt_Leave_Records] that gives me the results.

Thank you

[pareshj]

Hi,

Try the below query, use # for presenting dates in Access.

"select * from [tbl_Leave_Records]
where [leavestartdate] >= # " & startingdate & " # and
[leaveenddate] <= # " & enddate & " #"

Where,
startingdate = Your Leave Start Date
enddate = Your Leave End Date



Regards,
Paresh J

[SGT]

This is the code I have on the run report button to give me the values for the start date and end date:

Please Login or Register  to view this content.

[pareshj]

Hi,

Debug the code and check what goes in as StartDate and EndDate.
Also, check when you apply formatting on date with strcJetDate, whether it gets actually applied as you want them to.



Regards,
Paresh J

[SGT]

The above code works. I got it from another source. My dilemma is as follow: The leave database started out by adding each date the member was on leave into the system. IOW, if a member was on leave for 10 days, you have to capture 10 records or days (entries). I changed that by adding a start date and end date but access doesn't store the dates in between, it just stores the start date and end date. How can I get the dates in between the start date and end date to be recorded as well?