I've created a query on which to base a report showing a three-year audit schedule. Some business areas (AAA, etc.) will be audited once in the three-year period, others twice, others not at all (thus the blank
fields).

Can I get the blanks at the bottom of each column without the dates being sorted backward (Dec-Jan)? Also, how do I eliminate the records that contain no date? For records with both 2004 and 2006 dates, I only need the 2004 dates in order, and obviously, the business areas are not involved in the sort at all.

Following are the current results:

...........2004........2005.........2006

AAA.....(blank).....(blank)......(blank)
BBB.....(blank).....(blank)......(blank)
CCC....(blank).....(blank)......(blank)
DDD....(blank).....(blank)......(blank)
EEE......(blank).....(blank)......01/01/06
FFF.......(blank).....(blank) .....03/01/06
GGG.....(blank).....(blank)......07/01/06
HHH......(blank).....(blank).....12/01/06
III........(blank).... 01/01/05....(blank)
JJJ........(blank).....03/01/05....(blank)
KKK......(blank).... 07/01/05 ...(blank)
LLL. ....(blank).... 12/01/05....(blank)
MMM....01/01/04...(blank).......01/01/06
NNN.....03/01/04....(blank)......(blank)
OOO....07/01/04....(blank)........07/01/06
PPP......12/01/04....(blank).......12/01/06

What I'm looking for is this:

MMM....01/01/04...(blank).......01/01/06
NNN.....03/01/04....(blank)......(blank)
OOO....07/01/04....(blank)........07/01/06
PPP......12/01/04....(blank).......12/01/06
III........(blank).... 01/01/05....(blank)
JJJ........(blank).....03/01/05....(blank)
KKK......(blank).... 07/01/05 ...(blank)
LLL. ....(blank).... 12/01/05....(blank)
EEE......(blank).....(blank)......01/01/06
FFF.......(blank).....(blank) .....03/01/06
GGG.....(blank).....(blank)......07/01/06
HHH......(blank).....(blank).....12/01/06

with these records eliminated:
AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)

Any help would be greatly appreciated!

WHERE (YOUR criteria)

AND (((not isnull(2004) AND not isnull(2005) AND not isnull(2006))))

Order BY 2004 , 2005, 2006 desc



Expl.


AND ((not isnull(2004) AND not isnull(2005) AND not isnull(2006)))
This will exclude the "rows" that have 2004 - 2006 blank
Be sure to use the extra ( and ) minimal twice



Order BY 2004 , 2005, 2006 desc
this wil order your result descending USE asc if you want to revers it
It will first look at the 2004 then 2005 and then 2006
If you wanna switch just put them in an other order

Thanks for your quick response!

Where do I enter this code -- in the query fields, in the report?? Sorry, I'm new at this level of complexity.


Originally posted by Marvels
WHERE (YOUR criteria)

AND (((not isnull(2004) AND not isnull(2005) AND not isnull(2006))))

Order BY 2004 , 2005, 2006 desc



Expl.


AND ((not isnull(2004) AND not isnull(2005) AND not isnull(2006)))
This will exclude the "rows" that have 2004 - 2006 blank
Be sure to use the extra ( and ) minimal twice



Order BY 2004 , 2005, 2006 desc
this wil order your result descending USE asc if you want to revers it
It will first look at the 2004 then 2005 and then 2006
If you wanna switch just put them in an other order [/SIZE][/QUOTE]

if your report is based on the query youl have to open the query in design mode then open the query in SQL mode (in the upper left corner just under file - do not click on file)


Be sure to delete the ; if you want to put the code after this sign


SO youll get


SELECT A, B, C, D

From ((A.A
LEFT JOIN B.A ON A.A = B.A)
RIGHT JOIN C.B. ON B.B =C.B)
INNER JOIN D.C. ON C.C =C.C

WHERE (Year = 2000) AND (((not isnull(2004) AND not isnull(2005) AND not isnull(2006))))

ORDER BY 2004 , 2005, 2006 desc

Save your query

Here's what the SQL view currently has for this query:

SELECT RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity, RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006
FROM RiskAnalysisDetails
ORDER BY RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity, RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006;

Does your code need to go after this, or replace it? Also, I'm not sure how the A, B, C, D fits in.

Thanks for your patience!

SELECT RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity, RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006


FROM RiskAnalysisDetails

WHERE (((not isnull(RiskAnalysisDetails.Date2004)
AND not isnull(RiskAnalysisDetails.Date2005)
AND not isnull(RiskAnalysisDetails.Date2006))))


ORDER BY RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006 asc

First, thanks for your response on the decimal question -- your solution worked (of course)!

Back to the query:

I copied/pasted your code over the existing code. I saved the query, then ran it, but no results were produced. When I looked at the SQL view again, it had changed to:

SELECT RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity, RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006

FROM RiskAnalysisDetails

WHERE (((IsNull([RiskAnalysisDetails].[Date2004]))=False) AND ((IsNull([RiskAnalysisDetails].[Date2005]))=False) AND ((IsNull([RiskAnalysisDetails].[Date2006]))=False))
ORDER BY RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006;

...and back in Design view, three columns containing the Where statements had been added to the end.

Where'd I go wrong this time?? Thanks!!!

Where you say Blank is

the date 00/00/00 00:00:00
or is the field realy blank
or is the value NULL

The field is really blank.

Does this do what you want :



SELECT RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity,
RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006


FROM RiskAnalysisDetails

WHERE ((((RiskAnalysisDetails.Date2004 <> "")
AND (RiskAnalysisDetails.Date2005 <> "")
AND (RiskAnalysisDetails.Date2006 <> ""))))


ORDER BY RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006 asc

I get an error message: Data type mismatch in criteria expression.

If this doen't work can you zip your table and attach it here
PS only that 1 table

SELECT RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity, RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006


FROM RiskAnalysisDetails

WHERE ((not isnull(RiskAnalysisDetails.Date2004))
AND (not isnull(RiskAnalysisDetails.Date2005))
AND (not isnull(RiskAnalysisDetails.Date2006)))


ORDER BY RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006 asc

Sorry, the query returned no results. I've attached the file -- the zipped db, since I don't know how to send only a portion of it. Please don't laugh too hard at my structure and design; I've still much to learn.

The table is RiskAnalysisDetails and the query is 04-06AuditScheduleQuerybyDate. Also, the report 04-06AuditScheduleRpt shows what I'm trying to fix; i.e., the blanks are at the top of each column instead of at the bottom and the records with no dates are not being excluded.

Thanks again for your patience and help!!!

Jaycee

BTW, I'm also doing battle with the report 04-06AuditScheduleIntervals, trying to get the intervals between audits to calculate correctly in the report, as they do in the form Risk Analysis/AuditSchedule. Another guru was helping me with it, but I think he's given up on me. If the solution comes to you without taking too much of your time, please enlighten me! Thanks!

Could you change the AND into OR

So

SELECT RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity, RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006
FROM RiskAnalysisDetails
WHERE ((not isnull(RiskAnalysisDetails.Date2004))
OR (not isnull(RiskAnalysisDetails.Date2005))
OR (not isnull(RiskAnalysisDetails.Date2006)))
ORDER BY RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006 asc

BTW i cant open the .mdb
Coud you copy a range from your table into xls or something

Thx

The last changes produced results and excluded the records with no dates, but the columns are still upside down. I've attached a Word document showing the table and query results. Hope this helps!

Thanks much!