Odd result with Min of Date Range

[Guest]

First let me say thanks to all the MVPs out there. Reading through the
various postings in these newsgroups has been an invaluable education!

Here's an interesting one.

1) Having to select the Min and Max Date Range on Hotel Registrations. I
was going to start just with the Min so I ran the following SQL. Without the
grouping, a quick scan of 30 records shows only two dates, 4/4/05 and 4/5/05.
When I group and choose Min on the CheckIn field, the result is 4/12/05. I
verified the data in the table to make sure I wasn't missing something - all
that is there is 4/4/05 and 4/5/05. Where is 4/12/05 coming from?

2) My CheckOut dates range from 4/8/05 to 4/10/05. The Max function
properly gives me 4/10/05 on the grouping. Assuming I figure out the problem
to #1 above, can I run BOTH the Min and Max functions at the same time to
give me 4/4/05 and 4/10/05, respectively?

SELECT EventsRegs.EventID, Min(EventsRegsHotels.CheckIn) AS MinOfCheckIn
FROM EventsRegs INNER JOIN EventsRegsHotels ON EventsRegs.EventRegID =
EventsRegsHotels.RegID
GROUP BY EventsRegs.EventID
HAVING (((EventsRegs.EventID)=157));

Thanks!

 

[OfficeDev18 via AccessMonster.com]

1 - Your fields are probably not date type but string, so "4/12" comes before
"4/4" or "4/5". Try looking for the Min(Cdate(CheckInDate)) (or Max).

2 - Yes you can co-mingle Min and Max aggregate functions in the same query.

HTH,

Sam

 

[John Spencer]

I would double check to make sure about the earliest Checkin Date. Also,
CHECK the field type and make sure you are dealing with a DateTime field and
not a text field.

SELECT EventsRegs.EventID,
EventsRegsHotels.CheckIn
FROM EventsRegs INNER JOIN EventsRegsHotels
ON EventsRegs.EventRegID = EventsRegsHotels.RegID
WHERE (((EventsRegs.EventID)=157))
ORDER BY CheckIn;

Yes, you can get both Max and Min at the same time. I would make one change
to the query and change the Having clause to a Where Clause.

SELECT EventsRegs.EventID,
Min(EventsRegsHotels.CheckIn) AS MinOfCheckIn,
Max(EventsRegsHotels.CheckOut) as MaxCheckout
FROM EventsRegs INNER JOIN EventsRegsHotels
ON EventsRegs.EventRegID = EventsRegsHotels.RegID
WHERE EventsRegs.EventID = 157
GROUP BY EventsRegs.EventID

 

[Guest]

Thanks, Sam. I tried a couple other filters to verify my data and have
discovered that I have a lazy person in the office - rather than creating new
registration records, old records for an individual were being reused. gasp!
Time to increase security measures......

After fixing the records, Min and Max worked. Good to know about the CDate
function - I will have other uses for that.