Change date in multiple queries

[PauloG]

Hi,

I have a report which I run each day which shows how many types of incidents
we have had and the details of these incidents. I have 10 queries which are
set up to find these incidents which have been added to this report as
subreports. Every day I have to change the date in each of these queries to
the same date (e.g. Between #20/10/2008 07:00:00# And #21/10/2008 07:00:00#)

Is there a way of changing the date in one of these queries and all of them
being updated or on opening the report I simply put in the start and end date
and all of the queries are updated?

Any help anyone can give would be appreciated.

Cheers

Paul

 

[Allen Browne]

One way would be to create a little form with 2 unbound text boxes.

Then in the query, set the Criteria to something like this:
Between [Forms].[Form1].[txtStartDate] And [Forms].[Form1].[txtEndDate]

To get this to work reliably:
a) Set the Format property of the unbound text boxes to General Date or
similar (so Access knows they are dates.)

b) In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter 2 rows:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time
This ensures the query does a date comparsion (not a text comparsion.)

 

[John Spencer]

In addition to what Allen Browne said, if you are filtering for the prior day
every time you could use and expression like the following.

Between DateAdd("h",7, Date()-1) AND DateAdd("h",7,Date())

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[PauloG]

Hi Allen,

I must be doing something wrong as when I am trying to open my report I get
a prompt for a start and an end date but this appears ten times! Is there a
way of only having this appear once. I updated each of the 10 queries with
the date criteria and the query parameters as advised so not sure what I did
wrong.

Cheers

One way would be to create a little form with 2 unbound text boxes.

Then in the query, set the Criteria to something like this:
Between [Forms].[Form1].[txtStartDate] And [Forms].[Form1].[txtEndDate]

To get this to work reliably:
a) Set the Format property of the unbound text boxes to General Date or
similar (so Access knows they are dates.)

b) In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter 2 rows:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time
This ensures the query does a date comparsion (not a text comparsion.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a report which I run each day which shows how many types of
incidents
we have had and the details of these incidents. I have 10 queries which
are
set up to find these incidents which have been added to this report as
subreports. Every day I have to change the date in each of these queries
to
the same date (e.g. Between #20/10/2008 07:00:00# And #21/10/2008
07:00:00#)

Is there a way of changing the date in one of these queries and all of
them
being updated or on opening the report I simply put in the start and end
date
and all of the queries are updated?

Any help anyone can give would be appreciated.

Cheers

Paul

 

[Klatuu]

Where do you run your report from?
If you used a form and the suggestion Allen offered, it would work for you.

Another way is to include no date criteria in the queries, but use the Where
argument of the OpenReport method to do the filtering.

Dim strWhere As String

strWhere = "[MyDateField] BETWEEN #" & Me.txtFromDate & "# AND #" &
Me.txtToDate & "#"

Docmd.OpenReport "MyReport", , , strWhere

Hi Allen,

I must be doing something wrong as when I am trying to open my report I
get
a prompt for a start and an end date but this appears ten times! Is there
a
way of only having this appear once. I updated each of the 10 queries with
the date criteria and the query parameters as advised so not sure what I
did
wrong.

Cheers

One way would be to create a little form with 2 unbound text boxes.

Then in the query, set the Criteria to something like this:
Between [Forms].[Form1].[txtStartDate] And
[Forms].[Form1].[txtEndDate]

To get this to work reliably:
a) Set the Format property of the unbound text boxes to General Date or
similar (so Access knows they are dates.)

b) In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter 2 rows:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time
This ensures the query does a date comparsion (not a text comparsion.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a report which I run each day which shows how many types of
incidents
we have had and the details of these incidents. I have 10 queries which
are
set up to find these incidents which have been added to this report as
subreports. Every day I have to change the date in each of these
queries
to
the same date (e.g. Between #20/10/2008 07:00:00# And #21/10/2008
07:00:00#)

Is there a way of changing the date in one of these queries and all of
them
being updated or on opening the report I simply put in the start and
end
date
and all of the queries are updated?

Any help anyone can give would be appreciated.

Cheers

Paul

 

[Allen Browne]

You need to open the form first, fill in the dates, and then open the
report.

If it still asks for the parameters after that, then the names are not spelt
identically. For example, you may have used spaces in one name but not the
other.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I must be doing something wrong as when I am trying to open my report I
get
a prompt for a start and an end date but this appears ten times! Is there
a
way of only having this appear once. I updated each of the 10 queries with
the date criteria and the query parameters as advised so not sure what I
did
wrong.

Cheers

One way would be to create a little form with 2 unbound text boxes.

Then in the query, set the Criteria to something like this:
Between [Forms].[Form1].[txtStartDate] And
[Forms].[Form1].[txtEndDate]

To get this to work reliably:
a) Set the Format property of the unbound text boxes to General Date or
similar (so Access knows they are dates.)

b) In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter 2 rows:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time
This ensures the query does a date comparsion (not a text comparsion.)

Hi,

I have a report which I run each day which shows how many types of
incidents
we have had and the details of these incidents. I have 10 queries which
are
set up to find these incidents which have been added to this report as
subreports. Every day I have to change the date in each of these
queries
to
the same date (e.g. Between #20/10/2008 07:00:00# And #21/10/2008
07:00:00#)

Is there a way of changing the date in one of these queries and all of
them
being updated or on opening the report I simply put in the start and
end
date
and all of the queries are updated?

 

[TONGO]

In addition to what Allen Browne said, if you are filtering for the prior
day every time you could use and expression like the following.

Between DateAdd("h",7, Date()-1) AND DateAdd("h",7,Date())

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[PauloG]

Worked like a charm!

Thanks

You need to open the form first, fill in the dates, and then open the
report.

If it still asks for the parameters after that, then the names are not spelt
identically. For example, you may have used spaces in one name but not the
other.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I must be doing something wrong as when I am trying to open my report I
get
a prompt for a start and an end date but this appears ten times! Is there
a
way of only having this appear once. I updated each of the 10 queries with
the date criteria and the query parameters as advised so not sure what I
did
wrong.

Cheers

One way would be to create a little form with 2 unbound text boxes.

Then in the query, set the Criteria to something like this:
Between [Forms].[Form1].[txtStartDate] And
[Forms].[Form1].[txtEndDate]

To get this to work reliably:
a) Set the Format property of the unbound text boxes to General Date or
similar (so Access knows they are dates.)

b) In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter 2 rows:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time
This ensures the query does a date comparsion (not a text comparsion.)

Hi,

I have a report which I run each day which shows how many types of
incidents
we have had and the details of these incidents. I have 10 queries which
are
set up to find these incidents which have been added to this report as
subreports. Every day I have to change the date in each of these
queries
to
the same date (e.g. Between #20/10/2008 07:00:00# And #21/10/2008
07:00:00#)

Is there a way of changing the date in one of these queries and all of
them
being updated or on opening the report I simply put in the start and
end
date
and all of the queries are updated?