Getting query to look up the values of combo boxes then display da

[bUncE]

Morning all,
(Im using Access2003)
I have a form with 3 combo boxes on it, one displays some Field Names, one
displays some logical operators (>, <>, Like etc) the third one looks up the
value of the Field Names (so if someone selects LastName in the first combo
box it gives you the options of all the LastNames in the third combo box)
However, i have a query which searches for the values of the last two boxes
and displays the appropriate result...
It doesnt work.... well actually it does work but only with one extra
expression!?
Here is the one bit from the query that i can get to work (without the
second combo box doing anything might i add!)

[LastName] Like "*" & [Forms]![frmTest]![cboValueToFind] & "*" Or
[Forms]![frmTest]![cboValueToFind] Is Null

Does anyone know how i can include the second combo box as criteria for the
query results? I've been racking my brain for hours, any help will be greatly
appreciated

Thanks in advance

Mike Harkess

 

[John Spencer]

You can use parameters (references to form controls) to pass values to a
query, you cannot use them to pass operators or field names to the query.

You can do what you want by using VBA to build the query or to build a where
clause that you can use to filter the source of a report. WHERE do you
intend to use the results of the query - in a form (as the source) or in a
report (as the source)?

Do you know much about VBA?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[bUncE]

Thanks for the reply John,
Im basically using it as an easy way to query the results in a table.
Its for an admin mode of a database so the manager can literally click
something like combo1= "RepID" combo2 = "=" combo3 = "Joe Bloggs"
this would bring up all data corresponding to the criteria selected in the
combos.
All i want is for the manager to view the results in a query style sheet, no
reports are involved at all. Am i going about it the wrong way?
Yeah i know a fair bit of vba, i try to use it almost every day.

You can use parameters (references to form controls) to pass values to a
query, you cannot use them to pass operators or field names to the query.

You can do what you want by using VBA to build the query or to build a where
clause that you can use to filter the source of a report. WHERE do you
intend to use the results of the query - in a form (as the source) or in a
report (as the source)?

Do you know much about VBA?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Morning all,
(Im using Access2003)
I have a form with 3 combo boxes on it, one displays some Field Names, one
displays some logical operators (>, <>, Like etc) the third one looks up
the
value of the Field Names (so if someone selects LastName in the first
combo
box it gives you the options of all the LastNames in the third combo box)
However, i have a query which searches for the values of the last two
boxes
and displays the appropriate result...
It doesnt work.... well actually it does work but only with one extra
expression!?
Here is the one bit from the query that i can get to work (without the
second combo box doing anything might i add!)

[LastName] Like "*" & [Forms]![frmTest]![cboValueToFind] & "*" Or
[Forms]![frmTest]![cboValueToFind] Is Null

Does anyone know how i can include the second combo box as criteria for
the
query results? I've been racking my brain for hours, any help will be
greatly
appreciated

Thanks in advance

Mike Harkess

 

[John Spencer]

Save yourself a lot of coding and effort.

You might want to consider the FREE Query By Form applet at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Form'


***FEATURES***

The DH QBF is a complete query by form applet that can be easily integrated
into any existing Access application. Typically, the functionality provided
by DH QBF can replace many "canned" reports. The developer imports several
forms, tables, a query, and a report from the DH_QBF.mdb, creates some
master queries, and deploys.

The developer creates one or more master queries that join tables, alias
field names, create calculated columns, etc. The users can then select a
master query (datasource) from a drop-down and then select up to 30 fields
from the master query. Users can define sorting and criteria as well as
grouping and totaling. All of this "design" information is stored in two
tables for re-use.

The results of the queries are displayed in a datasheet subform contained in
a main form. The main form has options to send/export the records to print,
Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most
formats allow he user to automatically open the target application. The Word
merge process will open a new Word document and link to the merge fields.

--
Duane Hookom
MS Access MVP

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

Thanks for the reply John,
Im basically using it as an easy way to query the results in a table.
Its for an admin mode of a database so the manager can literally click
something like combo1= "RepID" combo2 = "=" combo3 = "Joe Bloggs"
this would bring up all data corresponding to the criteria selected in the
combos.
All i want is for the manager to view the results in a query style sheet,
no
reports are involved at all. Am i going about it the wrong way?
Yeah i know a fair bit of vba, i try to use it almost every day.

You can use parameters (references to form controls) to pass values to a
query, you cannot use them to pass operators or field names to the query.

You can do what you want by using VBA to build the query or to build a
where
clause that you can use to filter the source of a report. WHERE do you
intend to use the results of the query - in a form (as the source) or in
a
report (as the source)?

Do you know much about VBA?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Morning all,
(Im using Access2003)
I have a form with 3 combo boxes on it, one displays some Field Names,
one
displays some logical operators (>, <>, Like etc) the third one looks
up
the
value of the Field Names (so if someone selects LastName in the first
combo
box it gives you the options of all the LastNames in the third combo
box)
However, i have a query which searches for the values of the last two
boxes
and displays the appropriate result...
It doesnt work.... well actually it does work but only with one extra
expression!?
Here is the one bit from the query that i can get to work (without the
second combo box doing anything might i add!)

[LastName] Like "*" & [Forms]![frmTest]![cboValueToFind] & "*" Or
[Forms]![frmTest]![cboValueToFind] Is Null

Does anyone know how i can include the second combo box as criteria for
the
query results? I've been racking my brain for hours, any help will be
greatly
appreciated

Thanks in advance

Mike Harkess

 

[bUncE]

The effort and coding are part of the fun for me, not too happy bout using
any external software either.

Also the link is dead.

Thanks anyway

Save yourself a lot of coding and effort.

You might want to consider the FREE Query By Form applet at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Form'


***FEATURES***

The DH QBF is a complete query by form applet that can be easily integrated
into any existing Access application. Typically, the functionality provided
by DH QBF can replace many "canned" reports. The developer imports several
forms, tables, a query, and a report from the DH_QBF.mdb, creates some
master queries, and deploys.

The developer creates one or more master queries that join tables, alias
field names, create calculated columns, etc. The users can then select a
master query (datasource) from a drop-down and then select up to 30 fields
from the master query. Users can define sorting and criteria as well as
grouping and totaling. All of this "design" information is stored in two
tables for re-use.

The results of the queries are displayed in a datasheet subform contained in
a main form. The main form has options to send/export the records to print,
Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most
formats allow he user to automatically open the target application. The Word
merge process will open a new Word document and link to the merge fields.

--
Duane Hookom
MS Access MVP

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

Thanks for the reply John,
Im basically using it as an easy way to query the results in a table.
Its for an admin mode of a database so the manager can literally click
something like combo1= "RepID" combo2 = "=" combo3 = "Joe Bloggs"
this would bring up all data corresponding to the criteria selected in the
combos.
All i want is for the manager to view the results in a query style sheet,
no
reports are involved at all. Am i going about it the wrong way?
Yeah i know a fair bit of vba, i try to use it almost every day.

You can use parameters (references to form controls) to pass values to a
query, you cannot use them to pass operators or field names to the query.

You can do what you want by using VBA to build the query or to build a
where
clause that you can use to filter the source of a report. WHERE do you
intend to use the results of the query - in a form (as the source) or in
a
report (as the source)?

Do you know much about VBA?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Morning all,
(Im using Access2003)
I have a form with 3 combo boxes on it, one displays some Field Names,
one
displays some logical operators (>, <>, Like etc) the third one looks
up
the
value of the Field Names (so if someone selects LastName in the first
combo
box it gives you the options of all the LastNames in the third combo
box)
However, i have a query which searches for the values of the last two
boxes
and displays the appropriate result...
It doesnt work.... well actually it does work but only with one extra
expression!?
Here is the one bit from the query that i can get to work (without the
second combo box doing anything might i add!)

[LastName] Like "*" & [Forms]![frmTest]![cboValueToFind] & "*" Or
[Forms]![frmTest]![cboValueToFind] Is Null

Does anyone know how i can include the second combo box as criteria for
the
query results? I've been racking my brain for hours, any help will be
greatly
appreciated

Thanks in advance

Mike Harkess

 

[John Spencer]

Well, I just tested the link and it works for me.

As far as external software, the code and forms are all there and you can
simply study what Duane has done and adapt it if you don't want to use his
method.

Another source that has sample form and code to do the same thing. is

http://www.mvps.org/access/forms/frm0045.htm

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

The effort and coding are part of the fun for me, not too happy bout using
any external software either.

Also the link is dead.

Thanks anyway

Save yourself a lot of coding and effort.

You might want to consider the FREE Query By Form applet at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Form'


***FEATURES***

The DH QBF is a complete query by form applet that can be easily
integrated
into any existing Access application. Typically, the functionality
provided
by DH QBF can replace many "canned" reports. The developer imports
several
forms, tables, a query, and a report from the DH_QBF.mdb, creates some
master queries, and deploys.

The developer creates one or more master queries that join tables, alias
field names, create calculated columns, etc. The users can then select a
master query (datasource) from a drop-down and then select up to 30
fields
from the master query. Users can define sorting and criteria as well as
grouping and totaling. All of this "design" information is stored in two
tables for re-use.

The results of the queries are displayed in a datasheet subform contained
in
a main form. The main form has options to send/export the records to
print,
Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph.
Most
formats allow he user to automatically open the target application. The
Word
merge process will open a new Word document and link to the merge fields.

--
Duane Hookom
MS Access MVP

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

