Select Date Range to Generate Query?

[michaeljohannes]

Hello Access Forum 'Form'ers,

I have question regarding a simple way to retrieve information in a report (based on a query).

I would like to create a Form (called frmSelectDateRange) where a user can retrieve results from a query (and consequently shown in a report) using combo boxes.

I think it would be something like:

2 dropdown boxes to show dates (nice new feature in 2007 shows a little calendar one can easily select a date):

cboStartDate

cboFinishDate

1 control button:

cmdOpenReport (this report based on the query)

In the past, I have done this where I simply use the following code in the 'date' field of the query.

SELECT DISTINCTROW from [myTable] where [date] BETWEEN [enter the start date] AND [Enter the finishDate]

More accurately, in the design view of the query, I simply entered the above BETWEEN statement in the CRITERIA section of that field.

This WORKS! It's fine... But because it pops up a window that says, "Enter Parameter Value" with no option to use a calendar type button (featured in 2007), it's not as user friendly as having a form to do this.

So! Is it even possible to use two unbound fields in a form INSTEAD of using a SQL between statement for a date, to generate a report based on a query I create/assign to that given report?

Any, all thoughts are much appreciated!

Thanks!
Mike

 

[pbaldy]

Of course; it's how most of us have users enter criteria. It would look like:

...Between Forms!FormName.cboStartDate And Forms!FormName.cboFinishDate

 

[michaeljohannes]

Excellent! Thanks PBaldy.

Is there a common expression in the expression builder (like a specific built in function) I can use so my unbound cboStart and cboFinish shows a calendar when I click the dropdown arrow? I can't seem to figure out this feature... (I can set the control to default to todays date, but not the option to select a date from this calendar type selection window). Can you suggest an expression I should enter to make the drop down work this way?

Best,
Mike

 

[pbaldy]

Sorry, I have not worked with 2007, so not sure how to help you.

 

[michaeljohannes]

Is there a simpler way to do this? Perhaps using a drop down isn't necessary. Do you have an example of how you did it for your users?

Thanks!
Mike

 

[boblarson]

Is there a simpler way to do this? Perhaps using a drop down isn't necessary. Do you have an example of how you did it for your users?

Thanks!
Mike

Mike:

If I remember, tonight at home (I have 2007 at home) I'll try to look and see what you can do.

 

[michaeljohannes]

cheers, Bob!

I have an idea that works (but is likely not ideal...)

If I create a new table and call it "tableDate" and give it two fields start_Date and finish_Date (without a primary key, id number), I can then use these two fields as controls on the form I want.

Because they are arbitrary fields in a table that has no relationship to any other tables, when I import those fields (as controls) on the form, it gives me that little calendar button.

I can then use the above code

Between Forms!FormName.cboStartDate And Forms!FormName.cboFinishDate

replacing the cbostart with the actual control name on the form. then use a cmd button to open the query.

What do you think? Not really ideal I guess, but I think it will work!

Best,
Mike

 

[pbaldy]

Rather than the ActiveX calendar control, I use this, which has worked very well for me:

http://www.mdbmakers.com/forums/showthread.php?t=235

 

[michaeljohannes]

Thanks, pbaldy! I'll have a look this weekend. Have a happy Memorial Day, everyone.

Mike