Date() not functioning as expected

Tweaked it in what way? I think it might help if we forget the SQL for a
moment and examine the underlying logic of the desired restriction.

What is the logical basis of the query? As it stood after I rationalised the
WHERE clause, and you cleared out the parentheses I'd overlooked when
clearing up the garbage put in by Access, the basis was that a row would be
returned whe

1. Every 'proposed' date column position is between 1 January 2001 and the
current date, with none being Null, AND…
2. Every 'actual' date column position is Null, AND…
3. The value at the RW column position is any one of "EEM", "RSB", "DSC",
"JAC" , "JLC", "AMO", "RAR","KLS" ,"KAT".

so:

Is this the correct logical basis?
If not, what is the correct logical basis?
In what way has the 'tweaking' changed the logical basis from the above?

Ken Sheridan
Stafford, England

Phrank wrote:
I replaced the SQL with this, and as I tried to save it, I got error
messages saying there were extra parenthesis. When I got rid of those
it saved, but it didn't return ANY dates. One of the IT guys took a
look and tweaked it, and it started pulling dates again, but it again
pulled with more future dates that it doesn't pull when the manual
date is entered. It just doesn't make sense.

Frank

It looks to me like the WHERE clause could be rationalised considerably. I
hope I've understood the underlying logic correctly, but try this. Whether
[quoted text clipped - 149 lines]

Frank

--
Message posted via http://www.accessmonster.com

Doh! How did I miss those? Guess I can't put that overdue optician's
appointment off much longer!

Ken Sheridan
Stafford, England

John W. Vinson wrote:
I replaced the SQL with this, and as I tried to save it, I got error
messages saying there were extra parenthesis. When I got rid of those
[quoted text clipped - 4 lines]

Frank

Remove the quote marks from:

AND Sheet1.AnalyticalArchiveDateProposed BETWEEN "#1/1/2001#" AND DATE()
AND Sheet1.PlasmaArchiveDateProposed BETWEEN "#1/1/2001#" AND DATE()

