I'm trying to fix a glitch in a complex access database, and have a
fairly complex problem... unless there's some obscure easy fix I don't
know being fairly new with Access. Basically, the area I'm trying to
fix includes a form which takes entered data, concatenates it into a VB
string to form an SQL query, then launches a report with information
from the query. Several tables are linked in the query, but the key
ones for this problem are like this:
Project<-->Agency-->AgencyContac ts
The report will display a list of 'Projects', grouped by 'Agency', with
an option to include contact information. For the purpose of the
reports, there's only one type of contact out of several that I want:
WHERE ...
AND AC.DPriority = 'Primary' AND AC.[Inactive?]= No
Initially this line was in the main body of the code, not in an IF
statement, so it was being applied to all queries.
Where the problem starts, is when there is no 'Primary' contact for an
Agency, and keep in mind that the contact information is just sort of
side note, any Project linked to that Agency will be filtered out of the
report. What I want to do is keep the project there but do not include
anything for the contact.
Usually this is not a problem, but there's the odd case where we want
contact information included, and the rare Agency that doesn't have a
'Primary' contact. It's pretty obscure and very easy to miss on the
report.
This is my progress so far. First, I moved the WHERE clause statements
listed above into the SELECT statement using an IIF, within the VB IF
statement for when the contacts checkbox is ticked, and leave the field
as blank '' as the alternative:
IIf(AC.DPriorit y='Primary' AND AC.[Inactive?]=No,"Contact Name: " &
AC.Name,'') AS ContactName,
... AS ContactPhone
... etc
This appeared to work at first. However, this selects all contacts, not
just the Primary, and while it doesn't display anything for them, it
makes duplicate copies of the project and messes up the report.
I was about to post here when I thought I had it fixed again, by opening
up the design view for the report itself, and setting duplicate values
to be invisible. I just realized this morning however, that while that
will not show anything, it just leaves a lot of whitespace instead.
So to sum it up, it's sort of a square peg round hole problem. I want
to select one specific value for a couple of columns from a seperate
table, and if it's not there, still maintin the row for the query, with
blank values for those column. Not eliminate the entire row, or create
duplicates. So does anyone have a suggestion on how I can get this
done? Either through an SQL statement, VB code, or Report design?
*** Sent via Developersdex http://www.developersdex.com ***
