Pulling a Report Automatically between two days

[Ruenells]

I am looking to have a report that was working in Crystal Reporting and I am now trying to get to work in MS Access.

I currently have to manually enter the date and time range I want the report to be.

I want to be able to hit the button on the switchboard and have it lauch and pull the correct information.

I need it to pull from Tuesday at 11:00 am of the current week to Thursday to 3:00 pm of the current week and also from the current Tuesday 11:00 am to the following Tuesday (one Week out until 3:00 pm.

This did work in Crystal, the problem is I can no longer access the code.

I am desparate for any help!!

 

[dhookom]

Are these dates and times always the same? If so, you should be able to set the criteria under your date field to something like:

BETWEEN DateAdd("d",-Weekday(Date())+3,Date()) + 11/24 AND
DateAdd("d",-Weekday(Date())+5,Date()) + .625

I generally save my report with its record source free of any criteria. I use the Where Condition of the DoCmd.OpenReport method to filter reports.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Ruenells]

The times are always the same, however the day of the week will always be the same, however it will always be the current weeks tuesday.

 

[Ruenells]

also can you please explain the 11/24 and the .625

I am not sure what you are calling.

 

[PHV]

11/24 is 11:00 am
.625 (ie 15/24) is 3:00 pm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ruenells]

ok i must be stupid because when i put that in the query grid it wants to make it a date and puts the # and completes the dates for me.

I am so confused now

 

[dhookom]

Dates are numbers and numbers can be dates. Don't you want the dates? Aren't they correct?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Ruenells]

Thank you so much for responding, however perhaps I am not being clear.

Tomorrow morning I will be manually entering [Start Date] and an [End Date] with the following information. 12/18/07 11:00 am and 12/20/07 3:00 pm

I am looking to see if there is a way to have the times remain the same and have it always pull from Tuesday to Thursday within the same week.

I also will need to pull the same information from Tuesday to the following Tuesday.

Currently when I copied and pasted the code you provided. Access converted the 11/24 to #11/24/07# it wanted to make it a date range. I am not sure that is what it was supposed to do.

Thank you so much for your help with this I truly appreciate your help!!

Rue

 

[dhookom]

I assume you entered my expression into the criteria of the query grid. There is no way that I am aware the expression of [blue][tt]DateAdd("d",-Weekday(Date())+3,Date()) + 11/24 AND DateAdd("d",-Weekday(Date())+5,Date()) + .625
[/tt][/blue] will change into dates. The values returned from the expressions will return the Tuesday at 11:00 AM and Thursday at 3:00 PM of the current week.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Ruenells]

I pasted exactly what you gave me and this is what i got

DateAdd("d",-Weekday(Date())+3,Date())+#11/24/2007# And DateAdd("d",-Weekday(Date())+5,Date())+0.625

I did not add the date when i hit enter it converted to that format

 

[dhookom]

I see now. Only the 11/24 was converted to a date. Try

BETWEEN DateAdd("d",-Weekday(Date())+3,Date()) + #11:00# AND
DateAdd("d",-Weekday(Date())+5,Date()) + #15:00#

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Ruenells]

Sorry it took me so long to reply back but THANK YOU! This worked perfect!!

You rock!

Rue