Date() not functioning as expected

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
0
Phrank
2/28/2010 2:01:02 PM
access 16762 articles. 3 followers. Follow

19 Replies
907 Views

Similar Articles

[PageSpeed] 20

"Phrank" <pbeal@hotmail.com> wrote in message 
news:s8tko55tmu3up64fugdk8lgfb0pqjm98io@4ax.com...
> 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

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

0
PvdG42
2/28/2010 3:20:34 PM
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

Phrank wrote:
>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

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

0
KenSheridan
2/28/2010 4:05:01 PM
On Sun, 28 Feb 2010 09:01:02 -0500, Phrank <pbeal@hotmail.com> 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.
-- 

             John W. Vinson [MVP]
0
John
2/28/2010 9:17:25 PM
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
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>On Sun, 28 Feb 2010 09:01:02 -0500, Phrank <pbeal@hotmail.com> 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.
0
Phrank
3/1/2010 11:38:57 AM
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



On Sun, 28 Feb 2010 16:05:01 GMT, "KenSheridan via AccessMonster.com"
<u51882@uwe> wrote:

>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
>
>Phrank wrote:
>>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
0
Phrank
3/1/2010 10:31:57 PM
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

Phrank wrote:
>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:
>>
>[quoted text clipped - 31 lines]
>>>
>>>Frank

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

0
KenSheridan
3/2/2010 12:05:22 AM
Thanks!  I'll try this out tomorrow and will post back to let you know
how it goes.  Frank

On Tue, 02 Mar 2010 00:05:22 GMT, "KenSheridan via AccessMonster.com"
<u51882@uwe> wrote:

>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
>
>Phrank wrote:
>>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:
>>>
>>[quoted text clipped - 31 lines]
>>>>
>>>>Frank
0
Phrank
3/2/2010 2:57:45 AM
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

On Tue, 02 Mar 2010 00:05:22 GMT, "KenSheridan via AccessMonster.com"
<u51882@uwe> wrote:

>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
>
>Phrank wrote:
>>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:
>>>
>>[quoted text clipped - 31 lines]
>>>>
>>>>Frank
0
Phrank
3/3/2010 11:40:58 PM
On Wed, 03 Mar 2010 18:40:58 -0500, Phrank <pbeal@hotmail.com> 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

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()


-- 

             John W. Vinson [MVP]
0
John
3/3/2010 11:49:47 PM
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

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

0
KenSheridan
3/4/2010 12:17:09 AM
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

0
KenSheridan
3/4/2010 12:23:19 AM
>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 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
>
>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
0
Phrank
3/4/2010 10:51:18 PM
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

0
KenSheridan
3/5/2010 12:48:35 AM
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()
>>
0
Phrank
3/7/2010 12:18:16 PM
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
0
Phrank
3/9/2010 3:23:24 AM
On Mon, 08 Mar 2010 22:23:24 -0500, Phrank <pbeal@hotmail.com> wrote:

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

Try using a calculated field CDate([thistextfield]) to coerce all of the dates
into Date/Time field types; although "2/1/2010" will not sort chronologically,
CDate("2/1/2010") will.
-- 

             John W. Vinson [MVP]
0
John
3/9/2010 4:10:03 AM
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 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
>[quoted text clipped - 10 lines]
>>>>>>
>>>>>>Frank

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

0
KenSheridan
3/9/2010 11:08:25 AM
"Phrank" <pbeal@hotmail.com> wrote in message 
news:qm9no5doj9f3m3ife5q4amqcutbmb1ludt@4ax.com...
> 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
> <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
>
>>On Sun, 28 Feb 2010 09:01:02 -0500, Phrank <pbeal@hotmail.com> 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. 

0
De
3/13/2010 5:58:28 PM
tytiiooopo

"Phrank" <pbeal@hotmail.com> a �crit dans le message de groupe de discussion 
: s8tko55tmu3up64fugdk8lgfb0pqjm98io@4ax.com...
> 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 

0
joelgeraldine
3/17/2010 1:35:24 PM
Reply:

Similar Artilces:

