Subqueries

[Guest]

I initially posted this question in the Reports forum as my subquery is
creating a filtered report. However, I just can't seem to get this subquery
down, and now that I know what it is I'm trying to accomplish (I had never
even heard of subqueries when I posted my initial question) I thought I would
post here. I apologize if this is out of line.

Here is the info on my database:

It is a database with information on various organizations.
The main report has "Organization Name" and "County" fields.
Because each organization may have several offices, there is a
subform/report for Location. Included is a Community field to show which
area of the county the office/location is in.

Next, to make this way user friendly, I have created a dialouge box where
the user chooses from a list of counties which populates the community list.
This is working fine. The user then clicks a button to view all the
organizations in the chosen community.

To save myself from creating several extra reports (which are quite complex
with several subforms) I have linked the button to a macro which runs the
Report with a filter. Other such buttons I have set up (based on the main
report) have worked fine, but of course, this is a bit tricker as it's a
subform.

This is the subquery I have created (I've played around with it a lot so I
can't remember how it started out, but this is what it looks like now!)

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE ((Locations.community = " & [choosecom] & ") " & _
"AND (CommunityInventory.OrgID = Locations.OrgID)))"

DoCmd.OpenReport "CommunityInventory", acViewPreview, strWhere

The code *does* bring up my report, but it is *not* filtered - it shows all
records.

I was advised of the 'immediate' window...thing (not big on technical
terms!!) this is what comes up:

EXISTS (SELECT Community FROM Locations WHERE ((Locations.community =
Bridgetown) AND (CommunityInventory.OrgID = Locations.OrgID)))

I've been told the last "Locations.OrgID" should be a number. (not sure why)

I've tried every combination I can think of, but it just won't work. I'm
about ready to give up, but this filter is very important and I'm not sure
how else to get the information I want (i.e. all the organizations in a given
community) without having to create a seperate records for each seperate
office (which would be a lot of wasted time in many cases - many
organizations have quite a few locations and there is a lot of information in
this database associated with each organization).

ANY help would be greatly appreciated! I've been trying to get this work
for almost 2 weeks and I really need to get this database back to the users!

Thank you!

 

[John Spencer]

I would try making sure that you delimited the text string with either
quotation marks or an apostrophe. See below as an example of the string

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE Locations.community = '" & [choosecom] & "' " & _
"AND Locations.OrgID = CommunityInventory.OrgID"

IF THIS doesn't work, perhaps you could post the complete query that is
the record source for the report.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I initially posted this question in the Reports forum as my subquery is
creating a filtered report. However, I just can't seem to get this subquery
down, and now that I know what it is I'm trying to accomplish (I had never
even heard of subqueries when I posted my initial question) I thought I would
post here. I apologize if this is out of line.

Here is the info on my database:

It is a database with information on various organizations.
The main report has "Organization Name" and "County" fields.
Because each organization may have several offices, there is a
subform/report for Location. Included is a Community field to show which
area of the county the office/location is in.

Next, to make this way user friendly, I have created a dialouge box where
the user chooses from a list of counties which populates the community list.
This is working fine. The user then clicks a button to view all the
organizations in the chosen community.

To save myself from creating several extra reports (which are quite complex
with several subforms) I have linked the button to a macro which runs the
Report with a filter. Other such buttons I have set up (based on the main
report) have worked fine, but of course, this is a bit tricker as it's a
subform.

This is the subquery I have created (I've played around with it a lot so I
can't remember how it started out, but this is what it looks like now!)

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE ((Locations.community = " & [choosecom] & ") " & _
"AND (CommunityInventory.OrgID = Locations.OrgID)))"

DoCmd.OpenReport "CommunityInventory", acViewPreview, strWhere

The code *does* bring up my report, but it is *not* filtered - it shows all
records.

I was advised of the 'immediate' window...thing (not big on technical
terms!!) this is what comes up:

EXISTS (SELECT Community FROM Locations WHERE ((Locations.community =
Bridgetown) AND (CommunityInventory.OrgID = Locations.OrgID)))

I've been told the last "Locations.OrgID" should be a number. (not sure why)

I've tried every combination I can think of, but it just won't work. I'm
about ready to give up, but this filter is very important and I'm not sure
how else to get the information I want (i.e. all the organizations in a given
community) without having to create a seperate records for each seperate
office (which would be a lot of wasted time in many cases - many
organizations have quite a few locations and there is a lot of information in
this database associated with each organization).

ANY help would be greatly appreciated! I've been trying to get this work
for almost 2 weeks and I really need to get this database back to the users!

Thank you!

 

[Guest]

Ah ha! perhaps that is my problem (modifying the code as you had below
didn't work). It was suggested to me that this would be all I would need to
get the filter to work.

How would you suggest I proceed? I'm completely lost!

I wish I could upload the database so you could get a better idea... Let me
if what info you need to help me.

Thank you so much!

I would try making sure that you delimited the text string with either
quotation marks or an apostrophe. See below as an example of the string

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE Locations.community = '" & [choosecom] & "' " & _
"AND Locations.OrgID = CommunityInventory.OrgID"

IF THIS doesn't work, perhaps you could post the complete query that is
the record source for the report.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I initially posted this question in the Reports forum as my subquery is
creating a filtered report. However, I just can't seem to get this subquery
down, and now that I know what it is I'm trying to accomplish (I had never
even heard of subqueries when I posted my initial question) I thought I would
post here. I apologize if this is out of line.

Here is the info on my database:

It is a database with information on various organizations.
The main report has "Organization Name" and "County" fields.
Because each organization may have several offices, there is a
subform/report for Location. Included is a Community field to show which
area of the county the office/location is in.

Next, to make this way user friendly, I have created a dialouge box where
the user chooses from a list of counties which populates the community list.
This is working fine. The user then clicks a button to view all the
organizations in the chosen community.

To save myself from creating several extra reports (which are quite complex
with several subforms) I have linked the button to a macro which runs the
Report with a filter. Other such buttons I have set up (based on the main
report) have worked fine, but of course, this is a bit tricker as it's a
subform.

This is the subquery I have created (I've played around with it a lot so I
can't remember how it started out, but this is what it looks like now!)

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE ((Locations.community = " & [choosecom] & ") " & _
"AND (CommunityInventory.OrgID = Locations.OrgID)))"

DoCmd.OpenReport "CommunityInventory", acViewPreview, strWhere

The code *does* bring up my report, but it is *not* filtered - it shows all
records.

I was advised of the 'immediate' window...thing (not big on technical
terms!!) this is what comes up:

EXISTS (SELECT Community FROM Locations WHERE ((Locations.community =
Bridgetown) AND (CommunityInventory.OrgID = Locations.OrgID)))

I've been told the last "Locations.OrgID" should be a number. (not sure why)

I've tried every combination I can think of, but it just won't work. I'm
about ready to give up, but this filter is very important and I'm not sure
how else to get the information I want (i.e. all the organizations in a given
community) without having to create a seperate records for each seperate
office (which would be a lot of wasted time in many cases - many
organizations have quite a few locations and there is a lot of information in
this database associated with each organization).

ANY help would be greatly appreciated! I've been trying to get this work
for almost 2 weeks and I really need to get this database back to the users!

Thank you!

 

[John Spencer]

If your report is based on a saved query, open the query in design view and
switch to SQL view (Menu; VIEW: SQL) and copy and paste the SQL statement.

If your report is based on a query that is set up as a query in the record
source, then copy and paste the record source.

ALSO, your call to the report is incorrect. You should have another comma
in the line.

DoCmd.OpenReport "CommunityInventory", acViewPreview, strWhere
It should read
DoCmd.OpenReport "CommunityInventory", acViewPreview, , strWhere

I know that the it says FilterName for the third argument, but that argument
really is the name of a pre-built query. The fourth argument is a where
condition - which is "A string expression that's a valid SQL WHERE clause
without the word WHERE."
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ah ha! perhaps that is my problem (modifying the code as you had below
didn't work). It was suggested to me that this would be all I would need
to
get the filter to work.

How would you suggest I proceed? I'm completely lost!

I wish I could upload the database so you could get a better idea... Let
me
if what info you need to help me.

Thank you so much!

I would try making sure that you delimited the text string with either
quotation marks or an apostrophe. See below as an example of the string

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE Locations.community = '" & [choosecom] & "' " & _
"AND Locations.OrgID = CommunityInventory.OrgID"

IF THIS doesn't work, perhaps you could post the complete query that is
the record source for the report.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I initially posted this question in the Reports forum as my subquery is
creating a filtered report. However, I just can't seem to get this
subquery
down, and now that I know what it is I'm trying to accomplish (I had
never
even heard of subqueries when I posted my initial question) I thought I
would
post here. I apologize if this is out of line.

Here is the info on my database:

It is a database with information on various organizations.
The main report has "Organization Name" and "County" fields.
Because each organization may have several offices, there is a
subform/report for Location. Included is a Community field to show
which
area of the county the office/location is in.

Next, to make this way user friendly, I have created a dialouge box
where
the user chooses from a list of counties which populates the community
list.
This is working fine. The user then clicks a button to view all the
organizations in the chosen community.

To save myself from creating several extra reports (which are quite
complex
with several subforms) I have linked the button to a macro which runs
the
Report with a filter. Other such buttons I have set up (based on the
main
report) have worked fine, but of course, this is a bit tricker as it's
a
subform.

This is the subquery I have created (I've played around with it a lot
so I
can't remember how it started out, but this is what it looks like now!)

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE ((Locations.community = " & [choosecom] & ") " & _
"AND (CommunityInventory.OrgID = Locations.OrgID)))"

DoCmd.OpenReport "CommunityInventory", acViewPreview, strWhere

The code *does* bring up my report, but it is *not* filtered - it shows
all
records.

I was advised of the 'immediate' window...thing (not big on technical
terms!!) this is what comes up:

EXISTS (SELECT Community FROM Locations WHERE ((Locations.community =
Bridgetown) AND (CommunityInventory.OrgID = Locations.OrgID)))

I've been told the last "Locations.OrgID" should be a number. (not
sure why)

I've tried every combination I can think of, but it just won't work.
I'm
about ready to give up, but this filter is very important and I'm not
sure
how else to get the information I want (i.e. all the organizations in a
given
community) without having to create a seperate records for each
seperate
office (which would be a lot of wasted time in many cases - many
organizations have quite a few locations and there is a lot of
information in
this database associated with each organization).

ANY help would be greatly appreciated! I've been trying to get this
work
for almost 2 weeks and I really need to get this database back to the
users!

Thank you!

 

[Guest]

Okay,

My report is only based the regular tables. The filter is built into the
EventProcedure of the button (I had started putting the filter in a macro and
basing the button on the macro, but can only seem to use the expression
builder in the macro's OpenReport Where Condition, I can't type this code - I
don't think). This method has worked for my other buttons where the filter
is only based on the main report and not a subreport.

I did create a query that got as far as filtering the subreport (offices)
based on my combo boxes, but not that extra step that to get the main report
(organizations) filtered down to just showing the organizations with offices
in the community chosen in my combo box. Here is the code of that query
(although it is working fine):

SELECT Locations.OrgID, Locations.LocationID, Locations.[Site Name],
Locations.Address, Locations.Community, Locations.[Postal Code],
Locations.[Phone Number], Locations.[Main Location], CommunityInventory.OrgID
FROM CommunityInventory LEFT JOIN Locations ON CommunityInventory.OrgID =
Locations.OrgID
WHERE (((Locations.Community)=[Forms]![ChooseOrganization]![choosecom]));

Like I said, the report's filter is actually in the Event Procedure of the
button. What I posted previously is all I have for that.

Finally, I added the extra comma as you suggested, but the query now does
not work - I get an Enter Parameter Value dialogue box.

I usually pick up on this stuff pretty quickly, so this is really starting
to frustrate me and I've completely run out of ideas!!

If your report is based on a saved query, open the query in design view and
switch to SQL view (Menu; VIEW: SQL) and copy and paste the SQL statement.

If your report is based on a query that is set up as a query in the record
source, then copy and paste the record source.

ALSO, your call to the report is incorrect. You should have another comma
in the line.

DoCmd.OpenReport "CommunityInventory", acViewPreview, strWhere
It should read
DoCmd.OpenReport "CommunityInventory", acViewPreview, , strWhere

I know that the it says FilterName for the third argument, but that argument
really is the name of a pre-built query. The fourth argument is a where
condition - which is "A string expression that's a valid SQL WHERE clause
without the word WHERE."
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ah ha! perhaps that is my problem (modifying the code as you had below
didn't work). It was suggested to me that this would be all I would need
to
get the filter to work.

How would you suggest I proceed? I'm completely lost!

I wish I could upload the database so you could get a better idea... Let
me
if what info you need to help me.

Thank you so much!

I would try making sure that you delimited the text string with either
quotation marks or an apostrophe. See below as an example of the string

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE Locations.community = '" & [choosecom] & "' " & _
"AND Locations.OrgID = CommunityInventory.OrgID"

IF THIS doesn't work, perhaps you could post the complete query that is
the record source for the report.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


cprav wrote:
I initially posted this question in the Reports forum as my subquery is
creating a filtered report. However, I just can't seem to get this
subquery
down, and now that I know what it is I'm trying to accomplish (I had
never
even heard of subqueries when I posted my initial question) I thought I
would
post here. I apologize if this is out of line.

Here is the info on my database:

It is a database with information on various organizations.
The main report has "Organization Name" and "County" fields.
Because each organization may have several offices, there is a
subform/report for Location. Included is a Community field to show
which
area of the county the office/location is in.

Next, to make this way user friendly, I have created a dialouge box
where
the user chooses from a list of counties which populates the community
list.
This is working fine. The user then clicks a button to view all the
organizations in the chosen community.

To save myself from creating several extra reports (which are quite
complex
with several subforms) I have linked the button to a macro which runs
the
Report with a filter. Other such buttons I have set up (based on the
main
report) have worked fine, but of course, this is a bit tricker as it's
a
subform.

This is the subquery I have created (I've played around with it a lot
so I
can't remember how it started out, but this is what it looks like now!)

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE ((Locations.community = " & [choosecom] & ") " & _
"AND (CommunityInventory.OrgID = Locations.OrgID)))"

DoCmd.OpenReport "CommunityInventory", acViewPreview, strWhere

The code *does* bring up my report, but it is *not* filtered - it shows
all
records.

I was advised of the 'immediate' window...thing (not big on technical
terms!!) this is what comes up:

EXISTS (SELECT Community FROM Locations WHERE ((Locations.community =
Bridgetown) AND (CommunityInventory.OrgID = Locations.OrgID)))

I've been told the last "Locations.OrgID" should be a number. (not
sure why)

I've tried every combination I can think of, but it just won't work.
I'm
about ready to give up, but this filter is very important and I'm not
sure
how else to get the information I want (i.e. all the organizations in a
given
community) without having to create a seperate records for each
seperate
office (which would be a lot of wasted time in many cases - many
organizations have quite a few locations and there is a lot of
information in
this database associated with each organization).

ANY help would be greatly appreciated! I've been trying to get this
work
for almost 2 weeks and I really need to get this database back to the
users!

Thank you!

 

[John Spencer]

So your MAIN report is built directly on ONE table (?Organizations?) and you
want to filter the main report to show only Organizations that have a
locations in a specific community.

I think that what you want is something like the following.
StrWhere = "OrgID IN
(SELECT Locations.OrgID
FROM Locations
WHERE Locations.Community = """ & [Forms]![ChooseOrganization]![choosecom]
& """")

Basically, you need to construct a query that will return the Organizations
that you want to see based on a community. And then use that as a subquery
in a where clause.

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay,

My report is only based the regular tables. The filter is built into the
EventProcedure of the button (I had started putting the filter in a macro
and
basing the button on the macro, but can only seem to use the expression
builder in the macro's OpenReport Where Condition, I can't type this
code - I
don't think). This method has worked for my other buttons where the
filter
is only based on the main report and not a subreport.

I did create a query that got as far as filtering the subreport (offices)
based on my combo boxes, but not that extra step that to get the main
report
(organizations) filtered down to just showing the organizations with
offices
in the community chosen in my combo box. Here is the code of that query
(although it is working fine):

SELECT Locations.OrgID, Locations.LocationID, Locations.[Site Name],
Locations.Address, Locations.Community, Locations.[Postal Code],
Locations.[Phone Number], Locations.[Main Location],
CommunityInventory.OrgID
FROM CommunityInventory LEFT JOIN Locations ON CommunityInventory.OrgID =
Locations.OrgID
WHERE (((Locations.Community)=[Forms]![ChooseOrganization]![choosecom]));

Like I said, the report's filter is actually in the Event Procedure of the
button. What I posted previously is all I have for that.

Finally, I added the extra comma as you suggested, but the query now does
not work - I get an Enter Parameter Value dialogue box.

I usually pick up on this stuff pretty quickly, so this is really starting
to frustrate me and I've completely run out of ideas!!

If your report is based on a saved query, open the query in design view
and
switch to SQL view (Menu; VIEW: SQL) and copy and paste the SQL
statement.

If your report is based on a query that is set up as a query in the
record
source, then copy and paste the record source.

ALSO, your call to the report is incorrect. You should have another
comma
in the line.

DoCmd.OpenReport "CommunityInventory", acViewPreview, strWhere
It should read
DoCmd.OpenReport "CommunityInventory", acViewPreview, , strWhere

I know that the it says FilterName for the third argument, but that
argument
really is the name of a pre-built query. The fourth argument is a where
condition - which is "A string expression that's a valid SQL WHERE clause
without the word WHERE."
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ah ha! perhaps that is my problem (modifying the code as you had below
didn't work). It was suggested to me that this would be all I would
need
to
get the filter to work.

How would you suggest I proceed? I'm completely lost!

I wish I could upload the database so you could get a better idea...
Let
me
if what info you need to help me.

Thank you so much!

:

I would try making sure that you delimited the text string with either
quotation marks or an apostrophe. See below as an example of the
string

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE Locations.community = '" & [choosecom] & "' " & _
"AND Locations.OrgID = CommunityInventory.OrgID"

IF THIS doesn't work, perhaps you could post the complete query that
is
the record source for the report.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


cprav wrote:
I initially posted this question in the Reports forum as my subquery
is
creating a filtered report. However, I just can't seem to get this
subquery
down, and now that I know what it is I'm trying to accomplish (I had
never
even heard of subqueries when I posted my initial question) I
thought I
would
post here. I apologize if this is out of line.

Here is the info on my database:

It is a database with information on various organizations.
The main report has "Organization Name" and "County" fields.
Because each organization may have several offices, there is a
subform/report for Location. Included is a Community field to show
which
area of the county the office/location is in.

Next, to make this way user friendly, I have created a dialouge box
where
the user chooses from a list of counties which populates the
community
list.
This is working fine. The user then clicks a button to view all the
organizations in the chosen community.

To save myself from creating several extra reports (which are quite
complex
with several subforms) I have linked the button to a macro which
runs
the
Report with a filter. Other such buttons I have set up (based on
the
main
report) have worked fine, but of course, this is a bit tricker as
it's
a
subform.

This is the subquery I have created (I've played around with it a
lot
so I
can't remember how it started out, but this is what it looks like
now!)

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE ((Locations.community = " & [choosecom] & ") " & _
"AND (CommunityInventory.OrgID = Locations.OrgID)))"

DoCmd.OpenReport "CommunityInventory", acViewPreview, strWhere

The code *does* bring up my report, but it is *not* filtered - it
shows
all
records.

I was advised of the 'immediate' window...thing (not big on
technical
terms!!) this is what comes up:

EXISTS (SELECT Community FROM Locations WHERE ((Locations.community
=
Bridgetown) AND (CommunityInventory.OrgID = Locations.OrgID)))

I've been told the last "Locations.OrgID" should be a number. (not
sure why)

I've tried every combination I can think of, but it just won't work.
I'm
about ready to give up, but this filter is very important and I'm
not
sure
how else to get the information I want (i.e. all the organizations
in a
given
community) without having to create a seperate records for each
seperate
office (which would be a lot of wasted time in many cases - many
organizations have quite a few locations and there is a lot of
information in
this database associated with each organization).

ANY help would be greatly appreciated! I've been trying to get this
work
for almost 2 weeks and I really need to get this database back to
the
users!

Thank you!

 

[Guest]

The Main report is based on the table "Organizations"
There are many subreports based on other tables including the subreport
"Locations" This is where all the different locations are listed with the
community names which is being searched.

I copied your code and got a syntax error. I also tried it with the line:
Dim strWhere As String just for fun.

Could there be something wrong somewhere else? My relationships seem to be
right with the two tables linked via OrgID.

Thanks again.

So your MAIN report is built directly on ONE table (?Organizations?) and you
want to filter the main report to show only Organizations that have a
locations in a specific community.

I think that what you want is something like the following.
StrWhere = "OrgID IN
(SELECT Locations.OrgID
FROM Locations
WHERE Locations.Community = """ & [Forms]![ChooseOrganization]![choosecom]
& """")

Basically, you need to construct a query that will return the Organizations
that you want to see based on a community. And then use that as a subquery
in a where clause.

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay,

My report is only based the regular tables. The filter is built into the
EventProcedure of the button (I had started putting the filter in a macro
and
basing the button on the macro, but can only seem to use the expression
builder in the macro's OpenReport Where Condition, I can't type this
code - I
don't think). This method has worked for my other buttons where the
filter
is only based on the main report and not a subreport.

I did create a query that got as far as filtering the subreport (offices)
based on my combo boxes, but not that extra step that to get the main
report
(organizations) filtered down to just showing the organizations with
offices
in the community chosen in my combo box. Here is the code of that query
(although it is working fine):

SELECT Locations.OrgID, Locations.LocationID, Locations.[Site Name],
Locations.Address, Locations.Community, Locations.[Postal Code],
Locations.[Phone Number], Locations.[Main Location],
CommunityInventory.OrgID
FROM CommunityInventory LEFT JOIN Locations ON CommunityInventory.OrgID =
Locations.OrgID
WHERE (((Locations.Community)=[Forms]![ChooseOrganization]![choosecom]));

Like I said, the report's filter is actually in the Event Procedure of the
button. What I posted previously is all I have for that.

Finally, I added the extra comma as you suggested, but the query now does
not work - I get an Enter Parameter Value dialogue box.

I usually pick up on this stuff pretty quickly, so this is really starting
to frustrate me and I've completely run out of ideas!!

If your report is based on a saved query, open the query in design view
and
switch to SQL view (Menu; VIEW: SQL) and copy and paste the SQL
statement.

If your report is based on a query that is set up as a query in the
record
source, then copy and paste the record source.

ALSO, your call to the report is incorrect. You should have another
comma
in the line.

DoCmd.OpenReport "CommunityInventory", acViewPreview, strWhere
It should read
DoCmd.OpenReport "CommunityInventory", acViewPreview, , strWhere

I know that the it says FilterName for the third argument, but that
argument
really is the name of a pre-built query. The fourth argument is a where
condition - which is "A string expression that's a valid SQL WHERE clause
without the word WHERE."
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ah ha! perhaps that is my problem (modifying the code as you had below
didn't work). It was suggested to me that this would be all I would
need
to
get the filter to work.

How would you suggest I proceed? I'm completely lost!

I wish I could upload the database so you could get a better idea...
Let
me
if what info you need to help me.

Thank you so much!

:

I would try making sure that you delimited the text string with either
quotation marks or an apostrophe. See below as an example of the
string

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE Locations.community = '" & [choosecom] & "' " & _
"AND Locations.OrgID = CommunityInventory.OrgID"

IF THIS doesn't work, perhaps you could post the complete query that
is
the record source for the report.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


cprav wrote:
I initially posted this question in the Reports forum as my subquery
is
creating a filtered report. However, I just can't seem to get this
subquery
down, and now that I know what it is I'm trying to accomplish (I had
never
even heard of subqueries when I posted my initial question) I
thought I
would
post here. I apologize if this is out of line.

Here is the info on my database:

It is a database with information on various organizations.
The main report has "Organization Name" and "County" fields.
Because each organization may have several offices, there is a
subform/report for Location. Included is a Community field to show
which
area of the county the office/location is in.

Next, to make this way user friendly, I have created a dialouge box
where
the user chooses from a list of counties which populates the
community
list.
This is working fine. The user then clicks a button to view all the
organizations in the chosen community.

To save myself from creating several extra reports (which are quite
complex
with several subforms) I have linked the button to a macro which
runs
the
Report with a filter. Other such buttons I have set up (based on
the
main
report) have worked fine, but of course, this is a bit tricker as
it's
a
subform.

This is the subquery I have created (I've played around with it a
lot
so I
can't remember how it started out, but this is what it looks like
now!)

Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE ((Locations.community = " & [choosecom] & ") " & _
"AND (CommunityInventory.OrgID = Locations.OrgID)))"

DoCmd.OpenReport "CommunityInventory", acViewPreview, strWhere

The code *does* bring up my report, but it is *not* filtered - it
shows
all
records.

I was advised of the 'immediate' window...thing (not big on
technical
terms!!) this is what comes up:

EXISTS (SELECT Community FROM Locations WHERE ((Locations.community
=
Bridgetown) AND (CommunityInventory.OrgID = Locations.OrgID)))

I've been told the last "Locations.OrgID" should be a number. (not
sure why)

I've tried every combination I can think of, but it just won't work.
I'm
about ready to give up, but this filter is very important and I'm
not
sure
how else to get the information I want (i.e. all the organizations
in a
given
community) without having to create a seperate records for each
seperate
office (which would be a lot of wasted time in many cases - many
organizations have quite a few locations and there is a lot of
information in
this database associated with each organization).

ANY help would be greatly appreciated! I've been trying to get this
work
for almost 2 weeks and I really need to get this database back to
the
users!

Thank you!

 

[John Spencer]

One step at a time.

Try the following as a query and see if it works
SELECT OrgID
FROM Locations

If that works, then try

SELECT *
FROM Organizations
WHERE OrgID in
(SELECT OrgID
FROM Locations)

If that works then try this
SELECT *
FROM Organizations
WHERE OrgID in
(SELECT OrgID
FROM Locations
WHERE Locations.Community =
[Forms]![ChooseOrganization]![choosecom] )

If any of these fail, then we know that we need to look at the record source
for the main report. If they all work without errors, then we know we need
to work on something else - probably the VBA that we are attempting to use.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

The Main report is based on the table "Organizations"
There are many subreports based on other tables including the subreport
"Locations" This is where all the different locations are listed with the
community names which is being searched.

I copied your code and got a syntax error. I also tried it with the line:
Dim strWhere As String just for fun.

Could there be something wrong somewhere else? My relationships seem to
be
right with the two tables linked via OrgID.

Thanks again.

:

S N I P

 

[Guest]

I THINK IT WORKED!!!

using your code and some information I googled I planted the code in a new
query in the Query's SQL view. I then created a macro that opened the report
with the filter as that saved Query and based a button on the macro!!!!

All this time I've trying to do that in the button!

Thanks!

One step at a time.

Try the following as a query and see if it works
SELECT OrgID
FROM Locations

If that works, then try

SELECT *
FROM Organizations
WHERE OrgID in
(SELECT OrgID
FROM Locations)

If that works then try this
SELECT *
FROM Organizations
WHERE OrgID in
(SELECT OrgID
FROM Locations
WHERE Locations.Community =
[Forms]![ChooseOrganization]![choosecom] )

If any of these fail, then we know that we need to look at the record source
for the main report. If they all work without errors, then we know we need
to work on something else - probably the VBA that we are attempting to use.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

The Main report is based on the table "Organizations"
There are many subreports based on other tables including the subreport
"Locations" This is where all the different locations are listed with the
community names which is being searched.

I copied your code and got a syntax error. I also tried it with the line:
Dim strWhere As String just for fun.

Could there be something wrong somewhere else? My relationships seem to
be
right with the two tables linked via OrgID.

Thanks again.

:

S N I P