Using Form data for an Action Query

[School Boy Error]

Hello, I have set up my database to show the user a form to select data for my query. They need to choose start date and end dates and then can choose whether to further filter by pupil, year group, incident type etc. I have my form working, in that it allows the user to input the data I want and reads this from the table. However when it runs the query, if I only enter start date and end date it shows all the records irrelevant of date and if I enter anything else - pupil name or year group it returns no records! I've only been using Access since Sunday so sorry if this is an obvious or stupid question but I can't seem to get it to do what I want. Thanks

[School Boy Error]

Sorry, I should have included that in my query the statements read

Date field - Between [Forms]![frm_Report Builder]![StartDate] And [Forms]![frm_Report Builder]![EndDate]

Pupil name - [Forms]![frm_Report Builder]![PupilName] or [Forms]![frm_Report Builder]![PupilName] is null

Year group - [Forms]![frm_Report Builder]![YearGroup] or [Forms]![frm_Report Builder]![YearGroup] is null

Incident date - [Forms]![frm_Report Builder]![IncidentType] or [Forms]![frm_Report Builder]![IncidentType] is null

Everytime I add the OR field value, close the query and reopen it, it hasn't saved! I want the user to have to choose some dates but after that the other three are optional if they wish to further filter the data. Don't know why it's not working!

[School Boy Error]

I deleted the query, made a new one. Worked a couple of times and then stopped - I'm really confused.

[School Boy Error]

Don't know what happened - it was working with just the date query in. I added the pupil name. It worked. Added the year group and incident, it stopped working. Deleted them again and now nothing works

[School Boy Error]

The open query thing in the Macro?

[rpeare]

There are a lot of problems with this database in terms of design and I'll try to explain why. Just in the small amount of data you have there is a variability in how an incident is recorded. For instance you have 'missed 5 minutes of play' and 'missed five minutes of playtime'. I've altered your database considerably to show you how to work with a normalized structure so that your reports will have a uniform look when you print them out. There are some additional things to consider that might make this database easier to use, for instance assigning a year to each student do this once at the beginning of the year and you don't have to mess with making it part of your data entry for example. The structure I'm using is designed to store as little data as possible and give you the end result you want (this is how I think of normalizing more than anything else)

There code that drives the report is no longer based on a make table query or update table query it's based on changing the RECORDSOURCE of the report when you run it. The base query is shown, the code that modifies the WHERE statement of the base query is in the ON OPEN event of the report.

P.S. you'll have to re-establish the links in your environment.

[School Boy Error]

Thank you! That makes sense but I tried to keep it to one table because we wouldn't include all the children and we wouldn't keep any other details bar there name. I know what you mean about the reports looking different but unfortunately the nature of teaching means the discipline issues are unpredictable so I didn't want to go down the route of a listbox for the issue and the sanctions again can be varied. I will take the time picking through how you've done this though for my own knowledge and it may well help with the next thing I have in mind! Thanks again