DateDiff returning incorrect years

[mewins]

I am trying to use DateDiff to autocalculate some dates, but it returns dates
for past years that I don’t need. The query is listed below. What I want to
happen is for the user to enter in the date range and have the query
automatically find clients in particular contracts who will, within the
specified date range, be hitting their 30th, 90th, 120th, 180th day, or 8th
month, of being in that contract (based on that client’s arrival date, or
date of asylum if the client has that date listed). I am getting the
intervals right in general, but for clients who were enrolled in past years
come up in the query. I could fix this by only allowing clients who are
marked as being active show up, but sometimes clients go inactive and I need
those clients to show up in the specified dates. Sorry if it’s a little
confusing. Any ideas? Thanks!

SELECT [firstname] & " " & [lastname] AS [Full Name], tblContract.CotrName,
tblContract.ContrStart, tblContract.ContrEnd, tblMainClientInfo.CaseMgr,
tblMainClientInfo.AsylumDate, tblMainClientInfo.ArrivDate, IIf([asylumdate]
Is Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",30,[asylumdate])) AS [30
Day Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",90,[asylumdate])) AS [90 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",120,[asylumdate])) AS [120 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",180,[asylumdate])) AS [180 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("m",8,[asylumdate])) AS [8 Month
Report Date]
FROM tblMainClientInfo INNER JOIN tblContract ON tblMainClientInfo.ClientID
= tblContract.ClientID
WHERE (((tblContract.CotrName)="rcm" Or (tblContract.CotrName)="mg") AND
((tblContract.ContrStart) Is Not Null) AND ((tblContract.ContrEnd) Is Null))
ORDER BY [firstname] & " " & [lastname];

 

[Jeff Boyce]

While I see the portion of your WHERE clause that looks at what appears to
be Start and End dates, where is the part that excludes those "clients who
were enrolled in past years"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

I am trying to use DateDiff to autocalculate some dates,
Apparently you posted the wrong query as there is no DateDiff in your posting.

 

[mewins]

Sorry I didn't make this more clear. I have a form that staff use to enter
in the date parameters. If they want to see which clients are reaching
30-90-etc days in the contract for August 09, the start and end dates that
they enter in the form are 8/1/09 and 8/30/09. I want the query to only find
clients who will be reaching their 30th-90th-etc day in the contract for
August 2009, but the query will pop up with clients who reached that length
of time back in August 2008. Does that make more sense?

Apparently you posted the wrong query as there is no DateDiff in your posting.

--
Build a little, test a little.

I am trying to use DateDiff to autocalculate some dates, but it returns dates
for past years that I don’t need. The query is listed below. What I want to
happen is for the user to enter in the date range and have the query
automatically find clients in particular contracts who will, within the
specified date range, be hitting their 30th, 90th, 120th, 180th day, or 8th
month, of being in that contract (based on that client’s arrival date, or
date of asylum if the client has that date listed). I am getting the
intervals right in general, but for clients who were enrolled in past years
come up in the query. I could fix this by only allowing clients who are
marked as being active show up, but sometimes clients go inactive and I need
those clients to show up in the specified dates. Sorry if it’s a little
confusing. Any ideas? Thanks!

SELECT [firstname] & " " & [lastname] AS [Full Name], tblContract.CotrName,
tblContract.ContrStart, tblContract.ContrEnd, tblMainClientInfo.CaseMgr,
tblMainClientInfo.AsylumDate, tblMainClientInfo.ArrivDate, IIf([asylumdate]
Is Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",30,[asylumdate])) AS [30
Day Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",90,[asylumdate])) AS [90 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",120,[asylumdate])) AS [120 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",180,[asylumdate])) AS [180 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("m",8,[asylumdate])) AS [8 Month
Report Date]
FROM tblMainClientInfo INNER JOIN tblContract ON tblMainClientInfo.ClientID
= tblContract.ClientID
WHERE (((tblContract.CotrName)="rcm" Or (tblContract.CotrName)="mg") AND
((tblContract.ContrStart) Is Not Null) AND ((tblContract.ContrEnd) Is Null))
ORDER BY [firstname] & " " & [lastname];

 

[John Spencer]

It seems to me that you need a WHERE clause that looks like the following.


