Here is the query.... If possible, I would like to get the most current date
pulled from each record minus the details ....
Thank you,
SELECT Establishment.NCNAME, Establishment.CNAME, Establishment.CSNO,
Establishment.CASELOAD, Establishment.[2NDATTEMPT],
Establishment.SERVED, Establishment.INITIAL, Establishment.NCPPCKGRTN,
Establishment.[2NDATTEMPT], Establishment.PRIVPROCESSOR,
Establishment.NONCOOP, Establishment.[3DATTEMPT], Establishment.RETURNED,
Establishment.UNEXECUTED, Establishment.RTNPRIVPROCESS
FROM Establishment
WHERE (((Establishment.SERVED) Is Null))
ORDER BY Establishment.CASELOAD, IIf([3dATTEMPT] Is Not
Null,[2ndATTEMPT],IIf([2ndATTEMPT] Is Not Null,[2ndattempt],
IIf([2ndattempt] Is Not Null,[INITIAL])))));
KARL DEWEY said:
Post your complete query SQL.
Are you wanting all of the below in the header --
Name Date
John D 15 Jan 08
Jim 07 Aug 08
Mary 15 Apr 08
Then what is to be in the detail?
--
KARL DEWEY
Build a little - Test a little
:
Here is a small sample when I run the query that will reveal all records with
the current date.....
Name Initial 2d Attempt 3d Attempt
John D 25 Nov 07 15 Jan 08
Jim 07 Aug 08
Mary 04 Feb 08 03 Mar 08 15 Apr 08
Now here is what I would like the report to show with a different label as a
header....
Name Date
John D 15 Jan 08
Jim 07 Aug 08
Mary 15 Apr 08
I hope this helps on what I'm trying to ask....
Thank you,
:
Now ....is there way I can generate a report that will only show a record
with the most current date?
I thought that was what you were doing here. Are you saying you have
multiple records using multiple fields for the dates?
Post some sample records.
--
KARL DEWEY
Build a little - Test a little
:
Thank you for clarifying it.
The query looks great. Now ....is there way I can generate a report that
will only show a record with the most current date? What I see is that there
are many records in the query that has a date on each field. In other words,
some records have a history of attempts (others don't...etc) and I only want
to see a report with a each record that has the most current date. The dates
are scattered between each field.
Thank you so much for your help!
:
As posted it is meant to be pasted in an empty Field row space in the query
design view grid.
If you want to use in SQL view then add to the select statement like this ---
... , IIF([3d Attempt] Is Not Null, [3d Attempt], IIF([2d Attempt]
Is Not Null, [2d Attempt], [Initial])) AS [Report Date]
Preceed it with a comma as above.
--
KARL DEWEY
Build a little - Test a little
:
Hello:
I'm not exactly sure where to place this statement in my query. I opened
the SQL View and entered " IIF([3d Attempt] Is Not Null, [3d Attempt],
IIF([2d Attempt]
Is Not Null, [2d Attempt], [Initial]))". Afterwards, I tried to run the
query and got an error msg "Characters found after end of SQL Statement"
Thank you,
:
Use nested IIF in your query, testing 3d Attempt then 2d Attempt, and Initial
as default.
Report Date: IIF([3d Attempt] Is Not Null, [3d Attempt], IIF([2d Attempt]
Is Not Null, [2d Attempt], [Initial]))
--
KARL DEWEY
Build a little - Test a little
:
I have 3 fields on a table each with a date entered. The field names are ...
Initial
2d Attempt
3d Attempt
Question: Is there a way I can generate a report using the most current
date from the three different fields (Initial, 2d Attempt, & 3d Attempt) on
the table? For example, if the "2d Attempt" field is blank, then I would use
the "Initial" field. Or it could pick the most current date from any of the
three records listed and use that data as part of the report?
Thank you,