Thanks for the reply John,
Im basically using it as an easy way to query the results in a table.
Its for an admin mode of a database so the manager can literally click
something like combo1= "RepID" combo2 = "=" combo3 = "Joe Bloggs"
this would bring up all data corresponding to the criteria selected in
the
combos.
All i want is for the manager to view the results in a query style
sheet,
no
reports are involved at all. Am i going about it the wrong way?
Yeah i know a fair bit of vba, i try to use it almost every day.


:

You can use parameters (references to form controls) to pass values to
a
query, you cannot use them to pass operators or field names to the
query.

You can do what you want by using VBA to build the query or to build a
where
clause that you can use to filter the source of a report. WHERE do
you
intend to use the results of the query - in a form (as the source) or
in
a
report (as the source)?

Do you know much about VBA?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Morning all,
(Im using Access2003)
I have a form with 3 combo boxes on it, one displays some Field
Names,
one
displays some logical operators (>, <>, Like etc) the third one
looks
up
the
value of the Field Names (so if someone selects LastName in the
first
combo
box it gives you the options of all the LastNames in the third combo
box)
However, i have a query which searches for the values of the last
two
boxes
and displays the appropriate result...
It doesnt work.... well actually it does work but only with one
extra
expression!?
Here is the one bit from the query that i can get to work (without
the
second combo box doing anything might i add!)

[LastName] Like "*" & [Forms]![frmTest]![cboValueToFind] & "*" Or
[Forms]![frmTest]![cboValueToFind] Is Null

Does anyone know how i can include the second combo box as criteria
for
the
query results? I've been racking my brain for hours, any help will
be
greatly
appreciated

Thanks in advance

Mike Harkess

 

[bUncE]

Hmm strange, the pcs here are slightly restricted when it comes to internet
access.
That one works fine though, thanks for taking the time to help

Mike

Well, I just tested the link and it works for me.

As far as external software, the code and forms are all there and you can
simply study what Duane has done and adapt it if you don't want to use his
method.

Another source that has sample form and code to do the same thing. is

http://www.mvps.org/access/forms/frm0045.htm

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

The effort and coding are part of the fun for me, not too happy bout using
any external software either.

Also the link is dead.

Thanks anyway

Save yourself a lot of coding and effort.

You might want to consider the FREE Query By Form applet at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Form'


***FEATURES***

The DH QBF is a complete query by form applet that can be easily
integrated
into any existing Access application. Typically, the functionality
provided
by DH QBF can replace many "canned" reports. The developer imports
several
forms, tables, a query, and a report from the DH_QBF.mdb, creates some
master queries, and deploys.

The developer creates one or more master queries that join tables, alias
field names, create calculated columns, etc. The users can then select a
master query (datasource) from a drop-down and then select up to 30
fields
from the master query. Users can define sorting and criteria as well as
grouping and totaling. All of this "design" information is stored in two
tables for re-use.

The results of the queries are displayed in a datasheet subform contained
in
a main form. The main form has options to send/export the records to
print,
Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph.
Most
formats allow he user to automatically open the target application. The
Word
merge process will open a new Word document and link to the merge fields.

--
Duane Hookom
MS Access MVP

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

Thanks for the reply John,
Im basically using it as an easy way to query the results in a table.
Its for an admin mode of a database so the manager can literally click
something like combo1= "RepID" combo2 = "=" combo3 = "Joe Bloggs"
this would bring up all data corresponding to the criteria selected in
the
combos.
All i want is for the manager to view the results in a query style
sheet,
no
reports are involved at all. Am i going about it the wrong way?
Yeah i know a fair bit of vba, i try to use it almost every day.


:

You can use parameters (references to form controls) to pass values to
a
query, you cannot use them to pass operators or field names to the
query.

You can do what you want by using VBA to build the query or to build a
where
clause that you can use to filter the source of a report. WHERE do
you
intend to use the results of the query - in a form (as the source) or
in
a
report (as the source)?

Do you know much about VBA?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Morning all,
(Im using Access2003)
I have a form with 3 combo boxes on it, one displays some Field
Names,
one
displays some logical operators (>, <>, Like etc) the third one
looks
up
the
value of the Field Names (so if someone selects LastName in the
first
combo
box it gives you the options of all the LastNames in the third combo
box)
However, i have a query which searches for the values of the last
two
boxes
and displays the appropriate result...
It doesnt work.... well actually it does work but only with one
extra
expression!?
Here is the one bit from the query that i can get to work (without
the
second combo box doing anything might i add!)

[LastName] Like "*" & [Forms]![frmTest]![cboValueToFind] & "*" Or
[Forms]![frmTest]![cboValueToFind] Is Null

Does anyone know how i can include the second combo box as criteria
for
the
query results? I've been racking my brain for hours, any help will
be
greatly
appreciated

Thanks in advance

Mike Harkess