DMin Function

Hi,

I'm having trouble with the DMin function in finding the next date in the
table that is greater than or is the same the date entered;

The dates in the table's field are
04/18/02
06/16/02
09/10/02
12/15/02
04/18/03
06/14/03
09/08/03
12/13/03

In the immediate pane I've typed
DMin("[DateEnd]", "tblPeriods", "[DateEnd]>=#"10/20/02#")
this returns 10/31/02 and this date doesn't exist in my table. I want it to
return 12/15/02 because this is the next date stored. When I change the date
to #11/20/02# it returns 11/30/02. How can I find the next date stored in
the table? I've tried the FindFirst method and it returns the same.

Stewart

If you open the table in design view, what type of field is DateEnd? Is it
Date/Time?

Go to the Windows Control Panel | Regional Settings, and change Short Date
so that it uses a 4-digit year (i.e. include the letter y 4 times). This may
help clarify what the dates actually are.

There is an extra quote mark in your 3rd argument. In the Immediate Window,
paste this:
? DMin("DateEnd", "tblPeriods", "DateEnd >= #10/20/2002#")

The fact that Access is returning the last day of the month is a hint that
something is wrong. If you are in New Zealand, I would expect your date
format for your interface to be dd/mm/yyyy. You are correctly supplying the
argument as mm/dd/yyyy, but there may be something screwy with how the dates
are appearing in your table.

--
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.

"Stewart Allen" <sagasu@ThisPartNotVailid.wave.co.nz> wrote in message
news:bsh349$jiu$1@news.wave.co.nz...[color=blue]
>
> I'm having trouble with the DMin function in finding the next date in the
> table that is greater than or is the same the date entered;
>
> The dates in the table's field are
> 04/18/02
> 06/16/02
> 09/10/02
> 12/15/02
> 04/18/03
> 06/14/03
> 09/08/03
> 12/13/03
>
> In the immediate pane I've typed
> DMin("[DateEnd]", "tblPeriods", "[DateEnd]>=#"10/20/02#")
> this returns 10/31/02 and this date doesn't exist in my table. I want it[/color]
to[color=blue]
> return 12/15/02 because this is the next date stored. When I change the[/color]
date[color=blue]
> to #11/20/02# it returns 11/30/02. How can I find the next date stored in
> the table? I've tried the FindFirst method and it returns the same.
>
> Stewart[/color]

Yes the DateEnd field is Date/Time and my Regional Setting show a 4 digit
year in NZ format. The results in the immediate pane do display as
dd/mm/yyyy but I just wrote the dates in my question as mm/dd/yy.

The only time it pulls the correct date is when the month is the same as the
month in the table i.e.
? DMin("DateEnd", "tblPeriods", "DateEnd >= #12/08/2002#")
will pull the 15 Dec 2002 record but
? DMin("DateEnd", "tblPeriods", "DateEnd >= #11/20/2002#")
will display 30 Nov 2002 and that record doesn't exist.

Any other ideas?
Stewart


"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:3fec4ed4$0$1730$5a62ac22@freenews.iinet.net.a u...[color=blue]
> If you open the table in design view, what type of field is DateEnd? Is it
> Date/Time?
>
> Go to the Windows Control Panel | Regional Settings, and change Short Date
> so that it uses a 4-digit year (i.e. include the letter y 4 times). This[/color]
may[color=blue]
> help clarify what the dates actually are.
>
> There is an extra quote mark in your 3rd argument. In the Immediate[/color]
Window,[color=blue]
> paste this:
> ? DMin("DateEnd", "tblPeriods", "DateEnd >= #10/20/2002#")
>
> The fact that Access is returning the last day of the month is a hint that
> something is wrong. If you are in New Zealand, I would expect your date
> format for your interface to be dd/mm/yyyy. You are correctly supplying[/color]
the[color=blue]
> argument as mm/dd/yyyy, but there may be something screwy with how the[/color]
dates[color=blue]
> are appearing in your table.
>
> --
> 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.
>
> "Stewart Allen" <sagasu@ThisPartNotVailid.wave.co.nz> wrote in message
> news:bsh349$jiu$1@news.wave.co.nz...[color=green]
> >
> > I'm having trouble with the DMin function in finding the next date in[/color][/color]
the[color=blue][color=green]
> > table that is greater than or is the same the date entered;
> >
> > The dates in the table's field are
> > 04/18/02
> > 06/16/02
> > 09/10/02
> > 12/15/02
> > 04/18/03
> > 06/14/03
> > 09/08/03
> > 12/13/03
> >
> > In the immediate pane I've typed
> > DMin("[DateEnd]", "tblPeriods", "[DateEnd]>=#"10/20/02#")
> > this returns 10/31/02 and this date doesn't exist in my table. I want it[/color]
> to[color=green]
> > return 12/15/02 because this is the next date stored. When I change the[/color]
> date[color=green]
> > to #11/20/02# it returns 11/30/02. How can I find the next date stored[/color][/color]
in[color=blue][color=green]
> > the table? I've tried the FindFirst method and it returns the same.
> >
> > Stewart[/color]
>
>[/color]

Stewart, that is reall strange behaviour. What version of Access? And what
service pack (Help | About should tell)? Is this the latest Office service
pack for your version of Office? And also of JET? The service pack info is
at support.microsoft.com

Haven't seen anything like that since the y2k issues that Access 2 had.

--
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.

"Stewart Allen" <sagasu@ThisPartNotVailid.wave.co.nz> wrote in message
news:bsi1bg$rbh$1@news.wave.co.nz...[color=blue]
> Yes the DateEnd field is Date/Time and my Regional Setting show a 4 digit
> year in NZ format. The results in the immediate pane do display as
> dd/mm/yyyy but I just wrote the dates in my question as mm/dd/yy.
>
> The only time it pulls the correct date is when the month is the same as[/color]
the[color=blue]
> month in the table i.e.
> ? DMin("DateEnd", "tblPeriods", "DateEnd >= #12/08/2002#")
> will pull the 15 Dec 2002 record but
> ? DMin("DateEnd", "tblPeriods", "DateEnd >= #11/20/2002#")
> will display 30 Nov 2002 and that record doesn't exist.
>
> Any other ideas?
> Stewart
>
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:3fec4ed4$0$1730$5a62ac22@freenews.iinet.net.a u...[color=green]
> > If you open the table in design view, what type of field is DateEnd? Is[/color][/color]
it[color=blue][color=green]
> > Date/Time?
> >
> > Go to the Windows Control Panel | Regional Settings, and change Short[/color][/color]
Date[color=blue][color=green]
> > so that it uses a 4-digit year (i.e. include the letter y 4 times). This[/color]
> may[color=green]
> > help clarify what the dates actually are.
> >
> > There is an extra quote mark in your 3rd argument. In the Immediate[/color]
> Window,[color=green]
> > paste this:
> > ? DMin("DateEnd", "tblPeriods", "DateEnd >= #10/20/2002#")
> >
> > The fact that Access is returning the last day of the month is a hint[/color][/color]
that[color=blue][color=green]
> > something is wrong. If you are in New Zealand, I would expect your date
> > format for your interface to be dd/mm/yyyy. You are correctly supplying[/color]
> the[color=green]
> > argument as mm/dd/yyyy, but there may be something screwy with how the[/color]
> dates[color=green]
> > are appearing in your table.
> >
> > --
> > 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.
> >
> > "Stewart Allen" <sagasu@ThisPartNotVailid.wave.co.nz> wrote in message
> > news:bsh349$jiu$1@news.wave.co.nz...[color=darkred]
> > >
> > > I'm having trouble with the DMin function in finding the next date in[/color][/color]
> the[color=green][color=darkred]
> > > table that is greater than or is the same the date entered;
> > >
> > > The dates in the table's field are
> > > 04/18/02
> > > 06/16/02
> > > 09/10/02
> > > 12/15/02
> > > 04/18/03
> > > 06/14/03
> > > 09/08/03
> > > 12/13/03
> > >
> > > In the immediate pane I've typed
> > > DMin("[DateEnd]", "tblPeriods", "[DateEnd]>=#"10/20/02#")
> > > this returns 10/31/02 and this date doesn't exist in my table. I want[/color][/color][/color]
it[color=blue][color=green]
> > to[color=darkred]
> > > return 12/15/02 because this is the next date stored. When I change[/color][/color][/color]
the[color=blue][color=green]
> > date[color=darkred]
> > > to #11/20/02# it returns 11/30/02. How can I find the next date stored[/color][/color]
> in[color=green][color=darkred]
> > > the table? I've tried the FindFirst method and it returns the same.
> > >
> > > Stewart[/color][/color][/color]

i suspect that dates are expected to be mm/dd/yy sequence in many functions
and SQL code . . so try that and ignore your regional settings

frowning here on this one
cheers paul g



"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:3fecea0e$0$1742$5a62ac22@freenews.iinet.net.a u...[color=blue]
> Stewart, that is reall strange behaviour. What version of Access? And what
> service pack (Help | About should tell)? Is this the latest Office service
> pack for your version of Office? And also of JET? The service pack info is
> at support.microsoft.com
>
> Haven't seen anything like that since the y2k issues that Access 2 had.
>
> --
> 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.
>
> "Stewart Allen" <sagasu@ThisPartNotVailid.wave.co.nz> wrote in message
> news:bsi1bg$rbh$1@news.wave.co.nz...[color=green]
> > Yes the DateEnd field is Date/Time and my Regional Setting show a 4[/color][/color]
digit[color=blue][color=green]
> > year in NZ format. The results in the immediate pane do display as
> > dd/mm/yyyy but I just wrote the dates in my question as mm/dd/yy.
> >
> > The only time it pulls the correct date is when the month is the same as[/color]
> the[color=green]
> > month in the table i.e.
> > ? DMin("DateEnd", "tblPeriods", "DateEnd >= #12/08/2002#")
> > will pull the 15 Dec 2002 record but
> > ? DMin("DateEnd", "tblPeriods", "DateEnd >= #11/20/2002#")
> > will display 30 Nov 2002 and that record doesn't exist.
> >
> > Any other ideas?
> > Stewart
> >
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > news:3fec4ed4$0$1730$5a62ac22@freenews.iinet.net.a u...[color=darkred]
> > > If you open the table in design view, what type of field is DateEnd?[/color][/color][/color]
Is[color=blue]
> it[color=green][color=darkred]
> > > Date/Time?
> > >
> > > Go to the Windows Control Panel | Regional Settings, and change Short[/color][/color]
> Date[color=green][color=darkred]
> > > so that it uses a 4-digit year (i.e. include the letter y 4 times).[/color][/color][/color]
This[color=blue][color=green]
> > may[color=darkred]
> > > help clarify what the dates actually are.
> > >
> > > There is an extra quote mark in your 3rd argument. In the Immediate[/color]
> > Window,[color=darkred]
> > > paste this:
> > > ? DMin("DateEnd", "tblPeriods", "DateEnd >= #10/20/2002#")
> > >
> > > The fact that Access is returning the last day of the month is a hint[/color][/color]
> that[color=green][color=darkred]
> > > something is wrong. If you are in New Zealand, I would expect your[/color][/color][/color]
date[color=blue][color=green][color=darkred]
> > > format for your interface to be dd/mm/yyyy. You are correctly[/color][/color][/color]
supplying[color=blue][color=green]
> > the[color=darkred]
> > > argument as mm/dd/yyyy, but there may be something screwy with how the[/color]
> > dates[color=darkred]
> > > are appearing in your table.
> > >
> > > --
> > > 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.
> > >
> > > "Stewart Allen" <sagasu@ThisPartNotVailid.wave.co.nz> wrote in message
> > > news:bsh349$jiu$1@news.wave.co.nz...
> > > >
> > > > I'm having trouble with the DMin function in finding the next date[/color][/color][/color]
in[color=blue][color=green]
> > the[color=darkred]
> > > > table that is greater than or is the same the date entered;
> > > >
> > > > The dates in the table's field are
> > > > 04/18/02
> > > > 06/16/02
> > > > 09/10/02
> > > > 12/15/02
> > > > 04/18/03
> > > > 06/14/03
> > > > 09/08/03
> > > > 12/13/03
> > > >
> > > > In the immediate pane I've typed
> > > > DMin("[DateEnd]", "tblPeriods", "[DateEnd]>=#"10/20/02#")
> > > > this returns 10/31/02 and this date doesn't exist in my table. I[/color][/color][/color]
want[color=blue]
> it[color=green][color=darkred]
> > > to
> > > > return 12/15/02 because this is the next date stored. When I change[/color][/color]
> the[color=green][color=darkred]
> > > date
> > > > to #11/20/02# it returns 11/30/02. How can I find the next date[/color][/color][/color]
stored[color=blue][color=green]
> > in[color=darkred]
> > > > the table? I've tried the FindFirst method and it returns the same.
> > > >
> > > > Stewart[/color][/color]
>
>[/color]

What Allen was mentioning in his first reply to my post was that I'm from
New Zealand and the dates returned should be in dd/mm/yy format. The dates
in my original post were in mm/dd/yy format so the reason for his first
reply. My PC settings are for my country which makes things a bit more
confusing when using dates in SQL and functions.

I've found the solution to my problem. I did have those dates that were
being returned in the table. I had another field called PeriodType which
only holds Monthly or Term. Each period has a start date and an end date
where the monthly starts and the first day of the month (but could be
changed by the user as long as it's consistent) and the term lasts longer
than a month (maybe 2 or 3) which can start at anytime and finish anytime.

While testing the DMin function in the Immediate window I had the Filter By
Selection turned on for the table just to view all the "Term" records so the
reason why I getting confused with the results. The solution was an extra
condition to the third argument of the DMin function
?DMin("DateEnd","tblPeriods","DateEnd>=#10/20/02# and PeriodType='Term'")

Stewart


"Kinetic - Paul G" <kinetic@alwaysonline.net.au> wrote in message
news:3fee8c8a@alwaysonline.net.au...[color=blue]
> i suspect that dates are expected to be mm/dd/yy sequence in many[/color]
functions[color=blue]
> and SQL code . . so try that and ignore your regional settings
>
> frowning here on this one
> cheers paul g
>[/color]

On Sun, 28 Dec 2003 18:44:22 +1100 in comp.databases.ms-access,
"Kinetic - Paul G" <kinetic@alwaysonline.net.au> wrote:
[color=blue]
>i suspect that dates are expected to be mm/dd/yy sequence in many functions
>and SQL code . . so try that and ignore your regional settings
>
>frowning here on this one
>cheers paul g[/color]

Confirmed: Literal dates in Access SQL should be MM/DD/YYYY, they
cannot be regional since the same SQL string would work differently in
different parts of the world. Comparitively, in SQL Server (or MSDE)
they should be in international format (yyyy-mm-dd), which makes for
some interesting times when upsizing.

the only way dates can be displayed with regional settings is when
they're stored as a number (offset from a base date), in the case of
SQL strings, they're there as strings so must be in one particular
format.

--
A)bort, R)etry, I)nfluence with large hammer.