WHERE (
DateAdd("d",30,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("d",60,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("d",90,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("d",120,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("d",180,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("m",8,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
)

AND (tblContract.CotrName in ("rcm" ,"mg")
AND tblContract.ContrStart Is Not Null
AND tblContract.ContrEnd Is Null
)

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

Sorry I didn't make this more clear. I have a form that staff use to enter
in the date parameters. If they want to see which clients are reaching
30-90-etc days in the contract for August 09, the start and end dates that
they enter in the form are 8/1/09 and 8/30/09. I want the query to only find
clients who will be reaching their 30th-90th-etc day in the contract for
August 2009, but the query will pop up with clients who reached that length
of time back in August 2008. Does that make more sense?

I am trying to use DateDiff to autocalculate some dates,

Apparently you posted the wrong query as there is no DateDiff in your posting.

--
Build a little, test a little.

I am trying to use DateDiff to autocalculate some dates, but it returns dates
for past years that I don’t need. The query is listed below. What I want to
happen is for the user to enter in the date range and have the query
automatically find clients in particular contracts who will, within the
specified date range, be hitting their 30th, 90th, 120th, 180th day, or 8th
month, of being in that contract (based on that client’s arrival date, or
date of asylum if the client has that date listed). I am getting the
intervals right in general, but for clients who were enrolled in past years
come up in the query. I could fix this by only allowing clients who are
marked as being active show up, but sometimes clients go inactive and I need
those clients to show up in the specified dates. Sorry if it’s a little
confusing. Any ideas? Thanks!

SELECT [firstname] & " " & [lastname] AS [Full Name], tblContract.CotrName,
tblContract.ContrStart, tblContract.ContrEnd, tblMainClientInfo.CaseMgr,
tblMainClientInfo.AsylumDate, tblMainClientInfo.ArrivDate, IIf([asylumdate]
Is Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",30,[asylumdate])) AS [30
Day Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",90,[asylumdate])) AS [90 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",120,[asylumdate])) AS [120 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",180,[asylumdate])) AS [180 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("m",8,[asylumdate])) AS [8 Month
Report Date]
FROM tblMainClientInfo INNER JOIN tblContract ON tblMainClientInfo.ClientID
= tblContract.ClientID
WHERE (((tblContract.CotrName)="rcm" Or (tblContract.CotrName)="mg") AND
((tblContract.ContrStart) Is Not Null) AND ((tblContract.ContrEnd) Is Null))
ORDER BY [firstname] & " " & [lastname];

 

[mewins]

I'm trying to make that work but I'm just getting a bunch of syntax errors.
Here is a simplified version of the query:

SELECT [firstname] & " " & [lastname] AS [Full Name], tblContract.CotrName,
tblContract.ContrStart, DateAdd("d",90,[contrstart]) AS [90 Day Report Date],
tblMainClientInfo.Active
FROM tblMainClientInfo INNER JOIN tblContract ON tblMainClientInfo.ClientID
= tblContract.ClientID
WHERE (((tblContract.CotrName)=[forms]![frmcontrrep]![txtContract]))
ORDER BY [firstname] & " " & [lastname];

So if the user puts in the date parameters of 8/1/09 and 8/31/09, I only
want it to show people reaching 90 days in the month of August (so
[contrstart] would be between 5/1/09 and 5/31/09). Right now, I'm still
getting clients who enrolled in May 2008. I've tried placing criteria for 90
day Report Date, but that doesn't seem to be working. What else should I do?
Thanks so much for all your help.

It seems to me that you need a WHERE clause that looks like the following.


WHERE (
DateAdd("d",30,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("d",60,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("d",90,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("d",120,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("d",180,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("m",8,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
)

AND (tblContract.CotrName in ("rcm" ,"mg")
AND tblContract.ContrStart Is Not Null
AND tblContract.ContrEnd Is Null
)

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

Sorry I didn't make this more clear. I have a form that staff use to enter
in the date parameters. If they want to see which clients are reaching
30-90-etc days in the contract for August 09, the start and end dates that
they enter in the form are 8/1/09 and 8/30/09. I want the query to only find
clients who will be reaching their 30th-90th-etc day in the contract for
August 2009, but the query will pop up with clients who reached that length
of time back in August 2008. Does that make more sense?

I am trying to use DateDiff to autocalculate some dates,
Apparently you posted the wrong query as there is no DateDiff in your posting.

--
Build a little, test a little.


:

I am trying to use DateDiff to autocalculate some dates, but it returns dates
for past years that I don’t need. The query is listed below. What I want to
happen is for the user to enter in the date range and have the query
automatically find clients in particular contracts who will, within the
specified date range, be hitting their 30th, 90th, 120th, 180th day, or 8th
month, of being in that contract (based on that client’s arrival date, or
date of asylum if the client has that date listed). I am getting the
intervals right in general, but for clients who were enrolled in past years
come up in the query. I could fix this by only allowing clients who are
marked as being active show up, but sometimes clients go inactive and I need
those clients to show up in the specified dates. Sorry if it’s a little
confusing. Any ideas? Thanks!

SELECT [firstname] & " " & [lastname] AS [Full Name], tblContract.CotrName,
tblContract.ContrStart, tblContract.ContrEnd, tblMainClientInfo.CaseMgr,
tblMainClientInfo.AsylumDate, tblMainClientInfo.ArrivDate, IIf([asylumdate]
Is Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",30,[asylumdate])) AS [30
Day Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",90,[asylumdate])) AS [90 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",120,[asylumdate])) AS [120 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",180,[asylumdate])) AS [180 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("m",8,[asylumdate])) AS [8 Month
Report Date]
FROM tblMainClientInfo INNER JOIN tblContract ON tblMainClientInfo.ClientID
= tblContract.ClientID
WHERE (((tblContract.CotrName)="rcm" Or (tblContract.CotrName)="mg") AND
((tblContract.ContrStart) Is Not Null) AND ((tblContract.ContrEnd) Is Null))
ORDER BY [firstname] & " " & [lastname];

 

[KARL DEWEY]

WHERE (((tblContract.CotrName)=[forms]![frmcontrrep]![txtContract]))
The SQL you posted has no date criteria at all so you will get records other
than what you are looking for.

Try this ---
WHERE ([tblContract].[CotrName] =[forms]![frmcontrrep]![txtContract]) AND
([contrstart] Between #5/1/09# AND #5/31/09#)

--
Build a little, test a little.

I'm trying to make that work but I'm just getting a bunch of syntax errors.
Here is a simplified version of the query:

SELECT [firstname] & " " & [lastname] AS [Full Name], tblContract.CotrName,
tblContract.ContrStart, DateAdd("d",90,[contrstart]) AS [90 Day Report Date],
tblMainClientInfo.Active
FROM tblMainClientInfo INNER JOIN tblContract ON tblMainClientInfo.ClientID
= tblContract.ClientID
WHERE (((tblContract.CotrName)=[forms]![frmcontrrep]![txtContract]))
ORDER BY [firstname] & " " & [lastname];

So if the user puts in the date parameters of 8/1/09 and 8/31/09, I only
want it to show people reaching 90 days in the month of August (so
[contrstart] would be between 5/1/09 and 5/31/09). Right now, I'm still
getting clients who enrolled in May 2008. I've tried placing criteria for 90
day Report Date, but that doesn't seem to be working. What else should I do?
Thanks so much for all your help.

It seems to me that you need a WHERE clause that looks like the following.


WHERE (
DateAdd("d",30,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("d",60,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("d",90,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("d",120,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("d",180,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
OR
DateAdd("m",8,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![StartDate]
)

AND (tblContract.CotrName in ("rcm" ,"mg")
AND tblContract.ContrStart Is Not Null
AND tblContract.ContrEnd Is Null
)

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

Sorry I didn't make this more clear. I have a form that staff use to enter
in the date parameters. If they want to see which clients are reaching
30-90-etc days in the contract for August 09, the start and end dates that
they enter in the form are 8/1/09 and 8/30/09. I want the query to only find
clients who will be reaching their 30th-90th-etc day in the contract for
August 2009, but the query will pop up with clients who reached that length
of time back in August 2008. Does that make more sense?

:

I am trying to use DateDiff to autocalculate some dates,
Apparently you posted the wrong query as there is no DateDiff in your posting.

--
Build a little, test a little.


:

I am trying to use DateDiff to autocalculate some dates, but it returns dates
for past years that I don’t need. The query is listed below. What I want to
happen is for the user to enter in the date range and have the query
automatically find clients in particular contracts who will, within the
specified date range, be hitting their 30th, 90th, 120th, 180th day, or 8th
month, of being in that contract (based on that client’s arrival date, or
date of asylum if the client has that date listed). I am getting the
intervals right in general, but for clients who were enrolled in past years
come up in the query. I could fix this by only allowing clients who are
marked as being active show up, but sometimes clients go inactive and I need
those clients to show up in the specified dates. Sorry if it’s a little
confusing. Any ideas? Thanks!

SELECT [firstname] & " " & [lastname] AS [Full Name], tblContract.CotrName,
tblContract.ContrStart, tblContract.ContrEnd, tblMainClientInfo.CaseMgr,
tblMainClientInfo.AsylumDate, tblMainClientInfo.ArrivDate, IIf([asylumdate]
Is Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",30,[asylumdate])) AS [30
Day Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",90,[asylumdate])) AS [90 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",120,[asylumdate])) AS [120 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("d",180,[asylumdate])) AS [180 Day
Report Date], IIf([asylumdate] Is
Null,(DateAdd("d",30,[arrivdate])),DateAdd("m",8,[asylumdate])) AS [8 Month
Report Date]
FROM tblMainClientInfo INNER JOIN tblContract ON tblMainClientInfo.ClientID
= tblContract.ClientID
WHERE (((tblContract.CotrName)="rcm" Or (tblContract.CotrName)="mg") AND
((tblContract.ContrStart) Is Not Null) AND ((tblContract.ContrEnd) Is Null))
ORDER BY [firstname] & " " & [lastname];

 

[mewins]

That definitely works if staff are only looking for people hitting the 90th
day in 8/09, but how do I make that WHERE clause flexible and based on the
date range the staff input? Thanks!

WHERE (((tblContract.CotrName)=[forms]![frmcontrrep]![txtContract]))

The SQL you posted has no date criteria at all so you will get records other
than what you are looking for.

Try this ---
WHERE ([tblContract].[CotrName] =[forms]![frmcontrrep]![txtContract]) AND
([contrstart] Between #5/1/09# AND #5/31/09#)

 

[KARL DEWEY]

Try this --
WHERE ([tblContract].[CotrName] =[forms]![frmcontrrep]![txtContract]) AND
(Format([contrstart], "yyyymm") = Format(DateAdd("d",[Enter number of
contract days] ,[contrstart]), "yyyymm")

--
Build a little, test a little.

That definitely works if staff are only looking for people hitting the 90th
day in 8/09, but how do I make that WHERE clause flexible and based on the
date range the staff input? Thanks!

WHERE (((tblContract.CotrName)=[forms]![frmcontrrep]![txtContract]))

The SQL you posted has no date criteria at all so you will get records other
than what you are looking for.

Try this ---
WHERE ([tblContract].[CotrName] =[forms]![frmcontrrep]![txtContract]) AND
([contrstart] Between #5/1/09# AND #5/31/09#)

 

[mewins]

Hm. I can't seem to get any clients to show up with this inserted. I've
also tried running this query as a sub query and then using another one to
look for clients who have the right date range, but that still manages to
include clients from the wrong year. Thoughts? Thanks again for all of your
help.

Try this --
WHERE ([tblContract].[CotrName] =[forms]![frmcontrrep]![txtContract]) AND
(Format([contrstart], "yyyymm") = Format(DateAdd("d",[Enter number of
contract days] ,[contrstart]), "yyyymm")

--
Build a little, test a little.

That definitely works if staff are only looking for people hitting the 90th
day in 8/09, but how do I make that WHERE clause flexible and based on the
date range the staff input? Thanks!

WHERE (((tblContract.CotrName)=[forms]![frmcontrrep]![txtContract]))
The SQL you posted has no date criteria at all so you will get records other
than what you are looking for.

Try this ---
WHERE ([tblContract].[CotrName] =[forms]![frmcontrrep]![txtContract]) AND
([contrstart] Between #5/1/09# AND #5/31/09#)

 

[John Spencer]

At this point I am confused.

Do you want to show records based on ContrStart, or AsylumDate, or ArrivDate?

Do you want to show them on based on 30 or 60 or 90 days past one of those
dates? Any one of them or all of them.


SELECT [firstname] & " " & [lastname] AS [Full Name]
, tblContract.CotrName
, tblContract.ContrStart
, tblContract.ContrEnd
, tblMainClientInfo.CaseMgr
, tblMainClientInfo.AsylumDate
, tblMainClientInfo.ArrivDate
WHERE (
DateAdd("d",30,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![EndDate]
OR
DateAdd("d",60,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![EndDate]
OR
DateAdd("d",90,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![EndDate]
OR
DateAdd("d",120,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![EndDate]
OR
DateAdd("d",180,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![EndDate]
OR
DateAdd("m",8,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![EndDate]
)
AND (tblContract.CotrName in ("rcm" ,"mg")
AND tblContract.ContrStart Is Not Null
AND tblContract.ContrEnd Is Null
)

If you wanted to do just one period at a time, you could add one more
parameter and use

SELECT [firstname] & " " & [lastname] AS [Full Name]
, tblContract.CotrName
, tblContract.ContrStart
, tblContract.ContrEnd
, tblMainClientInfo.CaseMgr
, tblMainClientInfo.AsylumDate
, tblMainClientInfo.ArrivDate

WHERE
DateAdd("d",Forms![FormName]![PeriodLength],NZ(AsylumDate,ArrivDate)) Between
Forms![FormName]![StartDate] and Forms![FormName]![EndDate]
AND tblContract.CotrName in ("rcm" ,"mg")
AND tblContract.ContrStart Is Not Null
AND tblContract.ContrEnd Is Null

If you are getting a BUNCH of syntax errors, could you try to tell us what the
errors are. n

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

I'm trying to make that work but I'm just getting a bunch of syntax errors.
Here is a simplified version of the query:

SELECT [firstname] & " " & [lastname] AS [Full Name], tblContract.CotrName,
tblContract.ContrStart, DateAdd("d",90,[contrstart]) AS [90 Day Report Date],
tblMainClientInfo.Active
FROM tblMainClientInfo INNER JOIN tblContract ON tblMainClientInfo.ClientID
= tblContract.ClientID
WHERE (((tblContract.CotrName)=[forms]![frmcontrrep]![txtContract]))
ORDER BY [firstname] & " " & [lastname];

So if the user puts in the date parameters of 8/1/09 and 8/31/09, I only
want it to show people reaching 90 days in the month of August (so
[contrstart] would be between 5/1/09 and 5/31/09). Right now, I'm still
getting clients who enrolled in May 2008. I've tried placing criteria for 90
day Report Date, but that doesn't seem to be working. What else should I do?
Thanks so much for all your help.

"John Spencer" wrote:

 

[mewins]

Ok, thanks for all your help. It's been decided that this query/function is
no longer needed. I'd still like to figure out how to make it work, but I'll
tinker with it in my spare time. Thanks again!

At this point I am confused.

Do you want to show records based on ContrStart, or AsylumDate, or ArrivDate?

Do you want to show them on based on 30 or 60 or 90 days past one of those
dates? Any one of them or all of them.


SELECT [firstname] & " " & [lastname] AS [Full Name]
, tblContract.CotrName
, tblContract.ContrStart
, tblContract.ContrEnd
, tblMainClientInfo.CaseMgr
, tblMainClientInfo.AsylumDate
, tblMainClientInfo.ArrivDate
WHERE (
DateAdd("d",30,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![EndDate]
OR
DateAdd("d",60,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![EndDate]
OR
DateAdd("d",90,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![EndDate]
OR
DateAdd("d",120,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![EndDate]
OR
DateAdd("d",180,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![EndDate]
OR
DateAdd("m",8,NZ(AsylumDate,ArrivDate)) Between Forms![FormName]![StartDate]
and Forms![FormName]![EndDate]
)
AND (tblContract.CotrName in ("rcm" ,"mg")
AND tblContract.ContrStart Is Not Null
AND tblContract.ContrEnd Is Null
)

If you wanted to do just one period at a time, you could add one more
parameter and use

SELECT [firstname] & " " & [lastname] AS [Full Name]
, tblContract.CotrName
, tblContract.ContrStart
, tblContract.ContrEnd
, tblMainClientInfo.CaseMgr
, tblMainClientInfo.AsylumDate
, tblMainClientInfo.ArrivDate

WHERE
DateAdd("d",Forms![FormName]![PeriodLength],NZ(AsylumDate,ArrivDate)) Between
Forms![FormName]![StartDate] and Forms![FormName]![EndDate]
AND tblContract.CotrName in ("rcm" ,"mg")
AND tblContract.ContrStart Is Not Null
AND tblContract.ContrEnd Is Null

If you are getting a BUNCH of syntax errors, could you try to tell us what the
errors are. n

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

I'm trying to make that work but I'm just getting a bunch of syntax errors.
Here is a simplified version of the query:

SELECT [firstname] & " " & [lastname] AS [Full Name], tblContract.CotrName,
tblContract.ContrStart, DateAdd("d",90,[contrstart]) AS [90 Day Report Date],
tblMainClientInfo.Active
FROM tblMainClientInfo INNER JOIN tblContract ON tblMainClientInfo.ClientID
= tblContract.ClientID
WHERE (((tblContract.CotrName)=[forms]![frmcontrrep]![txtContract]))
ORDER BY [firstname] & " " & [lastname];

So if the user puts in the date parameters of 8/1/09 and 8/31/09, I only
want it to show people reaching 90 days in the month of August (so
[contrstart] would be between 5/1/09 and 5/31/09). Right now, I'm still
getting clients who enrolled in May 2008. I've tried placing criteria for 90
day Report Date, but that doesn't seem to be working. What else should I do?
Thanks so much for all your help.

"John Spencer" wrote: