Try pasting this in. I fixed a couple of problems:
PARAMETERS [Enter Beginning Date] DateTime, [Enter Ending Date] DateTime;
SELECT audAssets.AudDate,
audAssets.audType,
audAssets.Status,
audAssets_1.Status
FROM audAssets INNER JOIN audAssets AS audAssets_1
ON audAssets.AudDate = audAssets_1.AudDate
WHERE audAssets.AudDate
Between [Enter Beginning Date] And [Enter Ending Date]
AND audAssets.Status <> [audAssets_1].[Status]
AND audAssets.Status Like "Req*"
AND audAssets_1.Status Like "Req*";
I'm assuming heavily that AudDate is an actual date/time datatype.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Arlene said:
Jerry,
Thanks for reply to e-mail,
Please find below the SQL statement
SELECT audAssets.AudDate, audAssets.audType, audAssets.Status,
audAssets_1.Status
FROM audAssets INNER JOIN audAssets AS audAssets_1 ON audAssets.AudDate =
audAssets_1.AudDate
WHERE (((audAssets.AudDate) Between [Enter Beginning Date] And [Enter Ending
Date]) AND ((audAssets.Status)<>[Aud_table_1].[Status] Like "Req*") AND
((audAssets_1.Status) Like "Req*"));
Many Thanks
Arlene
Jerry Whittle said:
Hi Arlene,
As there seems to be some differences in table names with underscores and
such, I need to see the actual SQL statement. Two of them in fact: one simple
on on the table that returns records and the one that does not.
To show us the SQL, open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
Jerry, Thanks for replying to my e-mail,
Please can you check that I am entering this correctly as I keep coming up
with a blank sheet.
AUD AS Table AUD AS1 Table
Aud Date Aud Date Joined the two Properties
Field: Aud Date
Table: Aud Asset
Criteria: Between [Enter Beginning Date] And [Enter Ending Date]
Field: Status
Table: Aud Asset
Criteria: <> [Aud_table_1].[Status] Like Req*
Field: Status
Table: Aud Asset
Criteria: Like Req*
Thanks
:
SELECT Aud_table.[Aud Date],
Aud_table.[Aud Type],
Aud_table.Status
FROM Aud_table INNER JOIN Aud_table AS Aud_table_1
ON Aud_table.[Aud Date] = Aud_table_1.[Aud Date]
WHERE Aud_table.[Aud Date]
Between [Enter Beginning Date] And [Enter Ending Date]
AND Aud_table.[Status] <> [Aud_table_1].[Status]
AND Aud_table.[Status] Like "Req*"
AND Aud_table_1.[Status] Like "Req*" ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
Hi, Please can you help me as I have run a query on a aud table and defined
it by the date and status but how do I run the query to only specify the
changes that have been done for eg it has been changed from Req Trans ab to
Req trans DU.
In my query table I have
Aud Date
Citeria: Between [Enter Beginning Date] And [Enter Ending Date]
Aud Type
Name
Status: Like "Req*"
This what I get when I run the query
09/08/2007 10:22:13 EditFrom Req Trans Ab
09/08/2007 10:22:13 EditFrom Req Trans Ab
14/08/2007 10:22:13 EditFrom Req Trans Ab
14/08/2007 10:22:13 Edit to Req Trans Du
This is what I would like it to be to see only the changes that have been
done.
Date audtype Status
14/08/2007 10:22:13 EditFrom Req Trans Ab
14/08/2007 10:22:13 Edit to Req Trans Du
