IIF question

[Jeff]

This is the IIF statement I'm using, but it's coming up with the old
"Does not include expression as part of an aggregate function error."
I want the birth date to appear in the Pink Birth Date if it's between
those dates and otherwise leave it blank.

Can someone help?

Thanks

Jeff

Pink Birth Date: IIf([Pregnancy Outcomes].[Birth Date] Between [HMB
Test].[Date] And [HMB Test].[EDC],[Pregnancy Outcomes].[Birth Date],"
")

 

[Rob Parker]

Hi Jeff,

I suspect that the problem lies elsewhere in the query, rather than in the
Iif expression itself. I just tried the following in a test example here,
and it worked as expected:
IIf([MyDateField] Between [StartDate] And [EndDate],[MyDateField],"")

The wording in the error message leads me to think that you have this in a
Totals query; if so, the problem will almost certainly be related to that
somehow. If you post the SQL for the query, I (or someone else) will
probably be able to help.

Rob

 

[Jeff]

You're absolutely right. I'm having trouble with this query in
general, though.

I have a table of pregnancy tests (for a client). I want my query to
report each "yes" or "already pregnant" test for every client and
check my pregnancy outcome table to see if the child has already been
born (hence the IIF statement above.) The client is the parent to both
of the other tables.

I'm using a "include rows where the joined fields from both records
are equal" for the client and pregnancy test tables (as I only want to
report if the client had a test) and a "include all records from the
client and only those records from the Pregnancy Outcome table where
the joined fields are equal." for the client and the outcome.

However, this is not giving me the results I want -- I'm getting
records for multiple pregnancy outcomes in my table for every client.
I tried a totals query, but that didn't work with the IIF statement to
retrieve the date of birth info from the pregnancy outcomes table.

Example

Jane Doe
pregnancy tests
02/13/07
03/08/08

Date of Birth
12/5/07 -- should match 2/13/07 test
10/05/04 -- should not match any pregnancy test (child prior to
database.)

I would appreciate any help with this.

Thanks

Jeff

 

[Rob Parker]

OK, let's work on it from scratch. To do that, I really need to know
details of the relevant fields in all of the tables involved (there seem to
be three, since you say "The client is the parent to both of the other
tables", but your original post mentioned only two).

The SQL for the query you currently have, even if it's not working as you
want, would also be useful. Just open the query, select SQL View from the
View menu, and cut/paste into your message.

Rob

 

[Jeff]

Thanks so much.

There are 3 tables involved. One parent table [Demographic] which
represents the client. The key is the DemogID (short for demographic
identifier). There are 2 child tables. The first is the pregnancy test
[HMB Test] which has the date of the pregnancy test, the results of
the test, and the expected due date (EDC). The other child is the
pregnancy outcome, which contains the name, date of birth, etc about
the baby born. All 3 tables contain the DemogID field and are tied
together (1 to many relationship with referential integrity.)

In my example

Demographic information
Jane Doe

pregnancy tests [HMB Test]
02/13/07 -- positive
03/08/08 -- positive


Pregnancy Outcomes
12/5/07 -- should match 2/13/07 test
10/05/04 -- should not match any pregnancy test (child prior to
database.)

I hope that's enough. The SQL is as follows:

SELECT Demographics.DemogID, Demographics.[Active Van Client],
Demographics.[Van Start Date], [HMB Test].LMP, DateAdd("d",90,Date())
AS Plus90, DateAdd("d",120,[HMB Test]!EDC) AS DuePlus90, Demographics.
[Van Neighborhood Served], Demographics.FirstName,
Demographics.LastName, Demographics.MiddleName, [HMB Test].Date, [HMB
Test].EDC, [HMB Test].[Test Results], IIf([Pregnancy Outcomes].[Baby's
DOB] Between [HMB Test].Date And [DuePlus90],[Pregnancy Outcomes].
[Baby's DOB],Null) AS [Pink Birth Date], IIf([Pregnancy Outcomes].
[Deceased Date] Between [HMB Test].Date And [HMB Test].EDC,[Pregnancy
Outcomes].[Deceased Date],Null) AS [Pink Deceased Date],
IIf([Pregnancy Outcomes].[Lost to Followup Date] Between [HMB
Test].Date And [HMB Test].EDC,[Pregnancy Outcomes].[Lost to Followup
Date],Null) AS [Pink Lost to Followup Date]
FROM (Demographics INNER JOIN [HMB Test] ON Demographics.DemogID =
[HMB Test].DemogID) LEFT JOIN [Pregnancy Outcomes] ON
Demographics.DemogID = [Pregnancy Outcomes].DemogID
WHERE (((Demographics.[Active Van Client])=-1) AND (([HMB Test].[Test
Results])="Positive" Or ([HMB Test].[Test Results])="Already
Pregnant"))
ORDER BY Demographics.LastName DESC;


Thanks a lot.

Jeff

 

[Rob Parker]

Hi Jeff,

Before I get too involved with this, there's one obvious problem with what
you've got at present, and also some "good practice" issues, which I'll get
out of the way first.

Avoid use of spaces in table and field name. You can always alias a
non-space field name to a string with spaces in a query if you need it for
display in a form/report (or you can simply change the default fieldname in
the label control). You got several examples of this, in both table and
field names. In particular, the [Baby's DOB] is awful; I'm surprised Access
even let you use this one - I would have though ' was an illegal character
in a field name, but I guess it's not.

The HMB Test table (better name would be tblHMDTests - using a naming
convention helps when reading something later; eg. in a comple query, you
can immediately see if an underlying table/query is a table or a query if
you name your tables tblName and your queries qryName) contains a field
named Date. That's a reserved word in Access (it's own function to return
the current date - which you're also using in your query); reserved words
should never be used as field names; there'll come a time when Access gets
confused and something will break (perhaps not obviously). There's a
comprehensive list of them at Allen Browne's website:
http://allenbrowne.com/AppIssueBadWord.html And he's also got a little
utility to check for these (and some other issues) in existing databases:
http://allenbrowne.com/AppIssueChecker.html

So much for the good practice things; here's the obvious problem:

You are using [DuePlus90] in an expression in an Iif statement; however,
[DuePlus90] is an alias to a calculated field in the query. You cannot use
an alias in the query which defines it - you must use the calculated
expression. So, your
IIf([Pregnancy Outcomes].[Baby's DOB] Between [HMB Test].Date And
[DuePlus90],[Pregnancy Outcomes].[Baby's DOB],Null)
would need to be:
IIf([Pregnancy Outcomes].[Baby's DOB] Between [HMB Test].Date And
DateAdd("d",120,[HMB Test]!EDC),[Pregnancy Outcomes].[Baby's DOB],Null)

Maybe this will fix the issue; could you try it and let me know before I
spend any more effort on this.

I'm also a little concerned that there is no direct link between records for
the same DemogID in the HMB Test and Pregnancy Outcomes tables; does there
need to be a link between a Pregnancy Outcome record for a particular
DemogID and the record(s) for various HMB Test - which could, presumably,
relate to more than one pregnancy at different times. Or are you satisified
that your conditions using dates is sufficient to prevent this. Note that
without a link - if there should be one - your query may return multiple
copies of certain records.

Rob

Thanks so much.

There are 3 tables involved. One parent table [Demographic] which
represents the client. The key is the DemogID (short for demographic
identifier). There are 2 child tables. The first is the pregnancy test
[HMB Test] which has the date of the pregnancy test, the results of
the test, and the expected due date (EDC). The other child is the
pregnancy outcome, which contains the name, date of birth, etc about
the baby born. All 3 tables contain the DemogID field and are tied
together (1 to many relationship with referential integrity.)

In my example

Demographic information
Jane Doe

pregnancy tests [HMB Test]
02/13/07 -- positive
03/08/08 -- positive


Pregnancy Outcomes
12/5/07 -- should match 2/13/07 test
10/05/04 -- should not match any pregnancy test (child prior to
database.)

I hope that's enough. The SQL is as follows:

SELECT Demographics.DemogID, Demographics.[Active Van Client],
Demographics.[Van Start Date], [HMB Test].LMP, DateAdd("d",90,Date())
AS Plus90, DateAdd("d",120,[HMB Test]!EDC) AS DuePlus90, Demographics.
[Van Neighborhood Served], Demographics.FirstName,
Demographics.LastName, Demographics.MiddleName, [HMB Test].Date, [HMB
Test].EDC, [HMB Test].[Test Results], IIf([Pregnancy Outcomes].[Baby's
DOB] Between [HMB Test].Date And [DuePlus90],[Pregnancy Outcomes].
[Baby's DOB],Null) AS [Pink Birth Date], IIf([Pregnancy Outcomes].
[Deceased Date] Between [HMB Test].Date And [HMB Test].EDC,[Pregnancy
Outcomes].[Deceased Date],Null) AS [Pink Deceased Date],
IIf([Pregnancy Outcomes].[Lost to Followup Date] Between [HMB
Test].Date And [HMB Test].EDC,[Pregnancy Outcomes].[Lost to Followup
Date],Null) AS [Pink Lost to Followup Date]
FROM (Demographics INNER JOIN [HMB Test] ON Demographics.DemogID =
[HMB Test].DemogID) LEFT JOIN [Pregnancy Outcomes] ON
Demographics.DemogID = [Pregnancy Outcomes].DemogID
WHERE (((Demographics.[Active Van Client])=-1) AND (([HMB Test].[Test
Results])="Positive" Or ([HMB Test].[Test Results])="Already
Pregnant"))
ORDER BY Demographics.LastName DESC;


Thanks a lot.

Jeff

 

[Jeff]

Hi

Yes, I'm very aware of the problems with the naming conventions. I am
currently supporting on a freelance basis this database which was
developed by another contract group. I've made some changes, but of
course, getting the client to pay for name changes is tough going,
since they don't see much reason for the changes. The reserved word
"Date" will be changed in this next round of changes, though.

The change to the IIF statement works great. Thanks

The reason that there is not a direct connect is because some clients
come in for tests and never have any follow-up, while others come in
for 2-3 tests. Other clients come in just to learn more about how to
care for a baby so there's no test but there is a pregnancy outcome. .
The clients are inner-city, so many are transient and cannot be
located on a regular basis. The dates have proved sufficient to tie
the 2 tables together.

I am seeing a few cases of multiple copies of certain records in the
query. Is there a way to remove the duplicate copies easily?

Thanks again.

Jeff

 

[Rob Parker]

Hi Jeff,

Snipped from below:

The change to the IIF statement works great. Thanks

I assume this means that your problem is solved., and your query is now
giving what you want.

The later question, regarding "multiple copies of certain records", is
probably due to the lack of a link where there should be one, as I said at
the end of my last post. You may be able to use a simple work-around by
setting the query to eliminate duplicates, using the DISTINCT keyword. Or
maybe you need to re-visit the relationships, and make sure they are
correct - as I said before, you may need a link between the two "child"
tables.

Rob

PS. I'm wondering where in the world you are, from your posting times. I'd
guess that, like me, you're in Oz; but maybe not. Care to satisfy my
curiosity on this point? I'll be back to browsing this group tomorrow -
it's now 11:40pm local time, so I'm off to bed.

 

[Jeff]

I fixed the duplicates problem through a unmatched query of the items
with delivery dates and the tests. It seems to be working great.

I'm actually in the US, Ohio to be precise. I'm just an insomniac by
nature, so not much sleep for me

Thanks again

Jeff