We have a form set up that is used to capture certain information, several times throughout the day, everyday. I would like to capture the information that has been inputted on a daily basis, perhaps in a report, and email it out. I am comfortable with the creating and mailing out of reports from access. I was able to create a report and mail it out, however the report contained all information in the database. I need the report to only contain the information inputted for that day. Is this a report issue or a form issue?

Can anyone help please?

Hi

I'm guessing that in a table you're storing the date that the data was input, so all you need to do is in the query that your report uses as it's record source (Assuming that it is a query, if not you can change it to one) you'd just add criteria to the date field of =Date()

I'm guessing again that in that scenario you're not storing the time as well as the date.

Another option would be to open the report using the Where parameter of the OpenReport method

Hi Alan,

Thanks for getting back to me so quickly.

I created the report using the Report Wizard and a table. I am also storing the time as well as the date. What would the criteria look like if I wanted it to capture the data between 00:01 and 23:59 daily?

Thanks

Hi

Easiest way would be to create a query based on the table you need and use that as the record source for your report. Add the appropriate fields from the table to the query. In the field where you have the date and time value stored, criteria would be something like

Thanks again Alan,

I have since created a query as you suggested. The date and time in my db are stored in seperate columns, so I assume that the criteria would have to be written differently? Once the date criteria is added, would it automatically report the previous 24 hr period when ran, or would I have to update the criteria everyday?

I appreciate your guidance.

Pardon my intrusion ...

There's no reason Access would know that you want to use the "previous day's" records, so you will have to tell it. However, you should be able to modify your query once and have it ALWAYS get "yesterday's" data...

A bit more info might help us help you...

Thanks Jeff,

Yes, that's what I am hoping to achive, to get "yesterday's data". So what info do you need from me?

It will not "automatically" do anything. You must tell it what to do. So if you want to use yesterday's date, the DateAdd function can help you with that.

For instance to get yesterday's date, without specifying today's date as a literal, you could use something like this (I am doing from memory, so you may need to fool with it):

DateAdd("d", -1, Date())

To include the time, you could do as Jeff B. already posted

Between DateAdd("d", -1, Date()) & " 00:00:01" And DateAdd("d", -1, Date()) & "23:59:59"

Hi



I'd suggest storing both the date and time in one field defined as Date/Time. You can always get the date and time values separately whenever/wherever you need them.

Assuming you change things to the same field, then to get yesterdays data Franks criteria is something like you need, although I *think* you may need to use the CDate() function

Thanks very much everyone I changed the date and time to one column and used Alan's criteria.

- glad we could help

Oops ran into a problem when I inputted yesterdays data and some of todays. When I run the query it only shows me todays data? I was hoping that it would only show me yesterdays from 00:00:01 to 23:59:59.

The query I used was >= CDate(DateAdd("d", -1, Date()) & " 00:00:01") And CDate(DateAdd("d", -1, Date()) & " 23:59:59)"

Hi

You're a comparison operator missing in the criteria expression you posted - specifically the less than or equal to (<=) - and also the ending double quotes is wrong. It should either be



or alternatively

Thanks very much everything is now working. I really do appreciate your help.

- glad we could help and you're up and running