They certainly should not be there if the DateProposed fields are Date/Time
fields (# is the date/time delimiter, " a text delimiter); and if the
DateProposed fields are Text fields, you *WILL* get future dates, because the
text string "2/1/2048" is in fact "between" the text strings "1/1/2001" and
"3/3/2010", since the character 2 is between 1 and 3. Not sure what the # in
the string will do but it can't be good!

If these are Text fields, try

AND CDate(Sheet1.AnalyticalArchiveDateProposed) BETWEEN #1/1/2001# AND DATE()
AND CDate(Sheet1.PlasmaArchiveDateProposed) BETWEEN #1/1/2001# AND DATE()


--
Message posted via http://www.accessmonster.com

1. Every 'proposed' date column position is between 1 January 2001 and the
current date, with none being Null, AND…
2. Every 'actual' date column position is Null, AND…
3. The value at the RW column position is any one of "EEM", "RSB", "DSC",
"JAC" , "JLC", "AMO", "RAR","KLS" ,"KAT".

Yes, this is the logical thinking. The strange part is that if I set
it up so that the user enters the current date, it returns the
appropriate responses. Whereas if I have it where the current Date()
is coded (negating the need for the user to enter anything),
additional future dates are pulled.

I will try Mr. Vinsons's suggestions tomorrow, and I will post back
what I come up with. Thanks for your continued interest, questions,
and suggestions.

Frank


On Thu, 04 Mar 2010 00:17:09 GMT, "KenSheridan via AccessMonster.com"
u51882@uwe wrote:

Tweaked it in what way? I think it might help if we forget the SQL for a
moment and examine the underlying logic of the desired restriction.

What is the logical basis of the query? As it stood after I rationalised the
WHERE clause, and you cleared out the parentheses I'd overlooked when
clearing up the garbage put in by Access, the basis was that a row would be
returned whe

1. Every 'proposed' date column position is between 1 January 2001 and the
current date, with none being Null, AND…
2. Every 'actual' date column position is Null, AND…
3. The value at the RW column position is any one of "EEM", "RSB", "DSC",
"JAC" , "JLC", "AMO", "RAR","KLS" ,"KAT".

so:

Is this the correct logical basis?
If not, what is the correct logical basis?
In what way has the 'tweaking' changed the logical basis from the above?

Ken Sheridan
Stafford, England

Phrank wrote:
I replaced the SQL with this, and as I tried to save it, I got error
messages saying there were extra parenthesis. When I got rid of those
it saved, but it didn't return ANY dates. One of the IT guys took a
look and tweaked it, and it started pulling dates again, but it again
pulled with more future dates that it doesn't pull when the manual
date is entered. It just doesn't make sense.

Frank

It looks to me like the WHERE clause could be rationalised considerably. I
hope I've understood the underlying logic correctly, but try this. Whether
[quoted text clipped - 149 lines]

Frank

In theory the query with the rationalised WHERE clause should work once
you've taken out the parentheses I'd missed, and the quotes characters which
John spotted. But, as you say, this doesn't account for the different
behaviour between the use of the Date() function and a literal date parameter.


You could try forcing a date value with something like:

CDATE(FORMAT(DATE(),"yyyy-mm-dd"))

or:

DATEVALUE(DATE())

But it shouldn't make any difference as the value returned in each case will
be the current date (or more strictly speaking the point of time at the start
of the current date), which is what the Date function itself returns.

We are not missing the obvious are we? The various 'date' columns in the
table are all of date/time data type I assume? A future date as text could
sort before the return value of the Date function. This would explain the
discrepancy as, unless you are declaring the parameter as DateTime in the
query the comparison with the literal date parameter would be a text
comparison if the field was a text data type. It would also explain why
there is no data type mismatch error arising from the quote characters around
the date literals, which I'd have expected with columns of date/time data
type.

Ken Sheridan
Stafford, England

Phrank wrote:
1. Every 'proposed' date column position is between 1 January 2001 and the
current date, with none being Null, ANDÂ…
2. Every 'actual' date column position is Null, ANDÂ…
3. The value at the RW column position is any one of "EEM", "RSB", "DSC",
"JAC" , "JLC", "AMO", "RAR","KLS" ,"KAT".

Yes, this is the logical thinking. The strange part is that if I set
it up so that the user enters the current date, it returns the
appropriate responses. Whereas if I have it where the current Date()
is coded (negating the need for the user to enter anything),
additional future dates are pulled.

I will try Mr. Vinsons's suggestions tomorrow, and I will post back
what I come up with. Thanks for your continued interest, questions,
and suggestions.

Frank

Tweaked it in what way? I think it might help if we forget the SQL for a
moment and examine the underlying logic of the desired restriction.
[quoted text clipped - 33 lines]

Frank

--
Message posted via http://www.accessmonster.com

Hi,

Below is the WHERE clause that works (basically, concatenating the
Date() function as a text field for a couple of the fields). The
trouble was that some of the fields were text fields instead of
date/time fields as they should have been. Apparently it was
inadvertantly set up that way as the database slowly evolved. Thank
you all for your help as I couldn't have gotten to this resolution
without it. Frank.

WHERE ((Sheet1.DataArchivedProposed Between #1/1/2001# And DATE() And
(Sheet1.DataArchivedActual Is Null Or Sheet1.DataArchivedActual = ''))
OR (Sheet1.ToPeerReviewerProposed Between #1/1/2001# And DATE() And
Sheet1.ToPeerReviewerActual Is Null) OR
(Sheet1.ToSDForResultsProposed Between #1/1/2001# And DATE() And
Sheet1.ToSDForResultsActual Is Null) OR
(Sheet1.ResultsFinishedProposed Between #1/1/2001# And DATE() And
Sheet1.ResultsFinishedActual Is Null) OR
(Sheet1.ToQAForDrftRevProposed Between #1/1/2001# And DATE() And
Sheet1.ToQADrftRevActual Is Null) OR (Sheet1.MailDateProposed Between
#1/1/2001# And DATE() And Sheet1.MailDateActual Is Null) OR
(Sheet1.AnalyticalArchiveDateProposed Between "#1/1/2001#" And "#" &
Month(Date()) & "/" & Day(Date()) & "/" & Year(Date()) & "#" And
Sheet1.AnalyticalArchiveDateActual Is Null) OR
(Sheet1.PlasmaArchiveDateProposed Between "#1/1/2001#" And "#" &
Month(Date()) & "/" & Day(Date()) & "/" & Year(Date()) & "#" And
Sheet1.PlasmaArchiveDateActual Is Null)) And Sheet1.RW In
("EEM","RSB","DSC","JAC","JLC","AMO","RAR","KLS"," KAT");



On Thu, 04 Mar 2010 00:23:19 GMT, "KenSheridan via AccessMonster.com"
u51882@uwe wrote:

Doh! How did I miss those? Guess I can't put that overdue optician's
appointment off much longer!

Ken Sheridan
Stafford, England

John W. Vinson wrote:
I replaced the SQL with this, and as I tried to save it, I got error
messages saying there were extra parenthesis. When I got rid of those
[quoted text clipped - 4 lines]

Frank

Remove the quote marks from:

AND Sheet1.AnalyticalArchiveDateProposed BETWEEN "#1/1/2001#" AND DATE()
AND Sheet1.PlasmaArchiveDateProposed BETWEEN "#1/1/2001#" AND DATE()

They certainly should not be there if the DateProposed fields are Date/Time
fields (# is the date/time delimiter, " a text delimiter); and if the
DateProposed fields are Text fields, you *WILL* get future dates, because the
text string "2/1/2048" is in fact "between" the text strings "1/1/2001" and
"3/3/2010", since the character 2 is between 1 and 3. Not sure what the # in
the string will do but it can't be good!

If these are Text fields, try

AND CDate(Sheet1.AnalyticalArchiveDateProposed) BETWEEN #1/1/2001# AND DATE()
AND CDate(Sheet1.PlasmaArchiveDateProposed) BETWEEN #1/1/2001# AND DATE()

Hi again,

You may be right. I spoke too soon yesterday when I posted the
'resolution', because I missed a few dates that were missing and
should have been there - I was so focused on the opposite side of the
equation, future dates showing up that shouldn't, that I didn't notice
current dates weren't showing up after I plugged in the 'fix'.

The fact of the matter is that, for some reason, some of the fields in
the overall database (including two in my query) are actually text
fields when they should be DateTime fields. I asked and was told that
it was because the database was originally setup by an inexperienced
person (who is no longer with the company). But, I was also told that
it couldn't just be changed at this point because of other issues and
errors that would return. So, I'm left to find a work around.
Frustrating.

I'll try the suggestions below and let you know what I come up with.
Thanks again.

Frank

On Fri, 05 Mar 2010 00:48:35 GMT, "KenSheridan via AccessMonster.com"
u51882@uwe wrote:

In theory the query with the rationalised WHERE clause should work once
you've taken out the parentheses I'd missed, and the quotes characters which
John spotted. But, as you say, this doesn't account for the different
behaviour between the use of the Date() function and a literal date parameter.


You could try forcing a date value with something like:

CDATE(FORMAT(DATE(),"yyyy-mm-dd"))

or:

DATEVALUE(DATE())

But it shouldn't make any difference as the value returned in each case will
be the current date (or more strictly speaking the point of time at the start
of the current date), which is what the Date function itself returns.

We are not missing the obvious are we? The various 'date' columns in the
table are all of date/time data type I assume? A future date as text could
sort before the return value of the Date function. This would explain the
discrepancy as, unless you are declaring the parameter as DateTime in the
query the comparison with the literal date parameter would be a text
comparison if the field was a text data type. It would also explain why
there is no data type mismatch error arising from the quote characters around
the date literals, which I'd have expected with columns of date/time data
type.

Ken Sheridan
Stafford, England

Phrank wrote:
1. Every 'proposed' date column position is between 1 January 2001 and the
current date, with none being Null, AND?
2. Every 'actual' date column position is Null, AND?
3. The value at the RW column position is any one of "EEM", "RSB", "DSC",
"JAC" , "JLC", "AMO", "RAR","KLS" ,"KAT".

Yes, this is the logical thinking. The strange part is that if I set
it up so that the user enters the current date, it returns the
appropriate responses. Whereas if I have it where the current Date()
is coded (negating the need for the user to enter anything),
additional future dates are pulled.

I will try Mr. Vinsons's suggestions tomorrow, and I will post back
what I come up with. Thanks for your continued interest, questions,
and suggestions.

Frank

Tweaked it in what way? I think it might help if we forget the SQL for a
moment and examine the underlying logic of the desired restriction.
[quoted text clipped - 33 lines]

Frank

Frank:

If the text dates in the table have been entered in the local regional date
format you should be able to convert them to a true date value in the query
with lines such as;

DATEVALUE(Sheet1.DataArchivedProposed) BETWEEN #1/1/2001# AND DATE()

and so on. This will work with the columns both which are true dates and
those which are text, so applying to all in the query should make sure of
catching everything. Using the DateValue function rather than the CDate
function also has the advantage that it will deal with any dates which might
inadvertently have had a non-zero time of day entered. This can all too
easily happen without your being aware of it; the inappropriate use of the
Now() function for entering defaults is a common cause.

Ken Sheridan
Stafford, England

KenSheridan wrote:
Tweaked it in what way? I think it might help if we forget the SQL for a
moment and examine the underlying logic of the desired restriction.

What is the logical basis of the query? As it stood after I rationalised the
WHERE clause, and you cleared out the parentheses I'd overlooked when
clearing up the garbage put in by Access, the basis was that a row would be
returned whe

1. Every 'proposed' date column position is between 1 January 2001 and the
current date, with none being Null, AND…
2. Every 'actual' date column position is Null, AND…
3. The value at the RW column position is any one of "EEM", "RSB", "DSC",
"JAC" , "JLC", "AMO", "RAR","KLS" ,"KAT".

so:

Is this the correct logical basis?
If not, what is the correct logical basis?
In what way has the 'tweaking' changed the logical basis from the above?

Ken Sheridan
Stafford, England

I replaced the SQL with this, and as I tried to save it, I got error
messages saying there were extra parenthesis. When I got rid of those
[quoted text clipped - 10 lines]

Frank

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201003/1

"Phrank" wrote in message
...
Thanks for the suggestions. I'll check them out when I go to work
today. Regarding the one question about trying Now() vs Date(), I did
that and it returned a LOT more future dates. I'll post back what I
find. Thanks!

On Sun, 28 Feb 2010 14:17:25 -0700, John W. Vinson
wrote:

On Sun, 28 Feb 2010 09:01:02 -0500, Phrank wrote:

I'm trying to get it where the user doesn't even have to enter the
current date by changing the query function to
Between #1/1/2001# And Date(). This does return a list, but it also
returns items that are in the future (e.g., if a milestone is supposed
to occurr next week (proposed date) and there's no actual date
entered. But it doesn't return ALL future dates.

I wonder if your system clock is off... or if you are perhaps using dates
in
the European dd/mm/yyyy format. A literal date 11/02/2010 may look like
February 11th but will be interpreted by an Access query as November 2 (in
the
future). Try typing ctrl-G to open the Immediate window and type

?Date()

in the window; does it show today's date (in your computer's regional
format)?

A second possibility is that you might have a field or a control named
Date on
your form or query, and it's getting confused about which date you mean.

The other place to look (though it usually gives compilation errors
instead)
is References. Again open VBA and select Tools... References. If any are
market MISSING, the Date() function in the VBA reference library may not
be
referenced correctly. Uncheck the missing reference; close VBA; open it
again
and recheck the reference and see if that changes it.

tytiiooopo

"Phrank" a écrit dans le message de groupe de discussion
: ...
Hi, I'm working with Access 2003 at work, and we have reports that
show projects with all associated milestones, and employees are
supposed to enter the date of completion when the milestone is met.

One of our reports is called 'Missing Dates', and it returns a list of
all projects with associated milestones ONLY if there is a proposed
milestone date between 1/1/2001 and the current date. The funtion is
currently set up as Between #1/1/2001# And [Enter Date mm/dd/yy]. This
works perfectly.

I'm trying to get it where the user doesn't even have to enter the
current date by changing the query function to
Between #1/1/2001# And Date(). This does return a list, but it also
returns items that are in the future (e.g., if a milestone is supposed
to occurr next week (proposed date) and there's no actual date
entered. But it doesn't return ALL future dates.

What is the difference between manually entering the date or have the
system date pulled into the function? Thanks.

Frank