Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Min Max Date

Discussion in 'Information Technology' started by Jeff C, Jul 28, 2006.

Tags:
  1. Jeff C

    Jeff C
    Expand Collapse
    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?
    --
    Jeff C
    Live Well .. Be Happy In All You Do
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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 - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    news:C41D1695-7632-4541-9C0F-96708A14B569@microsoft.com...
    >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?
    > --
    > Jeff C
    > Live Well .. Be Happy In All You Do
     
  4. Sheila D

    Sheila D
    Expand Collapse
    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

    "Jeff C" wrote:

    > 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?
    > --
    > Jeff C
    > Live Well .. Be Happy In All You Do
     
  5. Jeff C

    Jeff C
    Expand Collapse
    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


    "Allen Browne" wrote:

    > 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 - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    > news:C41D1695-7632-4541-9C0F-96708A14B569@microsoft.com...
    > >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?
    > > --
    > > Jeff C
    > > Live Well .. Be Happy In All You Do

    >
    >
    >
     
  6. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    news:199A7B2A-AA28-402E-834B-7D3BD6C877E8@microsoft.com...
    > 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
    >
    >
    > "Allen Browne" wrote:
    >
    >> 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.
    >>
    >> "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    >> news:C41D1695-7632-4541-9C0F-96708A14B569@microsoft.com...
    >> >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?
    >> > --
    >> > Jeff C
    >> > Live Well .. Be Happy In All You Do
     
  7. ChrisM

    ChrisM
    Expand Collapse
    Guest

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

    "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    news:199A7B2A-AA28-402E-834B-7D3BD6C877E8@microsoft.com...
    > 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
    >
    >
    > "Allen Browne" wrote:
    >
    >> 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 - Microsoft MVP. Perth, Western Australia.
    >> Tips for Access users - http://allenbrowne.com/tips.html
    >> Reply to group, rather than allenbrowne at mvps dot org.
    >>
    >> "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    >> news:C41D1695-7632-4541-9C0F-96708A14B569@microsoft.com...
    >> >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?
    >> > --
    >> > Jeff C
    >> > Live Well .. Be Happy In All You Do

    >>
    >>
    >>
     
  8. Ron2006

    Ron2006
    Expand Collapse
    Guest

    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
     
  9. Jeff C

    Jeff C
    Expand Collapse
    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
    --
    Jeff C
    Live Well .. Be Happy In All You Do


    "Allen Browne" wrote:

    > 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
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    > news:199A7B2A-AA28-402E-834B-7D3BD6C877E8@microsoft.com...
    > > 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
    > >
    > >
    > > "Allen Browne" wrote:
    > >
    > >> 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.
    > >>
    > >> "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    > >> news:C41D1695-7632-4541-9C0F-96708A14B569@microsoft.com...
    > >> >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?
    > >> > --
    > >> > Jeff C
    > >> > Live Well .. Be Happy In All You Do

    >
    >
    >
     

Share This Page