Date() not functioning as expected

P

Phrank

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
 
P

PvdG42

Phrank said:
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
Click to expand...

Instead of Date(), have you tried Now(), which returns the current
date/time?
 
J

John W. Vinson

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.
Click to expand...

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

Phrank

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!
 
P

Phrank

Hi again,

Below is the SQL query. I also checked the system date from immediate
window, and it showed the correct date (copied and pasted here).

?Date()
3/1/2010

Also, no references are marked as missing.

It just doesn't make sense. There are 8 different milestones that are
'watched', and all of them have a Proposed date in which I have the
function entered, and they have an Actual date in which I have 'Is
Null'. If Access is looking at the appropriate system date, then why
would this query work with a manually entered date as opposed to a
system date using the Date() function? It just doesn't make sense.
Here is the SQL query:

SELECT Sheet1.StudyNumber, Sheet1.SD, Sheet1.PA, Sheet1.RW, Sheet1.RC,
Sheet1.Auditor, Sheet1.Stats, Sheet1.CO, Sheet1.SR, Sheet1.PR,
Sheet1.DTPR, Sheet1.CPA, Sheet1.AnalyticalCoordinator,
RTwithBIS.Stat1, RTwithBIS.Prog1, Sheet1.ADH, Sheet1.QAAnalytical,
Sheet1.PL, Sheet1.StudyComments, Sheet1.StudyRelatedInfo,
Sheet1.DataArchivedProposed, Sheet1.DataArchivedActual,
Sheet1.ToBISProposed, Sheet1.ToBISActual, Sheet1.RunStatsProposed,
Sheet1.RunStatsActual, Sheet1.ToPeerReviewerProposed,
Sheet1.ToPeerReviewerActual, Sheet1.TablesToSCProposed,
Sheet1.TablesToSCActual, Sheet1.CPTablesTextProposed,
Sheet1.CPTablesTextActual, Sheet1.InlifeToQAProposed,
Sheet1.InlifeToQAActual, Sheet1.InlifeFromQAProposed,
Sheet1.InlifeFromQAActual, Sheet1.PathTeraToQAProposed,
Sheet1.PathTeraToQAActual, Sheet1.PathTeraFromQAProposed,
Sheet1.PathTeraFromQAActual, Sheet1.ToSDForResultsProposed,
Sheet1.ToSDForResultsActual, Sheet1.ResultsFinishedProposed,
Sheet1.ResultsFinishedActual, Sheet1.ToQAForDrftRevProposed,
Sheet1.ToQADrftRevActual, Sheet1.FromQADrftRevProposed,
Sheet1.FromQADrftRevActual, Sheet1.MailDateProposed,
Sheet1.MailDateActual, Sheet1.PathToSCProposed, Sheet1.PathToSCActual,
Sheet1.AnalyticalArchiveDateProposed,
Sheet1.AnalyticalArchiveDateActual, Sheet1.PlasmaArchiveDateProposed,
Sheet1.PlasmaArchiveDateActual FROM Sheet1 INNER JOIN RTwithBIS ON
Sheet1.StudyNumber = RTwithBIS.StudyNumber WHERE (((Sheet1.RW) Like
"EEM" Or (Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or
(Sheet1.RW) Like "JAC" Or (Sheet1.RW) Like "JLC" Or
(Sheet1.RW) Like "AMO" Or (Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like
"KLS" Or (Sheet1.RW) Like "KAT") AND ((Sheet1.DataArchivedProposed)
Between #1/1/2001# And Date()) AND ((Sheet1.DataArchivedActual) Is
Null)) OR (((Sheet1.RW) Like "EEM" Or (Sheet1.RW) Like "RSB" Or
(Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like "JAC" Or (Sheet1.RW) Like
"JLC" Or (Sheet1.RW) Like "AMO" Or (Sheet1.RW) Like "RAR" Or
(Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like "KAT") AND
((Sheet1.ToPeerReviewerProposed) Between #1/1/2001# And Date()) AND
((Sheet1.ToPeerReviewerActual) Is Null)) OR (((Sheet1.RW) Like "EEM"
Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.ToSDForResultsProposed) Between #1/1/2001# And Date()) AND
((Sheet1.ToSDForResultsActual) Is Null)) OR (((Sheet1.RW) Like "EEM"
Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.ResultsFinishedProposed) Between #1/1/2001# And Date()) AND
((Sheet1.ResultsFinishedActual) Is Null)) OR (((Sheet1.RW) Like "EEM"
Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.ToQAForDrftRevProposed) Between #1/1/2001# And Date()) AND
((Sheet1.ToQADrftRevActual) Is Null)) OR (((Sheet1.RW) Like "EEM" Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.MailDateProposed) Between #1/1/2001# And Date()) AND
((Sheet1.MailDateActual) Is Null)) OR (((Sheet1.RW) Like "EEM" Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.AnalyticalArchiveDateProposed) Between "#1/1/2001#" And
Date()) AND ((Sheet1.AnalyticalArchiveDateActual) Is Null)) OR
(((Sheet1.RW) Like "EEM" Or (Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like
"DSC" Or
(Sheet1.RW) Like "JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like
"AMO" Or (Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or
(Sheet1.RW) Like "KAT") AND ((Sheet1.PlasmaArchiveDateProposed)
Between "#1/1/2001#"
And Date()) AND ((Sheet1.PlasmaArchiveDateActual) Is Null));

Thanks for any help.

Frank



Frank:

All I can think of is that if there are multiple milestones associated with a
project, and the query is restricted to those projects with any date within
the range, and one lies within the date range, even though one or more is
beyond it or Null, then the project per se would be returned by the query.
However, this would not at first sight explain the discrepancy between the
results when a literal date is entered as the parameter and those when the
Date() function is called. Post the SQL of the query here; that may give us
a clue.

Ken Sheridan
Stafford, England
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
Click to expand...
Click to expand...
 
P

Phrank

Thanks! I'll try this out tomorrow and will post back to let you know
how it goes. 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
it will solve the problem over the date I can't say, however.

SELECT Sheet1.StudyNumber, Sheet1.SD, Sheet1.PA, Sheet1.RW, Sheet1.RC,
Sheet1.Auditor, Sheet1.Stats, Sheet1.CO, Sheet1.SR, Sheet1.PR,
Sheet1.DTPR, Sheet1.CPA, Sheet1.AnalyticalCoordinator,
RTwithBIS.Stat1, RTwithBIS.Prog1, Sheet1.ADH, Sheet1.QAAnalytical,
Sheet1.PL, Sheet1.StudyComments, Sheet1.StudyRelatedInfo,
Sheet1.DataArchivedProposed, Sheet1.DataArchivedActual,
Sheet1.ToBISProposed, Sheet1.ToBISActual, Sheet1.RunStatsProposed,
Sheet1.RunStatsActual, Sheet1.ToPeerReviewerProposed,
Sheet1.ToPeerReviewerActual, Sheet1.TablesToSCProposed,
Sheet1.TablesToSCActual, Sheet1.CPTablesTextProposed,
Sheet1.CPTablesTextActual, Sheet1.InlifeToQAProposed,
Sheet1.InlifeToQAActual, Sheet1.InlifeFromQAProposed,
Sheet1.InlifeFromQAActual, Sheet1.PathTeraToQAProposed,
Sheet1.PathTeraToQAActual, Sheet1.PathTeraFromQAProposed,
Sheet1.PathTeraFromQAActual, Sheet1.ToSDForResultsProposed,
Sheet1.ToSDForResultsActual, Sheet1.ResultsFinishedProposed,
Sheet1.ResultsFinishedActual, Sheet1.ToQAForDrftRevProposed,
Sheet1.ToQADrftRevActual, Sheet1.FromQADrftRevProposed,
Sheet1.FromQADrftRevActual, Sheet1.MailDateProposed,
Sheet1.MailDateActual, Sheet1.PathToSCProposed, Sheet1.PathToSCActual,
Sheet1.AnalyticalArchiveDateProposed,
Sheet1.AnalyticalArchiveDateActual, Sheet1.PlasmaArchiveDateProposed,
Sheet1.PlasmaArchiveDateActual FROM Sheet1 INNER JOIN RTwithBIS ON
Sheet1.StudyNumber = RTwithBIS.StudyNumber
WHERE Sheet1.DataArchivedProposed BETWEEN #1/1/2001# AND DATE()
AND Sheet1.ToPeerReviewerProposed BETWEEN #1/1/2001# AND DATE()
AND Sheet1.ToSDForResultsProposed BETWEEN #1/1/2001# AND DATE()
AND Sheet1.ResultsFinishedProposed BETWEEN #1/1/2001# AND DATE()
AND Sheet1.ToQAForDrftRevProposed BETWEEN #1/1/2001# AND DATE()
AND Sheet1.MailDateProposed BETWEEN #1/1/2001# AND DATE()
AND Sheet1.AnalyticalArchiveDateProposed BETWEEN "#1/1/2001#" AND DATE()
AND Sheet1.PlasmaArchiveDateProposed BETWEEN "#1/1/2001#" AND DATE()
AND Sheet1.DataArchivedActual IS NULL
AND Sheet1.ToPeerReviewerActual IS NULL
AND Sheet1.ToSDForResultsActual IS NULL
AND Sheet1.ResultsFinishedActual IS NULL
AND Sheet1.ToQADrftRevActual) IS NULL
AND Sheet1.MailDateActual) IS NULL
AND Sheet1.AnalyticalArchiveDateActual IS NULL
AND Sheet1.PlasmaArchiveDateActual IS NULL
AND Sheet1.RW IN("EEM", "RSB", "DSC", "JAC" ,
"JLC", "AMO", "RAR","KLS" ,"KAT");

Ken Sheridan
Stafford, England
Hi again,

Below is the SQL query. I also checked the system date from immediate
window, and it showed the correct date (copied and pasted here).

?Date()
3/1/2010

Also, no references are marked as missing.

It just doesn't make sense. There are 8 different milestones that are
'watched', and all of them have a Proposed date in which I have the
function entered, and they have an Actual date in which I have 'Is
Null'. If Access is looking at the appropriate system date, then why
would this query work with a manually entered date as opposed to a
system date using the Date() function? It just doesn't make sense.
Here is the SQL query:

SELECT Sheet1.StudyNumber, Sheet1.SD, Sheet1.PA, Sheet1.RW, Sheet1.RC,
Sheet1.Auditor, Sheet1.Stats, Sheet1.CO, Sheet1.SR, Sheet1.PR,
Sheet1.DTPR, Sheet1.CPA, Sheet1.AnalyticalCoordinator,
RTwithBIS.Stat1, RTwithBIS.Prog1, Sheet1.ADH, Sheet1.QAAnalytical,
Sheet1.PL, Sheet1.StudyComments, Sheet1.StudyRelatedInfo,
Sheet1.DataArchivedProposed, Sheet1.DataArchivedActual,
Sheet1.ToBISProposed, Sheet1.ToBISActual, Sheet1.RunStatsProposed,
Sheet1.RunStatsActual, Sheet1.ToPeerReviewerProposed,
Sheet1.ToPeerReviewerActual, Sheet1.TablesToSCProposed,
Sheet1.TablesToSCActual, Sheet1.CPTablesTextProposed,
Sheet1.CPTablesTextActual, Sheet1.InlifeToQAProposed,
Sheet1.InlifeToQAActual, Sheet1.InlifeFromQAProposed,
Sheet1.InlifeFromQAActual, Sheet1.PathTeraToQAProposed,
Sheet1.PathTeraToQAActual, Sheet1.PathTeraFromQAProposed,
Sheet1.PathTeraFromQAActual, Sheet1.ToSDForResultsProposed,
Sheet1.ToSDForResultsActual, Sheet1.ResultsFinishedProposed,
Sheet1.ResultsFinishedActual, Sheet1.ToQAForDrftRevProposed,
Sheet1.ToQADrftRevActual, Sheet1.FromQADrftRevProposed,
Sheet1.FromQADrftRevActual, Sheet1.MailDateProposed,
Sheet1.MailDateActual, Sheet1.PathToSCProposed, Sheet1.PathToSCActual,
Sheet1.AnalyticalArchiveDateProposed,
Sheet1.AnalyticalArchiveDateActual, Sheet1.PlasmaArchiveDateProposed,
Sheet1.PlasmaArchiveDateActual FROM Sheet1 INNER JOIN RTwithBIS ON
Sheet1.StudyNumber = RTwithBIS.StudyNumber WHERE (((Sheet1.RW) Like
"EEM" Or (Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or
(Sheet1.RW) Like "JAC" Or (Sheet1.RW) Like "JLC" Or
(Sheet1.RW) Like "AMO" Or (Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like
"KLS" Or (Sheet1.RW) Like "KAT") AND ((Sheet1.DataArchivedProposed)
Between #1/1/2001# And Date()) AND ((Sheet1.DataArchivedActual) Is
Null)) OR (((Sheet1.RW) Like "EEM" Or (Sheet1.RW) Like "RSB" Or
(Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like "JAC" Or (Sheet1.RW) Like
"JLC" Or (Sheet1.RW) Like "AMO" Or (Sheet1.RW) Like "RAR" Or
(Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like "KAT") AND
((Sheet1.ToPeerReviewerProposed) Between #1/1/2001# And Date()) AND
((Sheet1.ToPeerReviewerActual) Is Null)) OR (((Sheet1.RW) Like "EEM"
Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.ToSDForResultsProposed) Between #1/1/2001# And Date()) AND
((Sheet1.ToSDForResultsActual) Is Null)) OR (((Sheet1.RW) Like "EEM"
Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.ResultsFinishedProposed) Between #1/1/2001# And Date()) AND
((Sheet1.ResultsFinishedActual) Is Null)) OR (((Sheet1.RW) Like "EEM"
Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.ToQAForDrftRevProposed) Between #1/1/2001# And Date()) AND
((Sheet1.ToQADrftRevActual) Is Null)) OR (((Sheet1.RW) Like "EEM" Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.MailDateProposed) Between #1/1/2001# And Date()) AND
((Sheet1.MailDateActual) Is Null)) OR (((Sheet1.RW) Like "EEM" Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.AnalyticalArchiveDateProposed) Between "#1/1/2001#" And
Date()) AND ((Sheet1.AnalyticalArchiveDateActual) Is Null)) OR
(((Sheet1.RW) Like "EEM" Or (Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like
"DSC" Or
(Sheet1.RW) Like "JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like
"AMO" Or (Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or
(Sheet1.RW) Like "KAT") AND ((Sheet1.PlasmaArchiveDateProposed)
Between "#1/1/2001#"
And Date()) AND ((Sheet1.PlasmaArchiveDateActual) Is Null));

Thanks for any help.

Frank
Frank:
Click to expand...
[quoted text clipped - 31 lines]
Frank
Click to expand...
Click to expand...
Click to expand...
 
P

Phrank

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
it will solve the problem over the date I can't say, however.

SELECT Sheet1.StudyNumber, Sheet1.SD, Sheet1.PA, Sheet1.RW, Sheet1.RC,
Sheet1.Auditor, Sheet1.Stats, Sheet1.CO, Sheet1.SR, Sheet1.PR,
Sheet1.DTPR, Sheet1.CPA, Sheet1.AnalyticalCoordinator,
RTwithBIS.Stat1, RTwithBIS.Prog1, Sheet1.ADH, Sheet1.QAAnalytical,
Sheet1.PL, Sheet1.StudyComments, Sheet1.StudyRelatedInfo,
Sheet1.DataArchivedProposed, Sheet1.DataArchivedActual,
Sheet1.ToBISProposed, Sheet1.ToBISActual, Sheet1.RunStatsProposed,
Sheet1.RunStatsActual, Sheet1.ToPeerReviewerProposed,
Sheet1.ToPeerReviewerActual, Sheet1.TablesToSCProposed,
Sheet1.TablesToSCActual, Sheet1.CPTablesTextProposed,
Sheet1.CPTablesTextActual, Sheet1.InlifeToQAProposed,
Sheet1.InlifeToQAActual, Sheet1.InlifeFromQAProposed,
Sheet1.InlifeFromQAActual, Sheet1.PathTeraToQAProposed,
Sheet1.PathTeraToQAActual, Sheet1.PathTeraFromQAProposed,
Sheet1.PathTeraFromQAActual, Sheet1.ToSDForResultsProposed,
Sheet1.ToSDForResultsActual, Sheet1.ResultsFinishedProposed,
Sheet1.ResultsFinishedActual, Sheet1.ToQAForDrftRevProposed,
Sheet1.ToQADrftRevActual, Sheet1.FromQADrftRevProposed,
Sheet1.FromQADrftRevActual, Sheet1.MailDateProposed,
Sheet1.MailDateActual, Sheet1.PathToSCProposed, Sheet1.PathToSCActual,
Sheet1.AnalyticalArchiveDateProposed,
Sheet1.AnalyticalArchiveDateActual, Sheet1.PlasmaArchiveDateProposed,
Sheet1.PlasmaArchiveDateActual FROM Sheet1 INNER JOIN RTwithBIS ON
Sheet1.StudyNumber = RTwithBIS.StudyNumber
WHERE Sheet1.DataArchivedProposed BETWEEN #1/1/2001# AND DATE()
AND Sheet1.ToPeerReviewerProposed BETWEEN #1/1/2001# AND DATE()
AND Sheet1.ToSDForResultsProposed BETWEEN #1/1/2001# AND DATE()
AND Sheet1.ResultsFinishedProposed BETWEEN #1/1/2001# AND DATE()
AND Sheet1.ToQAForDrftRevProposed BETWEEN #1/1/2001# AND DATE()
AND Sheet1.MailDateProposed BETWEEN #1/1/2001# AND DATE()
AND Sheet1.AnalyticalArchiveDateProposed BETWEEN "#1/1/2001#" AND DATE()
AND Sheet1.PlasmaArchiveDateProposed BETWEEN "#1/1/2001#" AND DATE()
AND Sheet1.DataArchivedActual IS NULL
AND Sheet1.ToPeerReviewerActual IS NULL
AND Sheet1.ToSDForResultsActual IS NULL
AND Sheet1.ResultsFinishedActual IS NULL
AND Sheet1.ToQADrftRevActual) IS NULL
AND Sheet1.MailDateActual) IS NULL
AND Sheet1.AnalyticalArchiveDateActual IS NULL
AND Sheet1.PlasmaArchiveDateActual IS NULL
AND Sheet1.RW IN("EEM", "RSB", "DSC", "JAC" ,
"JLC", "AMO", "RAR","KLS" ,"KAT");

Ken Sheridan
Stafford, England
Hi again,

Below is the SQL query. I also checked the system date from immediate
window, and it showed the correct date (copied and pasted here).

?Date()
3/1/2010

Also, no references are marked as missing.

It just doesn't make sense. There are 8 different milestones that are
'watched', and all of them have a Proposed date in which I have the
function entered, and they have an Actual date in which I have 'Is
Null'. If Access is looking at the appropriate system date, then why
would this query work with a manually entered date as opposed to a
system date using the Date() function? It just doesn't make sense.
Here is the SQL query:

SELECT Sheet1.StudyNumber, Sheet1.SD, Sheet1.PA, Sheet1.RW, Sheet1.RC,
Sheet1.Auditor, Sheet1.Stats, Sheet1.CO, Sheet1.SR, Sheet1.PR,
Sheet1.DTPR, Sheet1.CPA, Sheet1.AnalyticalCoordinator,
RTwithBIS.Stat1, RTwithBIS.Prog1, Sheet1.ADH, Sheet1.QAAnalytical,
Sheet1.PL, Sheet1.StudyComments, Sheet1.StudyRelatedInfo,
Sheet1.DataArchivedProposed, Sheet1.DataArchivedActual,
Sheet1.ToBISProposed, Sheet1.ToBISActual, Sheet1.RunStatsProposed,
Sheet1.RunStatsActual, Sheet1.ToPeerReviewerProposed,
Sheet1.ToPeerReviewerActual, Sheet1.TablesToSCProposed,
Sheet1.TablesToSCActual, Sheet1.CPTablesTextProposed,
Sheet1.CPTablesTextActual, Sheet1.InlifeToQAProposed,
Sheet1.InlifeToQAActual, Sheet1.InlifeFromQAProposed,
Sheet1.InlifeFromQAActual, Sheet1.PathTeraToQAProposed,
Sheet1.PathTeraToQAActual, Sheet1.PathTeraFromQAProposed,
Sheet1.PathTeraFromQAActual, Sheet1.ToSDForResultsProposed,
Sheet1.ToSDForResultsActual, Sheet1.ResultsFinishedProposed,
Sheet1.ResultsFinishedActual, Sheet1.ToQAForDrftRevProposed,
Sheet1.ToQADrftRevActual, Sheet1.FromQADrftRevProposed,
Sheet1.FromQADrftRevActual, Sheet1.MailDateProposed,
Sheet1.MailDateActual, Sheet1.PathToSCProposed, Sheet1.PathToSCActual,
Sheet1.AnalyticalArchiveDateProposed,
Sheet1.AnalyticalArchiveDateActual, Sheet1.PlasmaArchiveDateProposed,
Sheet1.PlasmaArchiveDateActual FROM Sheet1 INNER JOIN RTwithBIS ON
Sheet1.StudyNumber = RTwithBIS.StudyNumber WHERE (((Sheet1.RW) Like
"EEM" Or (Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or
(Sheet1.RW) Like "JAC" Or (Sheet1.RW) Like "JLC" Or
(Sheet1.RW) Like "AMO" Or (Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like
"KLS" Or (Sheet1.RW) Like "KAT") AND ((Sheet1.DataArchivedProposed)
Between #1/1/2001# And Date()) AND ((Sheet1.DataArchivedActual) Is
Null)) OR (((Sheet1.RW) Like "EEM" Or (Sheet1.RW) Like "RSB" Or
(Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like "JAC" Or (Sheet1.RW) Like
"JLC" Or (Sheet1.RW) Like "AMO" Or (Sheet1.RW) Like "RAR" Or
(Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like "KAT") AND
((Sheet1.ToPeerReviewerProposed) Between #1/1/2001# And Date()) AND
((Sheet1.ToPeerReviewerActual) Is Null)) OR (((Sheet1.RW) Like "EEM"
Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.ToSDForResultsProposed) Between #1/1/2001# And Date()) AND
((Sheet1.ToSDForResultsActual) Is Null)) OR (((Sheet1.RW) Like "EEM"
Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.ResultsFinishedProposed) Between #1/1/2001# And Date()) AND
((Sheet1.ResultsFinishedActual) Is Null)) OR (((Sheet1.RW) Like "EEM"
Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.ToQAForDrftRevProposed) Between #1/1/2001# And Date()) AND
((Sheet1.ToQADrftRevActual) Is Null)) OR (((Sheet1.RW) Like "EEM" Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.MailDateProposed) Between #1/1/2001# And Date()) AND
((Sheet1.MailDateActual) Is Null)) OR (((Sheet1.RW) Like "EEM" Or
(Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like "DSC" Or (Sheet1.RW) Like
"JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like "AMO" Or
(Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or (Sheet1.RW) Like
"KAT") AND
((Sheet1.AnalyticalArchiveDateProposed) Between "#1/1/2001#" And
Date()) AND ((Sheet1.AnalyticalArchiveDateActual) Is Null)) OR
(((Sheet1.RW) Like "EEM" Or (Sheet1.RW) Like "RSB" Or (Sheet1.RW) Like
"DSC" Or
(Sheet1.RW) Like "JAC" Or (Sheet1.RW) Like "JLC" Or (Sheet1.RW) Like
"AMO" Or (Sheet1.RW) Like "RAR" Or (Sheet1.RW) Like "KLS" Or
(Sheet1.RW) Like "KAT") AND ((Sheet1.PlasmaArchiveDateProposed)
Between "#1/1/2001#"
And Date()) AND ((Sheet1.PlasmaArchiveDateActual) Is Null));

Thanks for any help.

Frank
Frank:
Click to expand...
[quoted text clipped - 31 lines]
Frank
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

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
Click to expand...

Remove the quote marks from:
AND Sheet1.AnalyticalArchiveDateProposed BETWEEN "#1/1/2001#" AND DATE()
AND Sheet1.PlasmaArchiveDateProposed BETWEEN "#1/1/2001#" AND DATE()
Click to expand...


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
 
P

Phrank

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".
Click to expand...

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.

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 where:

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
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
Click to expand...
[quoted text clipped - 149 lines]
Frank
Click to expand...
Click to expand...
Click to expand...
 
P

Phrank

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");



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

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
Click to expand...
[quoted text clipped - 4 lines]
Frank
Click to expand...

Remove the quote marks from:
AND Sheet1.AnalyticalArchiveDateProposed BETWEEN "#1/1/2001#" AND DATE()
AND Sheet1.PlasmaArchiveDateProposed BETWEEN "#1/1/2001#" AND DATE()
Click to expand...

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()
Click to expand...
Click to expand...
Click to expand...
 
P

Phrank

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

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
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".
Click to expand...

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.
Click to expand...
[quoted text clipped - 33 lines]
Frank
Click to expand...
Click to expand...
Click to expand...
 
D

De Jager

Phrank said:
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!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date search query 0
Deriving an earlier date based on a later date 7
Conditional Formatting - Multiple Criterias 6
subquery to keep maximum date 10
Excel Worksheet_BeforeDelete code not working as expected. 1
Cycle Times 4
Converting Date to Julian 4
Grouping data from different columns 1

Top