Form Options Group tied to date range

[gandnt]

I have a table that has four columns of dates.

1. Assigned Date
2. Reviewer Date
3. Approver Date
4. Scheduled Date

I would like to create a option for the user to select one of these four fields then search it by using a date range.

Or can I have them select 2 or 3,4 of the above options with date ranges for any or all? I would also then like the results to populate a report in print preview.

I am learning slowly but please be kind in your answer. And thank you for any help you might give

 

[John Big Booty]

Instead of storing all those dates in separate fields I would store them all in one field and add an additional field to indicate what the dates represent.

This will bring your table structure to a more normalised model, and additionally make it far easier for you to achieve your current goal.

 

[Receiverbuilder]

Not sure if this is what your looking for but setting up a simple user parameter search would be fairly simple using a form based query.

First you would have to create a new form with several unbound data boxes. Two unbound boxes for each of the data ranges you want to search for. So you would have as an exmaple a form field for assigned date name "AssignedDateStart" and a form field for assigned date named "AssignedDateEnd"

Then set up a query and in the criteria for the query you would list this.

Between [Forms]![formname]![AssignedDateStart] And [Forms]![frmASPISearch]![AssignedDateEnd]

Then create a form event based command thats a toggle switch to run the query. You may have to tweak the above criteria line to account for null entries depending on the realities of your data.

 

[gandnt]

Not sure if this is what your looking for but setting up a simple user parameter search would be fairly simple using a form based query.

First you would have to create a new form with several unbound data boxes. Two unbound boxes for each of the data ranges you want to search for. So you would have as an exmaple a form field for assigned date name "AssignedDateStart" and a form field for assigned date named "AssignedDateEnd"

Then set up a query and in the criteria for the query you would list this.

Between [Forms]![formname]![AssignedDateStart] And [Forms]![frmASPISearch]![AssignedDateEnd]


Then create a form event based command thats a toggle switch to run the query. You may have to tweak the above criteria line to account for null entries depending on the realities of your data.

I will give this a try and thanks for telling me something to try. There is no way at this point for me to change the table as was mentioned before your post. I am coming in on the back end a project that is 20 years old.

 

[gandnt]

Ok so if I simplify this down to seach for a name (initials) and then a date range. how would I go about it?

I placed an example in the attachment

 

[Receiverbuilder]

You would create a query that queries 2 fields the date and the assigned engineer.

In the query criteria for the date you would use the query line I listed in my earlier making changes for the form name, field name etc. Then set the toggle button set to run the query and just use the basic access tools to set it up to run your query.

If everything works right it should return a dinaset with the 2 date field and the assigned engineer field.

p.s. if you want additional information pulled by this query but your not changning the search parameters just add more fields to your query.