Solved: Dynamic Query

[kbsudhir]

Hi All,

I have a date/time field.
Here I am saving the date in "mm/dd/yyyy" format.

I want to pull the data between a set of dates.
Ex between 1st November 2008 and 14th November 2008

If I hardcode the dates then It is working but I want to make the criteria dynamic so that user can enter teh date as per the requirement.

How to do this.

Thanks for your time & help

Sudhir

[CreganTur]

There are 2 ways you can do this:

1) Parameter Query

2) via a Form

You'll have to decide which method is better for you. Some more information on each is outlined below:

Parameter Query:

Input boxes will automatically appear asking the User to input some data. All you have to do is run the query to have them appear. The only bad thing about them is a total lack of data validation- your query could need dates, but the User is free to enter a string or number value if they want. Check Access Help for some good info on Parameter Queries.

Via a Form:

You could create a form with 2 textboxes and a button. The textboxes are where the User would enter their start and end dates to form a range. You could put some data validation behind the button's click event to make sure that the User entered dates that make sense, and that they entered dates and not something else.

Then you could use a DoCmd.OpenQuery method to open up your query. Now, for your query you would need to open it up in design view ahead of time and make some changes to it. For the criteria of your dates you would want to select the build button. Then drill down to the form and select the correct textbox control for the right date criteria. This will make it so that your query will look at the values of the textboxes to get criteria. It's basically a fancy paramter query, but with validation.

HTH:thumb

[OBP]

You can see an example of the Form and Query method mentioned by Randy at the post
http://forums.techguy.org/software-development/749341-query-form.html

see the one attached to post #10.

[kbsudhir]

Thanks Cregan & OBP for the guidance.


Sudhir

[DarkSprout]

~ Or ~

Here's an example from one of my databases:


'// frm_RES_OccupanciesReport //
Public Function qryStartDate() As Date
On Error Resume Next
With Forms![frm_RES_OccupanciesReport].Form
'// cboYear|Year, cboMonth|Month, cboDay|Day ComboBoxs
qryStartDate = DateSerial(![cboYear], ![cboMonth], ![cboDay])
End With
End Function


In your query; within the criteria filter enter: (as an example)
>= qryStartDate()

This will then filter you query based on combo boxes on another form