Date help please

[golfinray]

I asked this question yesterday, but everything I have tried still isn't
getting me what I want. I have 7 columns of dates that are dates that mark
stages in a project. Project ID# is my key. I need to know how many projects
were there that had no dates entered on Jan. 1, 2009. In other words, the
project had not advanced at all before 1/1/2009. There were no contracts, no
agreements, no plans drawn, no nothing for these 7 columns. I have tried is
null or <1/1/2009, I have tried Between 1/1/2000 and 1/1/2009 in all criteria
fields but I am just not getting the expected results. What else can I try, I
just can't get my head around it. I know we have 378 projects with about 170
that have never started and only 6 or 7 that have started since 1/1/2009 but
I just can't figure it out. Thanks, so much!!!!

 

[Duane Hookom]

I would first normalize the table structure so that each date created a new
record in a related table. This would convert your spreadsheet to a
relational database.

If you can't do that, consider creating a normalizing UNION query like:
=== quniStageDates ======
SELECT ProjectID, "Start" as Stage, [StartDate] as StageDate
FROM tblProject
UNION ALL
SELECT ProjectID, "Agreement", [AgreeDate]
FROM tblProject
UNION ALL
SELECT ProjectID, "Plans", [PlansDate]
FROM tblProject
UNION ALL
-- etc --
SELECT ProjectID, "Contract", [ContractDate]
FROM tblProject;

You can then create a query like:
SELECT ProjectID, Max(StageDate) as MaxDate
FROM quniStageDates
Having Max(StageDate)<#1/1/2009#;

 

[Noëlla Gabriël]

Hi,

without redesigning your database you could use the NZ function.
example:
select ProjectID from tblProjects where
NZ([Plandate], #12-31-2008#) < #1-1-2009#) and
NZ([ContractDate],#12-31-2008#) < #1-1-2009#)

..........

The nz function will replace the empty values with the given date, in this
cas 12-31-2008. If the filed contains a value, this date will be returned.

--
Kind regards
Noëlla

I would first normalize the table structure so that each date created a new
record in a related table. This would convert your spreadsheet to a
relational database.

If you can't do that, consider creating a normalizing UNION query like:
=== quniStageDates ======
SELECT ProjectID, "Start" as Stage, [StartDate] as StageDate
FROM tblProject
UNION ALL
SELECT ProjectID, "Agreement", [AgreeDate]
FROM tblProject
UNION ALL
SELECT ProjectID, "Plans", [PlansDate]
FROM tblProject
UNION ALL
-- etc --
SELECT ProjectID, "Contract", [ContractDate]
FROM tblProject;

You can then create a query like:
SELECT ProjectID, Max(StageDate) as MaxDate
FROM quniStageDates
Having Max(StageDate)<#1/1/2009#;
--
Duane Hookom
Microsoft Access MVP

I asked this question yesterday, but everything I have tried still isn't
getting me what I want. I have 7 columns of dates that are dates that mark
stages in a project. Project ID# is my key. I need to know how many projects
were there that had no dates entered on Jan. 1, 2009. In other words, the
project had not advanced at all before 1/1/2009. There were no contracts, no
agreements, no plans drawn, no nothing for these 7 columns. I have tried is
null or <1/1/2009, I have tried Between 1/1/2000 and 1/1/2009 in all criteria
fields but I am just not getting the expected results. What else can I try, I
just can't get my head around it. I know we have 378 projects with about 170
that have never started and only 6 or 7 that have started since 1/1/2009 but
I just can't figure it out. Thanks, so much!!!!

 

[John Spencer MVP]

SELECT [Project ID#]
FROM SomeTable
WHERE (Date1 is Null or Date1 < #1/1/2009)
AND (Date2 is Null or Date2 < #1/1/2009)
AND (Date3 is Null or Date3 < #1/1/2009)
AND (Date4 is Null or Date4 < #1/1/2009

If this does not work for you, post the SQL that you have and perhaps we can
suggest a modification. Duane Hookom is correct that your data structure is
incorrect and should be changed if at all possible.

I would make one modification to his query to catch those projects where all
the dates are null for the project are null

SELECT ProjectID, Max(StageDate) as MaxDate
FROM quniStageDates
Having Max(StageDate)<#1/1/2009# OR Max(StageDate) is Null
--

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[golfinray]

Thanks so much, guys! I will normalize this spreadsheet I imported and then
everything will be easier. But for now, with the boss wanting it right this
minute, you got me through. Thanks Again!!!