Min Max Date

[Guest]

I have a date/time field formatted general date. data in the field is in the
form of:

mm/dd/yyyy hh:mm:ss ampm

In a query I am trying to use the min, max function of a totals query to
return the earlist and latest date and time.

July 10 is ignored and I am getting July 9 as a max, seems the time is
ignored completely.

How do I format the field to return the earliest and most recent data and
time value?

 

[Allen Browne]

The Max or Min value could depend on how you are grouping the data.

Switch the query to SQL View (View menu, from query design), and post the
SQL statement here.

 

[Guest]

I believe the General Date format only lets you put a date not a time (but
I'm happy to be proven wrong!) - how are you recording the date/time? I'd do
this using 2 fields - 1 for date and 1 for time then combine in a new field
in your query and find the Min/Max of that field.

Sheila
www.c-i-m-s.com
MS Iffuce training - London

 

[Guest]

SELECT Min(MedsScanned.AdmDate) AS MinOfAdmDate, Max(MedsScanned.AdmDate) AS
MaxOfAdmDate
FROM MedsScanned;

MinOfAdmDate MaxOfAdmDate
7/10/200 7/9/2006

I have data beginning with 7/1/06

--
Jeff C
Live Well .. Be Happy In All You Do

The Max or Min value could depend on how you are grouping the data.

Switch the query to SQL View (View menu, from query design), and post the
SQL statement here.

 

[Allen Browne]

Is there really a date in the year 200?

If not, this might be due to Access misunderstanding the data types, and
performing a string comparison. Is MedsScanned a table or a query?
- If a table, what is the data type of admDate field?
- If a query, is this a calculated date? If so, try typcasting it with
CVDate() as described here:
http://allenbrowne.com/ser-45.html

 

[ChrisM]

Is the field formatted as a DateTime field, or as a text field?

SELECT Min(MedsScanned.AdmDate) AS MinOfAdmDate, Max(MedsScanned.AdmDate)
AS
MaxOfAdmDate
FROM MedsScanned;

MinOfAdmDate MaxOfAdmDate
7/10/200 7/9/2006

I have data beginning with 7/1/06

 

[Ron2006]

I don't bet.....

But

I would be willing to bet here that the field is formated as text or is
coming in and is being loaded as a text field. (And it looks as if
possibly it has a field length of 8. - but that is a separate bet.)


Ron

 

[Guest]

Thank you for your help with this, All the data is being imported as text and
subsequently washed for analysis. There are some 30,000 records and for some
reason, three of them came in with dates 7/10/200. I appreciate all who
helped