Time Period Query?

[djmousie]

I'm trying to produce a time period on a report,however, I'm not sure how I need to go about it. For instance I have a field called "Date". In that fields are random dates. I want a query that will produce a field that results in something like this:

1/1/2004 - 3/5/2004

I don't know if it would be easier to create two seperate queries for each date or if this can be done all in one query. Any thoughts?

 

[Golom]

Just to clarify ...

Do you want a query that retrieves data where the [date] field has values in the range (for example) 1/1/2004 - 3/5/2004?

OR​

Do you want one field created in your query that has a value that appears as 1/1/2004 - 3/5/2004? If so, given that you have one date field per record, where do those two date values come from?

 

[djmousie]

I need the SECOND Scenario to appear in a report as scene in my example above. Like i need to present the time period in which my data starts and ends.

For instance I need to group by name, for instance. There is a name field and a date field, example.

Name Date

John 1/1/2001
John 2/1/2001
John 12/1/2002
Doe 1/1/2003
Doe 10/1/2004
Doe 11/1/2004


My results on the report should show this value:

John 1/1/2001 - 12/1/2002
Doe 1/1/2003 - 11/1/2004

Hope that diagram helps

 

[Golom]

OK
[blue][tt]
Select [Name],

(Format ( MIN([Date]), "m/d/yyyy") & " - " &
Format ( MAX([Date]), "m/d/yyyy")) As [Date Range]

From tbl

Group By [Name]
[/tt][/blue]

 

[djmousie]

Thanks, works great!

Kevin