Change query thru Code (1 Viewer)

[Ron_dK]

Hi all,

I have a number of queries in which criteria is set for certain periodes , e.g.
"between #01/01/2008" and #04/01/2008".
In stead of changing these dates manually ( in the Qry) , I would like to change this thru VBA or using code.

Example of Query :

SELECT TbRequest.Country, TbInput.[Conducted]
FROM TbRequest LEFT JOIN TbInput ON TbRequest.[Req-ID] = TbInput.[Req id]
WHERE (((TbInput.[Conducted]) Between #1/1/2008# And #12/31/2008#))
ORDER BY TbInput.[Conducted];

This is what I have made in VB, ( with Thanks to WayneRyan) but this doesn't work :

Sub Qry2009Update()
Dim ctl As Control
DoCmd.OpenQuery "QryStat", acDesign
For Each ctl In Query("QryStat").Controls
    With ctl
        Select Case .ControlType
            Case acTextBox
               ctl.ControlSource = Replace(ctl.ControlSource, "/2008#", "/2009#")
        End Select
    End With
Next ctl
DoCmd.Close acQuery, "QryStat", acSaveYes
End Sub

I would appreciate any pointers on this.

 

[Paul Buttress]

Create a Query on the fly

Hi

Try this module I find it very useful.

The previous query is overwritten.

The code under the module is to be used in your form, I'm sure you'll know what to do with it.

Regards

Paul

 

[gemma-the-husky]

save the dates as global variables, then phrase the query as

between date1() and date2()

 

[Ron_dK]

Hi

Try this module I find it very useful.

The previous query is overwritten.

The code under the module is to be used in your form, I'm sure you'll know what to do with it.

Regards

Paul

I've played around with this, but can't get it to work.

 

[Ron_dK]

save the dates as global variables, then phrase the query as

between date1() and date2()

Looks like this doesn't do more than changing every Query manually.
That's what I wanted to prevent.
Thanks anyway.

 

[Paul Buttress]

Hi Rak

I've put together a full example for you. There is a simple form and a table containing orders from 01 Feb 2005 to 19 Feb 2005. Try this and let me know how you get on.

 

[Ron_dK]

Hi Rak

I've put together a full example for you. There is a simple form and a table containing orders from 01 Feb 2005 to 19 Feb 2005. Try this and let me know how you get on.

Paul,

This is good stuff.
I have modified it to my needs and this works fine with me.

Thanks for that.

Cheers, Ron