max date between dates

[smiley0q0]

i have a begin date and an end date, sometimes they are only one day apart and sometimes they are a month apart,

how do i find the "max" date between the begin date and the end date?

Thanks,
Smiley :-0

 

[Psychpt]

DateDiff

 

[smiley0q0]

can you expand a little bit please?

 

[Psychpt]

if you want to calculate days then it would be:
Format("d", BeginDate, EndDate)

 

[smiley0q0]

okay, let me try and explain a little bit better. i want to find the latest date between my begin and end date.

ex.

9/12/01
9/13/01
9/14/01

i only want 9/14/01 to pull up.

 

[ErikZ]

Where do you keep your dates? If each date has it's own row, I'd have Access search for your end date, then go back a row to get the date before the end date.

 

[tlbroadbent]

Try a query like this.

Select Max(Date_Col) As MaxDate
From Table_Name
Where Date_Col Between [Start Date] And [End Date]
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[smiley0q0]

my dates are all in the same column. i tried
Select Max(Date_Col) As MaxDate
From Table_Name
Where Date_Col Between [Start Date] And [End Date]

but it wouldn't let me use a "where" it automatically changed it to "Max", so it brought up all the dates between my begin and end date.
i have a table called "tblDates" that has the begin and end date. but my bulk of data comes from a table called "tblData" the name of the date column is "Date"

any other ideas?

 

[tlbroadbent]

[ol][li]Recommendation: Change the name of the column named Date to something else. It is bad procedure to use a reserved word as a column name.

[li]When you create the query in query designer, you'll select MAX in one column and Where in another column and use [Date] as the field name in both.
[tt]
Field: MaxDate: Max([Date]) [Date]
Table: tblData
Sort:
Show: <checked> <not checked>
Criteria: Between [Start Date] And [End Date][/tt]

[li]Do you want to use the range of dates from one table to get the Max date within the range from the other table? Or will the user be entering the parameters? If you use a table to match, do you want to return a match for every range in the range table or just one value?

Example: Return max date for each range in range table

SELECT Max(tblData.[Date]) AS MaxDate,
tblDates.[Start Date], tblDates.[End Date]
FROM tblData, tblDates
WHERE (((tblData.[Date]) Between tblDates.[Start Date] And tblDates.[End Date]))
GROUP BY tblDates.[Start Date], tblDates.[End Date];[/ol] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[smiley0q0]

Thanks tlbroadbent, that worked great. i was doing the same thing earlier, but i forgot i had another date field, so that was making all my dates pull in, instead of just my max. but it's working now. I i really appreciate all the help everyone has given me.

Smiley (-: