I added an Iif statement to a query now it returns nothing

[MS_Access_Neophyte]

Thanks to this and other forums, and the access MVPs out there (especially
John Spencer), I'm so close to getting the data I need. Here's the SQL view
of the part that almost works:
*******************
SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [BMC Client Data Table]![AdmitDate] And [BMC
Client Data Table]![DC_DischargeDate]))
GROUP BY tDates2.fDate
HAVING (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date]))));
*******************
Problem is, this returns only the number of clients in program on all the
days, whose “DC_DischargeDate†is not null. I need the expression to
include any clients who have not discharged yet - whose “DC_DischargeDate†is
Null.

I tried changing the expression to this, but it returned nothing – not even
the previous result. Where am I going wrong?
*******************
IIf([DC_DischargeDate] Is Null,([tDates2].[fDate]) Between [BMC Client Data
Table]![AdmitDate] And (Date()),([tDates2].[fDate]) Between [BMC Client Data
Table]![AdmitDate] And [BMC Client Data Table]![DC_DischargeDate])
*******************

Thanks so much!!
Access Neophyte

 

[Bob Barrows [MVP]]

Thanks to this and other forums, and the access MVPs out there
(especially John Spencer), I'm so close to getting the data I need.
Here's the SQL view of the part that almost works:
*******************
SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID,
tDates2.fDate FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [BMC Client Data Table]![AdmitDate]
And [BMC Client Data Table]![DC_DischargeDate]))
GROUP BY tDates2.fDate
HAVING (((tDates2.fDate) Between [Starting Date] And
(DateAdd("d",-1,[Ending Date]))));
*******************
Problem is, this returns only the number of clients in program on all
the days, whose "DC_DischargeDate" is not null. I need the
expression to include any clients who have not discharged yet - whose
"DC_DischargeDate" is Null.

I tried changing the expression to this, but it returned nothing -
not even the previous result. Where am I going wrong?
*******************
IIf([DC_DischargeDate] Is Null,([tDates2].[fDate]) Between [BMC
Client Data Table]![AdmitDate] And (Date()),([tDates2].[fDate])
Between [BMC Client Data Table]![AdmitDate] And [BMC Client Data
Table]![DC_DischargeDate]) *******************

OK, first of all, you have a HAVING filter that should be getting done in
the WHERE clause. Secondly, it appears you are specifying a link in the
WHERE clause that should be done in the ON clause (admittedly, this
correction will prevent the use of Design view - I will show my suggestion
in both forms so you can decide which to use).

So what is wrong with your IIF expression? IIF requires 3 arguments: you
have only supplied two. Plus, your thinking appears to be wrong. It sounds
as if DC_DischargeDate contains the date of discharge, correct?
BETWEEN will include records where fdate is equal to DC_DischargeDate,
meaning it will include records of clients who have been discharged,
correct? This does not agree with your stated desire " ... to include any
client who have not discharged yet." So, let's see if this works for you:

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date])))) AND fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
GROUP BY tDates2.fDate

Slightly more correct (but prevents use of Design view):

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table] INNER JOIN tDates2
ON fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
WHERE (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date]))))
GROUP BY tDates2.fDate

 

[Erez Mor]

hi
i cant copete with elaborate answers like bob, but i'll only add, if i got
your intention right, that you can use the Nz function instead of the if(s):
if you want to check that [Date1] is between [StartDate] that is knwon and
[EndDate] that could be a date or Null, and as you said, the query is
supposed to return "...clients who have not discharged yet ", as in Now,
today, then add a bogus date that is later than now (i used tommorow) to all
Null dates and your done with:

Where [Date1] Between [StartDate] And Nz([EndDate],DateAdd("d",1,Date()))

good luck
Erez

Thanks to this and other forums, and the access MVPs out there
(especially John Spencer), I'm so close to getting the data I need.
Here's the SQL view of the part that almost works:
*******************
SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID,
tDates2.fDate FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [BMC Client Data Table]![AdmitDate]
And [BMC Client Data Table]![DC_DischargeDate]))
GROUP BY tDates2.fDate
HAVING (((tDates2.fDate) Between [Starting Date] And
(DateAdd("d",-1,[Ending Date]))));
*******************
Problem is, this returns only the number of clients in program on all
the days, whose "DC_DischargeDate" is not null. I need the
expression to include any clients who have not discharged yet - whose
"DC_DischargeDate" is Null.

I tried changing the expression to this, but it returned nothing -
not even the previous result. Where am I going wrong?
*******************
IIf([DC_DischargeDate] Is Null,([tDates2].[fDate]) Between [BMC
Client Data Table]![AdmitDate] And (Date()),([tDates2].[fDate])
Between [BMC Client Data Table]![AdmitDate] And [BMC Client Data
Table]![DC_DischargeDate]) *******************

OK, first of all, you have a HAVING filter that should be getting done in
the WHERE clause. Secondly, it appears you are specifying a link in the
WHERE clause that should be done in the ON clause (admittedly, this
correction will prevent the use of Design view - I will show my suggestion
in both forms so you can decide which to use).

So what is wrong with your IIF expression? IIF requires 3 arguments: you
have only supplied two. Plus, your thinking appears to be wrong. It sounds
as if DC_DischargeDate contains the date of discharge, correct?
BETWEEN will include records where fdate is equal to DC_DischargeDate,
meaning it will include records of clients who have been discharged,
correct? This does not agree with your stated desire " ... to include any
client who have not discharged yet." So, let's see if this works for you:

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date])))) AND fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
GROUP BY tDates2.fDate

Slightly more correct (but prevents use of Design view):

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table] INNER JOIN tDates2
ON fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
WHERE (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date]))))
GROUP BY tDates2.fDate





--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[MS_Access_Neophyte]

Hi Erez,

I'm going to check this out and get back to you.

Thanks!!!
MS_A_N
--
You''''ll never know if you don't ask!

hi
i cant copete with elaborate answers like bob, but i'll only add, if i got
your intention right, that you can use the Nz function instead of the if(s):
if you want to check that [Date1] is between [StartDate] that is knwon and
[EndDate] that could be a date or Null, and as you said, the query is
supposed to return "...clients who have not discharged yet ", as in Now,
today, then add a bogus date that is later than now (i used tommorow) to all
Null dates and your done with:

Where [Date1] Between [StartDate] And Nz([EndDate],DateAdd("d",1,Date()))

good luck
Erez

Thanks to this and other forums, and the access MVPs out there
(especially John Spencer), I'm so close to getting the data I need.
Here's the SQL view of the part that almost works:
*******************
SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID,
tDates2.fDate FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [BMC Client Data Table]![AdmitDate]
And [BMC Client Data Table]![DC_DischargeDate]))
GROUP BY tDates2.fDate
HAVING (((tDates2.fDate) Between [Starting Date] And
(DateAdd("d",-1,[Ending Date]))));
*******************
Problem is, this returns only the number of clients in program on all
the days, whose "DC_DischargeDate" is not null. I need the
expression to include any clients who have not discharged yet - whose
"DC_DischargeDate" is Null.

I tried changing the expression to this, but it returned nothing -
not even the previous result. Where am I going wrong?
*******************
IIf([DC_DischargeDate] Is Null,([tDates2].[fDate]) Between [BMC
Client Data Table]![AdmitDate] And (Date()),([tDates2].[fDate])
Between [BMC Client Data Table]![AdmitDate] And [BMC Client Data
Table]![DC_DischargeDate]) *******************

OK, first of all, you have a HAVING filter that should be getting done in
the WHERE clause. Secondly, it appears you are specifying a link in the
WHERE clause that should be done in the ON clause (admittedly, this
correction will prevent the use of Design view - I will show my suggestion
in both forms so you can decide which to use).

So what is wrong with your IIF expression? IIF requires 3 arguments: you
have only supplied two. Plus, your thinking appears to be wrong. It sounds
as if DC_DischargeDate contains the date of discharge, correct?
BETWEEN will include records where fdate is equal to DC_DischargeDate,
meaning it will include records of clients who have been discharged,
correct? This does not agree with your stated desire " ... to include any
client who have not discharged yet." So, let's see if this works for you:

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date])))) AND fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
GROUP BY tDates2.fDate

Slightly more correct (but prevents use of Design view):

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table] INNER JOIN tDates2
ON fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
WHERE (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date]))))
GROUP BY tDates2.fDate





--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[MS_Access_Neophyte]

The first version worked perfectly!!!

It's more accurate than the way I've been doing it, which was to run a
report for each date in the month, because your query takes into account
(meaning it doesn't include) clients whose discharge date is the report date.

I now need to either query this query or create another one that will pull
out for me the number of days on which the occupancy (our daily total)
exceeds 90% of our program's capacity (27). Can I do that in the same query?

Thanks so much!
MS_A_N
--
You'll never know if you don't ask!

Thanks to this and other forums, and the access MVPs out there
(especially John Spencer), I'm so close to getting the data I need.
Here's the SQL view of the part that almost works:
*******************
SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID,
tDates2.fDate FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [BMC Client Data Table]![AdmitDate]
And [BMC Client Data Table]![DC_DischargeDate]))
GROUP BY tDates2.fDate
HAVING (((tDates2.fDate) Between [Starting Date] And
(DateAdd("d",-1,[Ending Date]))));
*******************
Problem is, this returns only the number of clients in program on all
the days, whose "DC_DischargeDate" is not null. I need the
expression to include any clients who have not discharged yet - whose
"DC_DischargeDate" is Null.

I tried changing the expression to this, but it returned nothing -
not even the previous result. Where am I going wrong?
*******************
IIf([DC_DischargeDate] Is Null,([tDates2].[fDate]) Between [BMC
Client Data Table]![AdmitDate] And (Date()),([tDates2].[fDate])
Between [BMC Client Data Table]![AdmitDate] And [BMC Client Data
Table]![DC_DischargeDate]) *******************

OK, first of all, you have a HAVING filter that should be getting done in
the WHERE clause. Secondly, it appears you are specifying a link in the
WHERE clause that should be done in the ON clause (admittedly, this
correction will prevent the use of Design view - I will show my suggestion
in both forms so you can decide which to use).

So what is wrong with your IIF expression? IIF requires 3 arguments: you
have only supplied two. Plus, your thinking appears to be wrong. It sounds
as if DC_DischargeDate contains the date of discharge, correct?
BETWEEN will include records where fdate is equal to DC_DischargeDate,
meaning it will include records of clients who have been discharged,
correct? This does not agree with your stated desire " ... to include any
client who have not discharged yet." So, let's see if this works for you:

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date])))) AND fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
GROUP BY tDates2.fDate

Slightly more correct (but prevents use of Design view):

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table] INNER JOIN tDates2
ON fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
WHERE (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date]))))
GROUP BY tDates2.fDate





--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows [MVP]]

I'm guessing here that CountOfBMC_ID represents occupancy. Does 27 represent
your program's capacity? Or 90% of your program's capacity? I will assume
the latter.

This is where a HAVING clause will come into play:

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date])))) AND fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
GROUP BY tDates2.fDate
HAVING Count([BMC Client Data Table].BMC_ID) > 27

The first version worked perfectly!!!

It's more accurate than the way I've been doing it, which was to run a
report for each date in the month, because your query takes into
account (meaning it doesn't include) clients whose discharge date is
the report date.

I now need to either query this query or create another one that will
pull out for me the number of days on which the occupancy (our daily
total) exceeds 90% of our program's capacity (27). Can I do that in
the same query?

Thanks so much!
MS_A_N

Thanks to this and other forums, and the access MVPs out there
(especially John Spencer), I'm so close to getting the data I need.
Here's the SQL view of the part that almost works:
*******************
SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID,
tDates2.fDate FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [BMC Client Data Table]![AdmitDate]
And [BMC Client Data Table]![DC_DischargeDate]))
GROUP BY tDates2.fDate
HAVING (((tDates2.fDate) Between [Starting Date] And
(DateAdd("d",-1,[Ending Date]))));
*******************
Problem is, this returns only the number of clients in program on
all
the days, whose "DC_DischargeDate" is not null. I need the
expression to include any clients who have not discharged yet -
whose "DC_DischargeDate" is Null.

I tried changing the expression to this, but it returned nothing -
not even the previous result. Where am I going wrong?
*******************
IIf([DC_DischargeDate] Is Null,([tDates2].[fDate]) Between [BMC
Client Data Table]![AdmitDate] And (Date()),([tDates2].[fDate])
Between [BMC Client Data Table]![AdmitDate] And [BMC Client Data
Table]![DC_DischargeDate]) *******************

OK, first of all, you have a HAVING filter that should be getting
done in the WHERE clause. Secondly, it appears you are specifying a
link in the WHERE clause that should be done in the ON clause
(admittedly, this correction will prevent the use of Design view - I
will show my suggestion in both forms so you can decide which to
use).

So what is wrong with your IIF expression? IIF requires 3 arguments:
you have only supplied two. Plus, your thinking appears to be wrong.
It sounds as if DC_DischargeDate contains the date of discharge,
correct?
BETWEEN will include records where fdate is equal to
DC_DischargeDate, meaning it will include records of clients who
have been discharged, correct? This does not agree with your stated
desire " ... to include any client who have not discharged yet." So,
let's see if this works for you:

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID,
tDates2.fDate FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [Starting Date] And
(DateAdd("d",-1,[Ending Date])))) AND fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
GROUP BY tDates2.fDate

Slightly more correct (but prevents use of Design view):

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID,
tDates2.fDate FROM [BMC Client Data Table] INNER JOIN tDates2
ON fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
WHERE (((tDates2.fDate) Between [Starting Date] And
(DateAdd("d",-1,[Ending Date]))))
GROUP BY tDates2.fDate





--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

 

[MS_Access_Neophyte]

Yay!!!

I cannot tell you how many things I've tried, and how frustrating it's been.
You're a gem, Bob!

Thank you so much!
Liz
--
You'll never know if you don't ask!

I'm guessing here that CountOfBMC_ID represents occupancy. Does 27 represent
your program's capacity? Or 90% of your program's capacity? I will assume
the latter.

This is where a HAVING clause will come into play:

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date])))) AND fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
GROUP BY tDates2.fDate
HAVING Count([BMC Client Data Table].BMC_ID) > 27

The first version worked perfectly!!!

It's more accurate than the way I've been doing it, which was to run a
report for each date in the month, because your query takes into
account (meaning it doesn't include) clients whose discharge date is
the report date.

I now need to either query this query or create another one that will
pull out for me the number of days on which the occupancy (our daily
total) exceeds 90% of our program's capacity (27). Can I do that in
the same query?

Thanks so much!
MS_A_N

MS_Access_Neophyte wrote:
Thanks to this and other forums, and the access MVPs out there
(especially John Spencer), I'm so close to getting the data I need.
Here's the SQL view of the part that almost works:
*******************
SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID,
tDates2.fDate FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [BMC Client Data Table]![AdmitDate]
And [BMC Client Data Table]![DC_DischargeDate]))
GROUP BY tDates2.fDate
HAVING (((tDates2.fDate) Between [Starting Date] And
(DateAdd("d",-1,[Ending Date]))));
*******************
Problem is, this returns only the number of clients in program on
all
the days, whose "DC_DischargeDate" is not null. I need the
expression to include any clients who have not discharged yet -
whose "DC_DischargeDate" is Null.

I tried changing the expression to this, but it returned nothing -
not even the previous result. Where am I going wrong?
*******************
IIf([DC_DischargeDate] Is Null,([tDates2].[fDate]) Between [BMC
Client Data Table]![AdmitDate] And (Date()),([tDates2].[fDate])
Between [BMC Client Data Table]![AdmitDate] And [BMC Client Data
Table]![DC_DischargeDate]) *******************


OK, first of all, you have a HAVING filter that should be getting
done in the WHERE clause. Secondly, it appears you are specifying a
link in the WHERE clause that should be done in the ON clause
(admittedly, this correction will prevent the use of Design view - I
will show my suggestion in both forms so you can decide which to
use).

So what is wrong with your IIF expression? IIF requires 3 arguments:
you have only supplied two. Plus, your thinking appears to be wrong.
It sounds as if DC_DischargeDate contains the date of discharge,
correct?
BETWEEN will include records where fdate is equal to
DC_DischargeDate, meaning it will include records of clients who
have been discharged, correct? This does not agree with your stated
desire " ... to include any client who have not discharged yet." So,
let's see if this works for you:

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID,
tDates2.fDate FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [Starting Date] And
(DateAdd("d",-1,[Ending Date])))) AND fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
GROUP BY tDates2.fDate

Slightly more correct (but prevents use of Design view):

SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID,
tDates2.fDate FROM [BMC Client Data Table] INNER JOIN tDates2
ON fdate >= AdmitDate AND
(DC_DischargeDate Is Null OR fDate < DC_DischargeDate)
WHERE (((tDates2.fDate) Between [Starting Date] And
(DateAdd("d",-1,[Ending Date]))))
GROUP BY tDates2.fDate





--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"