query criteria

[Guest]

I have an unbound form in which the user can choose the option to search for
road numbers a30,a38 or a30/a38 and/or in which county they want to search
for, either devon or cornwall. The search results are then fed into a query
which is displayed in a report however I can't seem to get the query to
return both county and route selections only, it keeps returning its findings
as if it were and 'and/or' criteria and it is more of an 'only' criteria that
I'm looking for.
I have filters for all combinations which are saved as queries, however I
can't seem to ensure that the correct one is selected based on the users
selection. I hope this is understandable, it seems complicated to me and I'm
going around in circles. Perhaps I am approaching this from the completely
wrong angle. Any suggestions gratefully received.

 

[Guest]

Hi

I assume you have a column in the query with route and another with county.
In the criteria rows for both either place.

Route column criteria = [enter route]
County criteria = [enter county]
this will prompt the user to enter the route name and county

or

On a form have a combo with all the possible routes called SelectRoute and
another with the counties called SelectCounty

the criteria row in this case would be something like

[Forms]![FormName]![SelectRoute]
[Forms]![FormName]![SelectCounty]

On the second combo to be "clicked" you have an OnClick event to open the
query - something like this.

Private Sub SelectCounty_Click()
DoCmd.OpenQuery "QueryName", acViewNormal, acEdit
End Sub

Or you could open a report which is based on the query

Private Sub SelectCounty_Click()
DoCmd.OpenReport "ReportName", acViewPreview, "", "", acNormal
End Sub


Hope this helps

 

[Guest]

Thanks, I got that bit but on the report I get all the county and all the
route selections displayed but I don't want them all I want the ones that
meet both the search criteria. for example, there are 20 a30 route entries
and 10 devon entries, I don't want to return them all, I want just the a30
route in devon of which there may only be 5. The complication is that the
user may want a30/devon or cornwall and similarly want to change to the
a38/devon or cornwall results. Or they may wish to see all a30 or a38. As
you can see there are many options for the search and the results have to be
specific to the user request. Its doing my little swede in now. Any ideas?

Hi

I assume you have a column in the query with route and another with county.
In the criteria rows for both either place.

Route column criteria = [enter route]
County criteria = [enter county]
this will prompt the user to enter the route name and county

or

On a form have a combo with all the possible routes called SelectRoute and
another with the counties called SelectCounty

the criteria row in this case would be something like

[Forms]![FormName]![SelectRoute]
[Forms]![FormName]![SelectCounty]

On the second combo to be "clicked" you have an OnClick event to open the
query - something like this.

Private Sub SelectCounty_Click()
DoCmd.OpenQuery "QueryName", acViewNormal, acEdit
End Sub

Or you could open a report which is based on the query

Private Sub SelectCounty_Click()
DoCmd.OpenReport "ReportName", acViewPreview, "", "", acNormal
End Sub


Hope this helps



--
Wayne
Manchester, England.

I have an unbound form in which the user can choose the option to search for
road numbers a30,a38 or a30/a38 and/or in which county they want to search
for, either devon or cornwall. The search results are then fed into a query
which is displayed in a report however I can't seem to get the query to
return both county and route selections only, it keeps returning its findings
as if it were and 'and/or' criteria and it is more of an 'only' criteria that
I'm looking for.
I have filters for all combinations which are saved as queries, however I
can't seem to ensure that the correct one is selected based on the users
selection. I hope this is understandable, it seems complicated to me and I'm
going around in circles. Perhaps I am approaching this from the completely
wrong angle. Any suggestions gratefully received.

 

[Guest]

It sounds to me as if the report is based on a query that does not have the
filter in it. Base the query on the query that you have placed the
criteria(s) and this should filter the report.

Test it by opening the query 1st (you should be prompted for the criteria)
enter these then when the query opens it should give the info you need.


--
Wayne
Manchester, England.

Thanks, I got that bit but on the report I get all the county and all the
route selections displayed but I don't want them all I want the ones that
meet both the search criteria. for example, there are 20 a30 route entries
and 10 devon entries, I don't want to return them all, I want just the a30
route in devon of which there may only be 5. The complication is that the
user may want a30/devon or cornwall and similarly want to change to the
a38/devon or cornwall results. Or they may wish to see all a30 or a38. As
you can see there are many options for the search and the results have to be
specific to the user request. Its doing my little swede in now. Any ideas?

