Form Will Not Extract Specified Data When Opening a Report

[Guest]

Hi,

I have a form where the contents of a list box is taken from a query
containing the counties in the Uk. When i click a county and open a report it
should display a summary of all customers in that county. I have named the
list box "list8" and have used the criteria [Forms]![Main Form MBS]![List8]
on all queries which the report pulls from so it will only open the selected
counties information....

Problem is it seems to run the query but then just opens a blank report with
no data populated? I have set up a report like this before with no probelms
it just doesn't seem to be picking up the data?

Please Help!!!

Many Thanks

 

[Rick Brandt]

Hi,

I have a form where the contents of a list box is taken from a query
containing the counties in the Uk. When i click a county and open a
report it should display a summary of all customers in that county. I
have named the list box "list8" and have used the criteria
[Forms]![Main Form MBS]![List8] on all queries which the report pulls
from so it will only open the selected counties information....

Problem is it seems to run the query but then just opens a blank
report with no data populated? I have set up a report like this
before with no probelms it just doesn't seem to be picking up the
data?

Please Help!!!

Many Thanks

Look at the datasheet of the query. Does it return records? Does list8
have only one column (including hidden ones)? If there is more than one the
bound column might not be what you think it is. Do you need to filter on
the "name" of the county or on a numeric code that is the key for the
county?\

 

[Guest]

Look at the datasheet of the query. Does it return records? Does list8

have only one column (including hidden ones)? If there is more than one the
bound column might not be what you think it is. Do you need to filter on
the "name" of the county or on a numeric code that is the key for the
county?\

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


Yes the data source of the list box returns results & list8 only has 1 column. I am filtering on county name as my main table of data contains a field with county names for each of the customers. The whole process works fine until i insert the [Forms]![Main Form MBS]![List8] criteria into all of the queries which the report pulls from.

For example if i added an 'open report' button to my form, clicked
'aberdeen' from list8 (list box) and clicked the open report button it would
generate a report for aberdeen but also for every other county in list8.
Therefore when i add the [Forms]![Main Form MBS]![List8] command into my
'city lookup' query (used by the report) it should only display results for
aberdeen - but thats when it comes back blank....

 

[Rick Brandt]

For example if i added an 'open report' button to my form, clicked

'aberdeen' from list8 (list box) and clicked the open report button
it would generate a report for aberdeen but also for every other
county in list8. Therefore when i add the [Forms]![Main Form
MBS]![List8] command into my 'city lookup' query (used by the report)
it should only display results for aberdeen - but thats when it comes
back blank....

Post the SQL of your query.

 

[Douglas J. Steele]

Is the list box set to allow MultiSelect? If so, then you can't simply refer
to it in a query like you can other form controls.

 

[Guest]

Post the SQL of your query.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


SELECT Count([RDA Data MBS].PartnerID) AS CountOfPartnerID, [Post Code Listings].City, [Post Code Listings].Region, Sum([RDA Data MBS].NumbrEmploy) AS SumOfNumbrEmploy

FROM [RDA Data MBS] INNER JOIN [Post Code Listings] ON [RDA Data
MBS].PostCodeLeft = [Post Code Listings].[Post Code 1st Letters]
GROUP BY [Post Code Listings].City, [Post Code Listings].Region
HAVING ((([Post Code Listings].City)=[Forms]![Main Form MBS]![List8]));

 

[Guest]

Sorry made a mistake in my earlier post - The list box contains city's and
not county's. The city's are distinguished by their post codes (hence the SQL)

Thanks!

 

[Rick Brandt]

Post the SQL of your query.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


SELECT Count([RDA Data MBS].PartnerID) AS CountOfPartnerID, [Post
Code Listings].City, [Post Code Listings].Region, Sum([RDA Data
MBS].NumbrEmploy) AS SumOfNumbrEmploy

FROM [RDA Data MBS] INNER JOIN [Post Code Listings] ON [RDA Data
MBS].PostCodeLeft = [Post Code Listings].[Post Code 1st Letters]
GROUP BY [Post Code Listings].City, [Post Code Listings].Region
HAVING ((([Post Code Listings].City)=[Forms]![Main Form
MBS]![List8]));

I thought you were trying to match a "county". Your SQL is trying to match
a "city". Also that would be better as a WHERE clause rather than a HAVING
clause.

 

[Rick Brandt]

Sorry made a mistake in my earlier post - The list box contains
city's and not county's. The city's are distinguished by their post
codes (hence the SQL)

Thanks!

As Douglas pointed out this won't work if your ListBox has Multi-Select
enabled. Is that the case? A Mulit-Select ListBox always has a value of
Null.