How to open a dated critiera database? (1 Viewer)

I have created a student database that has Dates: Received, Start, End, and Taken. I want to [1] open the report by using the start and end date macro's and [2] only open reports where dates are not taken. Any suggestions?

I'm not clear on the request. You can use a criteria of

Is Null

on the Taken field to return records where that field is Null (empty).

Not understanding. Here's what I have set up in SQL:
FROM Students
WHERE (((Students.[Date Received]) Between [start date] And [end date]));

This currently is not working. But in another database that I have the "Where" criteria is working perfectly. Can you give an example of the null criteria?

That should prompt the user for 2 dates, and return records in between. That doesn't work? Does the field have a date/time data type?

I think what you are asking for is:

FROM Students
WHERE (Students.[Date Received] Between [start date] And [end date]) AND Taken Is Null;

Thanks, that does work; however, the database is only picking up 5\11 records. Also, as in other database using, when I click to open a report, a pop up box start date and end date have to be entered. This includes when opening the query. I'm okay with the pop up not appearing, but would like to have all open records to be shown?

If you're not getting prompted, those must be field names? Try changing to [Enter start date], etc.

Also, most of us use forms to gather user input. You have much more control.

1. When you open a report from a form (preferred method), you can have fields on the forms that contain the data you need and that will avoid prompting. So the start and end dates should refer to form fields OR the doCmd.OpenReport code can include a WHERE argument to provide the dates.
2. If you are not getting the records you expect when using a date range, it is most likely that the date fields contain time as well and that is interfering. NEVER format fields on the table. If you have a format attached to the date fields and that format does not include time, that doesn't prevent time from being stored, it simply prevents you from seeing the actual contents of the data. Any formating needed should be done on forms and reports and occasionally in queries if they are being exported to Excel. Know the difference between the various datetime functions - i.e. Now() = the current date + the current time whereas Date() = only the current date. So if you are populating date fields with Now(), you WILL have a time component. This is not always desirable so make sure your code is doing what you expect. That way when a date field will include time, you can create proper range criteria.