Hi,

Is there a way to build queres where I could change the arguments via vba?

Say I have this query

SELECT COUNT(ID) FROM MyTable WHERE SubmitDt BETWEEN #01/02/2008# AND #29/02/2008#...

Next month, I would have to go into the query and change the date... but I would like to have a form, where I just choose the month/year and it will be able to modify the between date ranges...

There are several ways to do this. How do you want the user to select the date ranges?

You could have two combo boxes on your form that you populate with dates from your table data (set Unique values to yes). The user selects a data from the start range, then the second combo cascades only later dates, and the user selects the end date from that combo. Then on your query, rewrite it to lool like this:

SELECT COUNT(ID) FROM MyTable WHERE SubmitDt BETWEEN [Forms]![myForm].[cboStartDate] AND [Forms]![myForm].[cboEndDate]

You can also do this if you want to prompt the user at runtime:

SELECT COUNT(ID) FROM MyTable WHERE SubmitDt BETWEEN #[Please enter a start date:]# AND #[Please enter an end date]#

You can also make a form that has two combos, one of which has Month Names, and one of which has years. The Month combo would have two columns, like this:

Col0 Col1
1 January
2 February
3 March

etc.

Then when the user selects the month and year, do this with the code:

Dim iMonth As Integer
Dim iYear As Integer
Dim sLink As String
Dim sDoc As String

If IsNull(Me.cboMonth) or Me.cboMonth = "" Then
   MsgBox "Please select a month", vbCritical
   End Sub
Else
   iMonth = Me.cboMonth
End If

If IsNull(Me.cboYear) or Me.cboYear = "" Then
   MsgBox "Please select a Year", vbCritical
   End Sub
Else
   iMonth = Me.cboYear
End If

sLink = "DatePart("m", [MyDateField] = " & iMonth " AND DatePart("yyy", [MyDateField]) = " & iYear

sDoc = "rptMyReport"

DoCmd.etc

I may have gotten away from myself with that last one. That will work with SQL Server, but I am not sure if it will work with Acces. In that case, create two new fields in your query called: CurMonth and CurYear, and then put this in the Field:

CurMonth:DatePart("m", [MyDateField])

And then in the criteria, refer to the combo on your form. The same for the year.

You can see there are many ways to do this.

Did that help?




mmcdonal

Look it up at: http://wrox.books24x7.com

I sould mention, I had to do a similar thing with a report, and I know the report will only ever be run from this interface after the first of the month for the previous month. So I select the month and year automatically like this:

Dim iMonth As Integer
Dim iYear As Integer

iMonth = DatePart("m", Date()) - 1
iYear = DatePart("yyyy", Date())

etc.



mmcdonal

Look it up at: http://wrox.books24x7.com