Display Records Not Found

[Mr. Ed]

I have a table that tracks client contact. I want to create a report
that checks to see if contact has been made in a specific month
(passed parameter). If no contact has been made for that month, then
display the client's name and move on to next client. I can do this
in other languages, but I cannot seem to figure it out in Access 2003.

 

[Ken Snell \(MVP\)]

You could use a query similar to this as the RecordSource of the report
(note: this is a generic SQL statement, so change names to match your real
names):

SELECT Clients.*
FROM Clients
WHERE NOT EXISTS
(SELECT T.ClientID
FROM Clients AS T
WHERE T.ContactDate Between
DateSerial([Enter year:], [Enter month number:], 1)
And DateSerial([Enter year:], [Enter month number:]+1, 0));

 

[Mr. Ed]

Here is my query. It works if their are no contacts for the specified
month for any clients. But if there is one contact for one client, I
get no data. Help.

SELECT GH.HID, GH.HSA, GH.FacilityName, Monitoring.MonDate
FROM GH LEFT JOIN Monitoring ON GH.HID = Monitoring.HID
WHERE (((Exists (SELECT Monitoring.MonDate FROM Monitoring WHERE
Monitoring.MonDate Between [forms]![frmQueryParameters]!
[txtBeginningDate] And [forms]![frmQueryParameters]!
[txtEndingDate]))=False))
ORDER BY GH.HSA, GH.FacilityName;

You could use a query similar to this as the RecordSource of the report
(note: this is a generic SQL statement, so change names to match your real
names):

SELECT Clients.*
FROM Clients
WHERE NOT EXISTS
(SELECT T.ClientID
FROM Clients AS T
WHERE T.ContactDate Between
DateSerial([Enter year:], [Enter month number:], 1)
And DateSerial([Enter year:], [Enter month number:]+1, 0));

--

Ken Snell
<MS ACCESS MVP>

I have a table that tracks client contact. I want to create a report
that checks to see if contact has been made in a specific month
(passed parameter). If no contact has been made for that month, then
display the client's name and move on to next client. I can do this
in other languages, but I cannot seem to figure it out in Access 2003.- Hide quoted text -

- Show quoted text -

 

[Ken Snell \(MVP\)]

Try this:

SELECT GH.HID, GH.HSA, GH.FacilityName
FROM GH
WHERE NOT EXISTS
(SELECT Monitoring.MonDate FROM Monitoring
WHERE Monitoring.MonDate Between
[forms]![frmQueryParameters]![txtBeginningDate] And
[forms]![frmQueryParameters]![txtEndingDate] AND
Monitoring.HID = GH.HID)
ORDER BY GH.HSA, GH.FacilityName;

--

Ken Snell
<MS ACCESS MVP>

Here is my query. It works if their are no contacts for the specified
month for any clients. But if there is one contact for one client, I
get no data. Help.

SELECT GH.HID, GH.HSA, GH.FacilityName, Monitoring.MonDate
FROM GH LEFT JOIN Monitoring ON GH.HID = Monitoring.HID
WHERE (((Exists (SELECT Monitoring.MonDate FROM Monitoring WHERE
Monitoring.MonDate Between [forms]![frmQueryParameters]!
[txtBeginningDate] And [forms]![frmQueryParameters]!
[txtEndingDate]))=False))
ORDER BY GH.HSA, GH.FacilityName;

You could use a query similar to this as the RecordSource of the report
(note: this is a generic SQL statement, so change names to match your
real
names):

SELECT Clients.*
FROM Clients
WHERE NOT EXISTS
(SELECT T.ClientID
FROM Clients AS T
WHERE T.ContactDate Between
DateSerial([Enter year:], [Enter month number:], 1)
And DateSerial([Enter year:], [Enter month number:]+1, 0));

--

Ken Snell
<MS ACCESS MVP>

I have a table that tracks client contact. I want to create a report
that checks to see if contact has been made in a specific month
(passed parameter). If no contact has been made for that month, then
display the client's name and move on to next client. I can do this
in other languages, but I cannot seem to figure it out in Access 2003.-
Hide quoted text -

- Show quoted text -

 

[Mr. Ed]

That did the trick....thank you!

Try this:

SELECT GH.HID, GH.HSA, GH.FacilityName
FROM GH
WHERE NOT EXISTS
(SELECT Monitoring.MonDate FROM Monitoring
WHERE Monitoring.MonDate Between
[forms]![frmQueryParameters]![txtBeginningDate] And
[forms]![frmQueryParameters]![txtEndingDate] AND
Monitoring.HID = GH.HID)
ORDER BY GH.HSA, GH.FacilityName;

--

Ken Snell
<MS ACCESS MVP>

Here is my query. It works if their are no contacts for the specified
month for any clients. But if there is one contact for one client, I
get no data. Help.

SELECT GH.HID, GH.HSA, GH.FacilityName, Monitoring.MonDate
FROM GH LEFT JOIN Monitoring ON GH.HID = Monitoring.HID
WHERE (((Exists (SELECT Monitoring.MonDate FROM Monitoring WHERE
Monitoring.MonDate Between [forms]![frmQueryParameters]!
[txtBeginningDate] And [forms]![frmQueryParameters]!
[txtEndingDate]))=False))
ORDER BY GH.HSA, GH.FacilityName;

You could use a query similar to this as the RecordSource of the report
(note: this is a generic SQL statement, so change names to match your
real
names):
SELECT Clients.*
FROM Clients
WHERE NOT EXISTS
(SELECT T.ClientID
FROM Clients AS T
WHERE T.ContactDate Between
DateSerial([Enter year:], [Enter month number:], 1)
And DateSerial([Enter year:], [Enter month number:]+1, 0));
--
Ken Snell
<MS ACCESS MVP>

I have a table that tracks client contact. I want to create a report
that checks to see if contact has been made in a specific month
(passed parameter). If no contact has been made for that month, then
display the client's name and move on to next client. I can do this
in other languages, but I cannot seem to figure it out in Access 2003.-
Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -