By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,190 Members | 1,056 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,190 IT Pros & Developers. It's quick & easy.

DMin Function

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:bs**********@news.wave.co.nz...

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

Nov 12 '05 #2

P: n/a
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" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
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" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:bs**********@news.wave.co.nz...

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


Nov 12 '05 #3

P: n/a
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" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:bs**********@news.wave.co.nz...
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" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
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" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:bs**********@news.wave.co.nz...

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

Nov 12 '05 #4

P: n/a
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" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
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" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:bs**********@news.wave.co.nz...
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" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
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" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:bs**********@news.wave.co.nz...
>
> 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


Nov 12 '05 #5

P: n/a
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" <ki*****@alwaysonline.net.au> wrote in message
news:3f******@alwaysonline.net.au...
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


Nov 12 '05 #6

P: n/a
On Sun, 28 Dec 2003 18:44:22 +1100 in comp.databases.ms-access,
"Kinetic - Paul G" <ki*****@alwaysonline.net.au> wrote:
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


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.
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.