Parameter query with Date

[Guest]

Hello...I am using a parameter query to check if patients received or did not
receive an injection during the rate range (Start-End Date) specified. The
following code (see below) is working successfully for patients who did
receive an injection during the date range. But I would like to also find
patients who did not receive an injection and therefore have no dates in the
specified date range...sort of the inverse of the first part. Any help is
greatly appreciated...I hope this is easy, but I am stumped. Thank you.

Between Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date())

 

[Guest]

Kathy,

Need more info on your table structure. Might also help if you posted the
entire SQL statement, not just the criteria for the InjectionDate field.

A question to ask yourself: How would you know that a person is supposed to
receive the injection during the period in question?

Dale

 

[Guest]

If that what you have in the criteria

Where DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date())

But you want in the same resault to display patients who didn't had an
injection, then add a criteria

Where DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date())
Or DateFieldName Is Null

If you want the data seperatly, then

Where DateFieldName Is Null

 

[Guest]

Thank you for your help....I am very new at database work, so I am not sure
about what I am doing, but anyway, I don't think the IS NULL will return the
desired results as there would not be a "blank" date field. So I tried
putting NOT in front of the Between/And code. So I received all the dates
other than the specifed date range....so far so good.. except that since one
patient may have received many past injections, (or outside the date range),
I see all those injection dates...where I really just need to see the name of
that patient who did not receive an injection during the date range. At this
point, if Mr. Jones got 10 injections outside the date range...I see Mr.
Jones' name 10 times with corresponding dates of injections.
I hope this is not completely nutty...Since most patients receive monthly
injections, I am just trying to make sure we did not miss a patient during
that specified date range...usually one month. Is there some way for me to
see just the patient name one time when I run the query? Thanks so much again.
--
Kbelo

If that what you have in the criteria

Where DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date())

But you want in the same resault to display patients who didn't had an
injection, then add a criteria

Where DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date())
Or DateFieldName Is Null

If you want the data seperatly, then

Where DateFieldName Is Null

--
Good Luck
BS"D

Hello...I am using a parameter query to check if patients received or did not
receive an injection during the rate range (Start-End Date) specified. The
following code (see below) is working successfully for patients who did
receive an injection during the date range. But I would like to also find
patients who did not receive an injection and therefore have no dates in the
specified date range...sort of the inverse of the first part. Any help is
greatly appreciated...I hope this is easy, but I am stumped. Thank you.

Between Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date())

 

[Guest]

For that you can use your query as a sub query , something like


Select T1.* From TableName As T1 Where T1.PatientId Not In (Select
T2.PatientId From TableName Where T2.DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date()))

If you want further help I'll need the SQL you are using

--
Good Luck
BS"D

Thank you for your help....I am very new at database work, so I am not sure
about what I am doing, but anyway, I don't think the IS NULL will return the
desired results as there would not be a "blank" date field. So I tried
putting NOT in front of the Between/And code. So I received all the dates
other than the specifed date range....so far so good.. except that since one
patient may have received many past injections, (or outside the date range),
I see all those injection dates...where I really just need to see the name of
that patient who did not receive an injection during the date range. At this
point, if Mr. Jones got 10 injections outside the date range...I see Mr.
Jones' name 10 times with corresponding dates of injections.
I hope this is not completely nutty...Since most patients receive monthly
injections, I am just trying to make sure we did not miss a patient during
that specified date range...usually one month. Is there some way for me to
see just the patient name one time when I run the query? Thanks so much again.
--
Kbelo

If that what you have in the criteria

Where DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date())

But you want in the same resault to display patients who didn't had an
injection, then add a criteria

Where DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date())
Or DateFieldName Is Null

If you want the data seperatly, then

Where DateFieldName Is Null

--
Good Luck
BS"D

Hello...I am using a parameter query to check if patients received or did not
receive an injection during the rate range (Start-End Date) specified. The
following code (see below) is working successfully for patients who did
receive an injection during the date range. But I would like to also find
patients who did not receive an injection and therefore have no dates in the
specified date range...sort of the inverse of the first part. Any help is
greatly appreciated...I hope this is easy, but I am stumped. Thank you.

Between Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date())

 

[Guest]

Sorry, forgot to set the second table as T2

Select T1.* From TableName As T1 Where T1.PatientId Not In (Select
T2.PatientId From TableName As T2 Where T2.DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date()))

--
Good Luck
BS"D

For that you can use your query as a sub query , something like


Select T1.* From TableName As T1 Where T1.PatientId Not In (Select
T2.PatientId From TableName Where T2.DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date()))

If you want further help I'll need the SQL you are using

--
Good Luck
BS"D

Thank you for your help....I am very new at database work, so I am not sure
about what I am doing, but anyway, I don't think the IS NULL will return the
desired results as there would not be a "blank" date field. So I tried
putting NOT in front of the Between/And code. So I received all the dates
other than the specifed date range....so far so good.. except that since one
patient may have received many past injections, (or outside the date range),
I see all those injection dates...where I really just need to see the name of
that patient who did not receive an injection during the date range. At this
point, if Mr. Jones got 10 injections outside the date range...I see Mr.
Jones' name 10 times with corresponding dates of injections.
I hope this is not completely nutty...Since most patients receive monthly
injections, I am just trying to make sure we did not miss a patient during
that specified date range...usually one month. Is there some way for me to
see just the patient name one time when I run the query? Thanks so much again.
--
Kbelo

If that what you have in the criteria

Where DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date())

But you want in the same resault to display patients who didn't had an
injection, then add a criteria

Where DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date())
Or DateFieldName Is Null

If you want the data seperatly, then

Where DateFieldName Is Null

--
Good Luck
BS"D


:

Hello...I am using a parameter query to check if patients received or did not
receive an injection during the rate range (Start-End Date) specified. The
following code (see below) is working successfully for patients who did
receive an injection during the date range. But I would like to also find
patients who did not receive an injection and therefore have no dates in the
specified date range...sort of the inverse of the first part. Any help is
greatly appreciated...I hope this is easy, but I am stumped. Thank you.

Between Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date())