Slow Excel graph with date axis
I've just created a simple chart in Excel 2007 SP2 with five columns and over 12k rows. In the first row is set the date and in die second to fifth row are stored data like this: 12.01.10 3434 3474 3734 3434 12.01.10 3413 3434 3458 3474 13.01.10 3534 3462 3432 3494 13.01.10 3437 3454 3434 3534 If I mark the data and add a simple line graph, the Excel is going to be very slow... You've to wait a minute till the graph is displayed. As soon as I switch the x-axis type from date (automatically) to text the Excel is running fast. In Excel 2000 I do not have this Problem....

calculate back dates
Hi is there a way to figure out a date 9 months previous to a date? eg. I have an install date of 12/02/10, is there a way or a formula that can count back 9 months from that install date? (they need to be weekdays) thanks very much Just use the Date function, as in: =Date(year(a1),month(a1)-9,day(a1)) Regards, Fred "sonia" <sonia@discussions.microsoft.com> wrote in message news:63DDCF12-C093-4532-B71C-2FB9112A47B8@microsoft.com... > Hi > > is there a way to figure out a date 9 months previous to a date? > > eg. I have an install date ...

with if function
Some one great MVP solved my problem with this function =(DATE(YEAR(E146),MONTH(E146)+3-MOD(MONTH(E146)-1,3),1)) but i just want to do more with this function. How it can be display blank when e146 does not have any date. when this function found date on particular cell then it calculate next 3 months dat. Thanks in advance Rao Ratan Singh =IF(ISBLANK(E146,"",formula) Jerry Rao Ratan Singh wrote: > Some one great MVP solved my problem with this function > =(DATE(YEAR(E146),MONTH(E146)+3-MOD(MONTH(E146)-1,3),1)) > > but i just want to do more with this function...

crm 3 email template, date & time
how can I add the current (system) date & time to a crm 3 email template? ...

Calling from the dll, a function written in the MFC application
I have a regular MFC dll that exports some global functions to my MFC applications. Everything goes fine but now I need to call in the oposite direction - from the dll, a function written in the MFC application. Can it be done and how? Thanks in advance Mani one of the ways is by using a pointer to a function i think; have in the dll a init function; call from exe the init function passing the function as a param and in the dll store the pointer into a variable you can reach later "mani" <mani@malloc.com> wrote in message news:ueuGuiI2EHA.2568@TK2MSFTNGP10.phx.gbl... >...

if function 12-14-09
if I have 500 rows and several col and lets say col B is office cities IE Dallas, Fort Worth, Austin, Houston and the col F has sales totals Is there a way I can subtotal the col f in setter work sheet IE if col b = Dallas, subtotal everthing that = dallas by col F You want a Pivot Table. You'll love them once you get used to them. Google pivot tables for more information. Regards, Fred "Dylan lotz" <Dylan lotz @discussions.microsoft.com> wrote in message news:AFDEAF5E-B191-45EC-8569-634F92552859@microsoft.com... > if I have 500 rows and ...

IE7 on XP Not Functioning
I just updated my XP with every update under the sun. I also removed norton with a removal tool. I updated to IE7 and cannot get anything at all to load. It opens, the diagnostic tool says there is no connection issue, but it won't work. I did disable add ons and did a reset and they didn't help. What next? I do not want to go to IE8 and IE6 has no tabs, which I love. Help! [Crosspost to IE General newsgroup] Always state your full Windows version (e.g., WinXP SP3; WinXP 64-bit SP2) when posting in a forum or newsgroup. Please do so in your next reply. Did the recentl...

Duplicate field required, not functioning
I know I'm going to hear that this is a bad design, but please bear with me. I have a field in a sub form called Qty (quantity) that is saved to a detail table. In addition, I need that same value saved to another "temporary" table that is used for posting, and then cleared. I have the second Qty field default set to = the value of the first Qty field. But no matter what I enter into the first Qty field, the second one remains unchanged. That is, if I set the first Qty field to default to a value on the primary form, then both fields are set automatically to that value. B...

IsNull Not Working For Date
Hello, I have a query established to provide data that populates a report. For those fields that the user did not enter in any information, I want the report to show the text "Not Entered." I have successfully created the correct code in the query to handle all information except my birthday field, which is in date format. Here is the query code: Birthday: IIf(IsNull([EeBirthday]),"Not Entered",[EeBirthday]) EeBirthday is the field is should pull if it is not null. When I select datasheet view from the design view, nothing happens. No errors, highlights, nada. Whe...

anyone else seen problem using LEN() function in Runtime?
HI. I have an applicaiton that for one of the combo boxes has a query that uses the Len() function to shorten the description length for sorting the list. Eg: Right([Partdescription],(Len([PartDescription])-11)) This was working fine - however having loaded the AccessRT.msp Runtime pre-release patch from microsoft to fix the SaveAsPDF addin problem the len function doesnt work. I have even tried to generate a standalone app that has a Form with a button and the VB code has a Msgbox Len([Me]![ErrorBox]) Under the runtime with the pre-release patch the msgbox does not display. Under ...

date time field for time
I need a field to store time to spend, like a task takes 5 minutes 20 seconds to finish, but not hours and minutes like clock time. I want to 10' 30" plus 5' 25" is 15' 55", but not 15' 55" later. For example, I have 2 tasks to complete one is 10' 30" and another is 5' 25". I need the sum is 2 tasks take 15' 55", but not get real clock time. Are there any field to store and calculate the infromaiton? Is it DateTime field a good choice? Your information is great appreciated, DateTime is not the appropriate choice: it...

Left function #2
Hi, I'm moved from Excel 2000 to 2003 and now I find that the Left an Right functions are not working correctly. They work OK if I type the in one cell at a time but if I use copy/paste it just reproduces th same result in every cell. This happens even if I use past special/values as well! Any ideas appreciate -- Message posted from http://www.ExcelForum.com Hi tools / options / calculation - ensure it is set to automatic. Cheers JulieD "dillonke >" <<dillonke.1bjvdg@excelforum-nospam.com> wrote in message news:dillonke.1bjvdg@excelforum-nospam.com... > Hi...

calling function as part of criteria
hey everyone this is driving me nuts i can't figure out the correct syntax basically I have a main form, where there are 2 subforms, the main one is subfrm the secondary is subfrmflt. i have a few different forms that i call up in the subform area, with queries for their recordsource. there is a box on each of these forms that the query refers to (Text60). here's the catch: since the subforms could posibly be opened in either subfrm or subfrmflt, I need the query to be able to figure out which one is open and fill that into the path: Forms!frmMain!subfrm!Text60 I have a funct...

Hot to call C# function inside XSLT ?
Hi, Is it possible to call C# function inside XSLT ? Thanks, Max answer : http://msdn.microsoft.com/msdnmag/issues/02/03/xml/ "Maxim Kazitov" <mvkazit@tut.by> wrote in message news:uVgMnxo5DHA.504@TK2MSFTNGP11.phx.gbl... > Hi, > > Is it possible to call C# function inside XSLT ? > > Thanks, > Max > > ...

Sorting dates #2
I got some advice before but I am still having problems sorting dates. Using Excel 2003. I have a file of about 1000 records over a time span of 1750 to 1921. When I sort by what I think is the date the order result is as follows... 1900 to 1921 followed by 1750 to 1899. I can fix by moving a set of records to the bottom but that doesn't help if the sort parameter is not the primary one. Can I convert the date column to anything else to solve my problem. I am not terribly interested in time between dates etc.. Thanks Sl�n Gerry I assume your dates are text. Give us examples ...

Calculating no of work days between two dates
Please advise how to calculate the number of work days (excludin weekeends and holidays) between two date -- fluffywhitedo ----------------------------------------------------------------------- fluffywhitedoh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3528 View this thread: http://www.excelforum.com/showthread.php?threadid=55075 =NETWORKDAYS(start_date,end_date,holidays) holidays is a range of holiday dates. You will need to have the Analysis Toolpak installed, Tools>Addins, and cheek it. -- HTH Bob Phillips (replace somewhere in email address ...

Nesting more than 7 IF functions
I have a list with over 20,000 rows in it. Each cell in column D has a number between 1 and 200,000. I would like to set up a formula in column E that will show a letter based on what range the number is in. For example, if the number is between 1 and 500, show "A", if it is between 501 and 1,000, show "B". If there were only 7 number ranges I would use a nested if: =IF(AND(D1>1,D1<500),"A",(IF(AND(D1>500,D1<1000)),"B",(IF....... and so on... The problem is that I have 13 number ranges that I need to create codes for. Any tips? For ...

Add ins and user defined functions
I have an ErlandC Add in I am using on my system. I have a workbook that uses the user defined functions in the worksheet. When I send the file via e-mail to anyone else to use, once they save the file - they get a #NAME? error anywhere the user defined functions have been used. These people also have add ins loaded on their PCs. If I go into each formula and redo the formula on their PCs, it will work. How can I make this work without redoing each formula each time? Thanks Vicki My guess is that excel is remembering the location of your addin and looks for it on the other pc an...

Stupid Pivot Table question
I've spent an hour now pulling my hair out on what I think should have a simple solution... I have one column of dates and times, another of numerical values. I've created a pivot table, grouped by year, month, and day, with the average of the value calculated for each day. I need to get this data out of the pivot table and into regular cells for another program, with one column of dates and a second of values averaged by day. The problem is that the Pivot Table only seems to store the dates as text, with the year and month values only appearing once. I.e., the first row of my PT has...

DATE FORMAT IN OUTLOOK
Does anyone know a way to get Outlook to display the day of the year w/in the Calendar function? IE, Jan 1 would be 001, Jan 2 would be 002, etc? If the answer is a plugin or addon, that's fine. Thanks in advance, Chuck ...

toolbar, date, services, errors, paste, minimized system restore doesn't work
I have a XP system that the toolbar does not show open programs, services do not seem to load at boot, the date is off by one month, I cannot paste anything that I copy, and system restore does not work. All of these issues started at the same time a few days ago. Has anyone else seen this group of errors? I am trying to resolve and do not know if it is a system update gone bad, or if maybe it is a viruse/malware issue. Please let me know if you had had these problems together and what you did to fix them. There is a very good chance that you are seeing the effects of a hi...

Sum is between two dates
I have a spreadsheet that I use to calculate my sales. It is a good solution for totaling what my sales for the year but I also want a monthly breakdown. I need a formula that will return the total sales for January. In the example below the answer would be $25,000. Here is an examply of my list: Candidate Company Start Date Fee Jim Company A 1/1/2004 $18,000 Dan Company B 1/17/2004 $7,000 Sara Company A 5/24/2004 $9,600 Tina Company C 7/20/2004 $8,250 Carol Company D 9/2/2004 $7,500 Dave Company A 9/20/2004 $11,600 Thanks in advance for any assistance. Dave David If t...

Downloaded transactions with wrong date...
I'm using Money 2004 Deluxe. I recently paid my Citi credit card account from my checking account using epay. The two payments were scheduled for 1/1/2004. A few days after New Years, Money automatically downloaded the transactions from Citi Cards. The downloaded transactions indicated that the payments were posted to my Citi Card account on 01/01/2003. The year was wrong - the payments actually posted on 01/01/2004! As such, Money would not automatically match these transactions. I manually matched the transactions, but unfortunately, Money kept Citi's date instead of m...

IF Function 12-02-09
Can anyone help me get this to work? =IF(AND(COUNT(C20:C53,F20:F53,I20:I53)>0,IF(ISNUMBER(H51,A54,D54,G54))),SUM(B20:B53,E20:E53,H20:H51)/2,"NOT BALANCED !") Thanks! What are you trying to do with this: >IF(ISNUMBER(H51,A54,D54,G54)) Do you want to test *every* cell or *any* cell? Why not just use the COUNT function: For every cell it would be: COUNT(H51,A54,D54,G54)=4 For any cell it would be: COUNT(H51,A54,D54,G54)>0 -- Biff Microsoft Excel MVP "Gerard Sanchez" <geepeeoneREMOVEME@gmail.com> wrote in message ...

Every Sat Date Population
How can I get it to populate the dates for every Saturday starting from 12/31/05 until the end of the year? To Excel, dates are just numbers... Try this: A1: 12/31/2005 A2:=+A1+7 Copy that formula down as far as you need and format the cells as dates. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Roberta" wrote: > How can I get it to populate the dates for every Saturday starting from > 12/31/05 until the end of the year? > > > > Ron gave you the easiest way, but an alternative would be, with 12/31/05 in A1, highlight A1 and as f...