Hi

I assume you have a column in the query with route and another with county.
In the criteria rows for both either place.

Route column criteria = [enter route]
County criteria = [enter county]
this will prompt the user to enter the route name and county

or

On a form have a combo with all the possible routes called SelectRoute and
another with the counties called SelectCounty

the criteria row in this case would be something like

[Forms]![FormName]![SelectRoute]
[Forms]![FormName]![SelectCounty]

On the second combo to be "clicked" you have an OnClick event to open the
query - something like this.

Private Sub SelectCounty_Click()
DoCmd.OpenQuery "QueryName", acViewNormal, acEdit
End Sub

Or you could open a report which is based on the query

Private Sub SelectCounty_Click()
DoCmd.OpenReport "ReportName", acViewPreview, "", "", acNormal
End Sub


Hope this helps



--
Wayne
Manchester, England.

I have an unbound form in which the user can choose the option to search for
road numbers a30,a38 or a30/a38 and/or in which county they want to search
for, either devon or cornwall. The search results are then fed into a query
which is displayed in a report however I can't seem to get the query to
return both county and route selections only, it keeps returning its findings
as if it were and 'and/or' criteria and it is more of an 'only' criteria that
I'm looking for.
I have filters for all combinations which are saved as queries, however I
can't seem to ensure that the correct one is selected based on the users
selection. I hope this is understandable, it seems complicated to me and I'm
going around in circles. Perhaps I am approaching this from the completely
wrong angle. Any suggestions gratefully received.

 

[Guest]

ok, that makes sense. I've tried most things that I can think of but I'm
quite new to Access and the more I find out the more complicated the things
that I think I should do to get it functioning are becoming. Thanks for your
help with this.

It sounds to me as if the report is based on a query that does not have the
filter in it. Base the query on the query that you have placed the
criteria(s) and this should filter the report.

Test it by opening the query 1st (you should be prompted for the criteria)
enter these then when the query opens it should give the info you need.


--
Wayne
Manchester, England.

Thanks, I got that bit but on the report I get all the county and all the
route selections displayed but I don't want them all I want the ones that
meet both the search criteria. for example, there are 20 a30 route entries
and 10 devon entries, I don't want to return them all, I want just the a30
route in devon of which there may only be 5. The complication is that the
user may want a30/devon or cornwall and similarly want to change to the
a38/devon or cornwall results. Or they may wish to see all a30 or a38. As
you can see there are many options for the search and the results have to be
specific to the user request. Its doing my little swede in now. Any ideas?

Hi

I assume you have a column in the query with route and another with county.
In the criteria rows for both either place.

Route column criteria = [enter route]
County criteria = [enter county]
this will prompt the user to enter the route name and county

or

On a form have a combo with all the possible routes called SelectRoute and
another with the counties called SelectCounty

the criteria row in this case would be something like

[Forms]![FormName]![SelectRoute]
[Forms]![FormName]![SelectCounty]

On the second combo to be "clicked" you have an OnClick event to open the
query - something like this.

Private Sub SelectCounty_Click()
DoCmd.OpenQuery "QueryName", acViewNormal, acEdit
End Sub

Or you could open a report which is based on the query

Private Sub SelectCounty_Click()
DoCmd.OpenReport "ReportName", acViewPreview, "", "", acNormal
End Sub


Hope this helps



--
Wayne
Manchester, England.



:

I have an unbound form in which the user can choose the option to search for
road numbers a30,a38 or a30/a38 and/or in which county they want to search
for, either devon or cornwall. The search results are then fed into a query
which is displayed in a report however I can't seem to get the query to
return both county and route selections only, it keeps returning its findings
as if it were and 'and/or' criteria and it is more of an 'only' criteria that
I'm looking for.
I have filters for all combinations which are saved as queries, however I
can't seem to ensure that the correct one is selected based on the users
selection. I hope this is understandable, it seems complicated to me and I'm
going around in circles. Perhaps I am approaching this from the completely
wrong angle. Any suggestions gratefully received.