Between ...And

[Leif Thorsen]

I am using the command "Between XX-date and YY-date" in a date-field named
"DateWritten" in a question where I have a bunch of posts. Let´s say that the
lowest date I have in my table is "XX-date+6days". In a report based om my
question I want to have following in the header of the report. "This shows
all posts between XX-date and YY-date"
How can I get this ???

 

[vanderghast]

you can use:

= MIN(fieldname) & " to " & = MAX( fieldName)


to get the minimum, maximum, sum, count, ... for the given group
(subgroup... or header if you want) you are actually in. You can also use
the standard DMIN, DMAX... but then, you will have to construct the third
criteria all by yourself, if you don't target the whole table. MIN and
MAX,in a report, used this way, also take into account the possible filters
you used.


Vanderghast, Access MVP

 

[Jerry Whittle]

I am using the command "Between XX-date and YY-date"

What do you mean by this? Is it something that you are actually storing in a
table or using it as a parameter in the criteria of a query. If in a query,
it shouldn't work as typed. If it is a parameter, it is possible to put what
is typed into the header of a report. Please explain further just what is
going on so that we can help you better.

 

[John Spencer]

A little unclear, but if you are using a parameter prompt you can refer to the
parameter in the report.

Set the source of the control to
="This shows all posts between " & [XX-Date] & " and " & [YY-Date]

IF you want to show the date range of the records returned, set the control's
source to
="This shows all posts between " & Min([DateWritten]) & " and " &
Max([DateWritten])

If you are not using a parameter prompt, but are using a reference to controls
on a form, then reference the controls. Forms![Formname]![StartDateControl]
in the expression.

If you are manually entering the date literals into the query, then I don't
know of any method that will allow you to get the date literals from the
query. Other than using some VBA to parse the query string and extract the
dates from the where clause.

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

 

[Allen Browne]

Add a text box to the Report Header section, and set its Control Source
property like this:
="This shows all posts between " & Format([XX-date], "Short Date") & " and "
& Format([YY-date], "Short Date")

If you wanted to show the minimum date actually present in the report
(rather than the parameters you used in the query), put a text box in the
Report Footer section, and give it these properties:
Control Source: =Min([DateWritten])
Format: General Date
Visible: No
Name: txtMinDateWritten

Do a similar thing for =Max([DateWritten]), and then use the name of these
text boxes in the expression above in place of XX-date and YY-date