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 6 5009
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
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
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
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
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: domeceo |
last post by:
can anyone tell me why I cannot pass values in a setTimeout function
whenever I use this function it says "menu is undefined" after th
alert.
function imgOff(menu, num) {
if (document.images) {...
|
by: phil_gg04 |
last post by:
Dear Javascript Experts,
Opera seems to have different ideas about the visibility of Javascript
functions than other browsers. For example, if I have this code:
if (1==2) {
function...
|
by: laredotornado |
last post by:
Hello, I am looking for a cross-browser way (Firefox 1+, IE 5.5+) to
have my Javascript function execute from the BODY's "onload" method,
but if there is already an onload method defined, I would...
|
by: turtle |
last post by:
I need to write an update query that will return the earliest date to a
table based on the data of a different table.
I have a labor table that looks like this
TableLabor
JobCode WorkORder ...
|
by: Gompie |
last post by:
Why does the function DMin("Abs(!- #" & &
"#)";"SomeTable") not work properly with datefields.
It always finds the closest difference with a later date only, if an earlier
date in the table is...
|
by: sushil |
last post by:
+1 #include<stdio.h>
+2 #include <stdlib.h>
+3 typedef struct
+4 {
+5 unsigned int PID;
+6 unsigned int CID;
+7 } T_ID;
+8
+9 typedef unsigned int (*T_HANDLER)(void);
+10
|
by: Olov Johansson |
last post by:
I just found out that JavaScript 1.5 (I tested this with Firefox 1.0.7
and Konqueror 3.5) has support not only for standard function
definitions, function expressions (lambdas) and Function...
|
by: Ed Marzan |
last post by:
Greetings,
I have a query that returns varying prices for the same item in the
following manner.
Item1 Price1
Item1 Price2
Item1 Price3
Item1 Price4
|
by: maccount |
last post by:
I'm having a simple problem (or at least I believe it to be), one that has stumped me. I have a simple text box in my Access Report, page header.
Dmin("ppID", "qry001")
This report is based on...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |