dynamic query

[daMike]

Hello,

does somebody know how to generate a dynamic query with VBA?
I already generated a form which allows the user to select a
specific date / period. With that information a query should
be started to build a table with all datasets related to the
input date /period)! I work with WinXP and Access2002!

Thanks a lot!
daMike

 

[Peter Smith]

does somebody know how to generate a dynamic query with VBA?
I already generated a form which allows the user to select a
specific date / period. With that information a query should
be started to build a table with all datasets related to the
input date /period)! I work with WinXP and Access2002!

The form you use to display the data will have a filter property.
Calculate your start and end dates using DateAdd to get StartDate + Period
= EndDate.

Make a filter string like:
MyFilter = "MyDateField BETWEEN #" & StartDate & "# and #" & EndDate & "#"

In VBA set MyForm.Filter = MyFilter
and set MyForm.Filter=TRUE

I have made up some nice long filter strings when necessary.
See if that helps...

- Peter

 

[Van T. Dinh]

Assume that you have the Controls "txtStartDate" and "txtEndDate" on your
Form "frmParameter", then you can use the references to the Controls as
Parameters for your Query with criteria like:

.... WHERE [DateField]
Between Forms!frmParameter!txtStartDate
And Forms!frmParameter!txtEndDate