Criteria for Query

  • Thread starter Thread starter The Pikey
  • Start date Start date
T

The Pikey

SELECT [Main Data Table].Review, [Main Data Table].EC, [Main Data
Table].Error_Code, [Main Data Table].UpndReview1, [Main Data
Table].Error_description, [Main Data Table].Error_contact, [Main Data
Table].Err_date, [Main Data Table].RevDate
FROM [Main Data Table]
WHERE ((([Main Data Table].Config_Name)=[Enter Name Below]) AND (([Main Data
Table].RelDate) Is Null))
ORDER BY [Main Data Table].Error_Code;

The above query lists all records assigned to a person using [Config_Name]
field as the criteria. This field in the [Main Data Table] is a Combo Box.

How do I amend the query so it gives me a combo box to select the criteria,
rather than having to type the name in manually each time?
 
Hi,
You would have to use a form with a combo box populated with all possible
choices and then reference the combo box as criteria from the query.
Something like:
= Forms!yourFormName!yourCombo

Also, you shouldn't be using lookup fields in tables, they can cause you problems.
A form with a combo box is the way to go.
 
Thanks for the reply Dan, although I'm not entirely sure what you mean and I
probably didn't explain it as I should have.

The [Config_Name] field in [Main Data Table] is a text field which is
populated in a form using a list of values from a separate table [Authorised
Reviewers].

I want to be able to run the query and select the criteria for the query
using a drop down list rather than having to type the name in full.

I have tried referencing the list of names straight from their own table (
=[Authorised Reviewers]![Name]) and I also created a new form [ReviewNames]
as you suggested with a Combo box [ReviewName] which had a control source of
=[Authorised Reviewers]![Name] and then pointed to this in the criteria of
the query (= Forms![ReviewNames]![ReviewName]) but it is still looking for a
manual input for the criteria rather than a drop down list.

Please help!!




Dan Artuso said:
Hi,
You would have to use a form with a combo box populated with all possible
choices and then reference the combo box as criteria from the query.
Something like:
= Forms!yourFormName!yourCombo

Also, you shouldn't be using lookup fields in tables, they can cause you problems.
A form with a combo box is the way to go.

--
HTH
Dan Artuso, Access MVP


SELECT [Main Data Table].Review, [Main Data Table].EC, [Main Data
Table].Error_Code, [Main Data Table].UpndReview1, [Main Data
Table].Error_description, [Main Data Table].Error_contact, [Main Data
Table].Err_date, [Main Data Table].RevDate
FROM [Main Data Table]
WHERE ((([Main Data Table].Config_Name)=[Enter Name Below]) AND (([Main Data
Table].RelDate) Is Null))
ORDER BY [Main Data Table].Error_Code;

The above query lists all records assigned to a person using [Config_Name]
field as the criteria. This field in the [Main Data Table] is a Combo Box.

How do I amend the query so it gives me a combo box to select the criteria,
rather than having to type the name in manually each time?
Click to expand...
Click to expand...
 
Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm


End Quoting John Vinson

The said:
Thanks for the reply Dan, although I'm not entirely sure what you mean and I
probably didn't explain it as I should have.

The [Config_Name] field in [Main Data Table] is a text field which is
populated in a form using a list of values from a separate table [Authorised
Reviewers].

I want to be able to run the query and select the criteria for the query
using a drop down list rather than having to type the name in full.

I have tried referencing the list of names straight from their own table (
=[Authorised Reviewers]![Name]) and I also created a new form [ReviewNames]
as you suggested with a Combo box [ReviewName] which had a control source of
=[Authorised Reviewers]![Name] and then pointed to this in the criteria of
the query (= Forms![ReviewNames]![ReviewName]) but it is still looking for a
manual input for the criteria rather than a drop down list.

Please help!!

Dan Artuso said:
Hi,
You would have to use a form with a combo box populated with all possible
choices and then reference the combo box as criteria from the query.
Something like:
= Forms!yourFormName!yourCombo

Also, you shouldn't be using lookup fields in tables, they can cause you problems.
A form with a combo box is the way to go.

--
HTH
Dan Artuso, Access MVP


SELECT [Main Data Table].Review, [Main Data Table].EC, [Main Data
Table].Error_Code, [Main Data Table].UpndReview1, [Main Data
Table].Error_description, [Main Data Table].Error_contact, [Main Data
Table].Err_date, [Main Data Table].RevDate
FROM [Main Data Table]
WHERE ((([Main Data Table].Config_Name)=[Enter Name Below]) AND (([Main Data
Table].RelDate) Is Null))
ORDER BY [Main Data Table].Error_Code;

The above query lists all records assigned to a person using [Config_Name]
field as the criteria. This field in the [Main Data Table] is a Combo Box.

How do I amend the query so it gives me a combo box to select the criteria,
rather than having to type the name in manually each time?
Click to expand...
Click to expand...
Click to expand...
 
Wow, that really is a detailed explanation!

--
HTH
Dan Artuso, Access MVP


John Spencer (MVP) said:
Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm


End Quoting John Vinson

The said:
Thanks for the reply Dan, although I'm not entirely sure what you mean and I
probably didn't explain it as I should have.

The [Config_Name] field in [Main Data Table] is a text field which is
populated in a form using a list of values from a separate table [Authorised
Reviewers].

I want to be able to run the query and select the criteria for the query
using a drop down list rather than having to type the name in full.

I have tried referencing the list of names straight from their own table (
=[Authorised Reviewers]![Name]) and I also created a new form [ReviewNames]
as you suggested with a Combo box [ReviewName] which had a control source of
=[Authorised Reviewers]![Name] and then pointed to this in the criteria of
the query (= Forms![ReviewNames]![ReviewName]) but it is still looking for a
manual input for the criteria rather than a drop down list.

Please help!!

Dan Artuso said:
Hi,
You would have to use a form with a combo box populated with all possible
choices and then reference the combo box as criteria from the query.
Something like:
= Forms!yourFormName!yourCombo

Also, you shouldn't be using lookup fields in tables, they can cause you problems.
A form with a combo box is the way to go.

--
HTH
Dan Artuso, Access MVP


SELECT [Main Data Table].Review, [Main Data Table].EC, [Main Data
Table].Error_Code, [Main Data Table].UpndReview1, [Main Data
Table].Error_description, [Main Data Table].Error_contact, [Main Data
Table].Err_date, [Main Data Table].RevDate
FROM [Main Data Table]
WHERE ((([Main Data Table].Config_Name)=[Enter Name Below]) AND (([Main Data
Table].RelDate) Is Null))
ORDER BY [Main Data Table].Error_Code;

The above query lists all records assigned to a person using [Config_Name]
field as the criteria. This field in the [Main Data Table] is a Combo Box.

How do I amend the query so it gives me a combo box to select the criteria,
rather than having to type the name in manually each time?
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the link John.

Excellent site for tips and tricks!


John Spencer (MVP) said:
Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm


End Quoting John Vinson

The said:
Thanks for the reply Dan, although I'm not entirely sure what you mean and I
probably didn't explain it as I should have.

The [Config_Name] field in [Main Data Table] is a text field which is
populated in a form using a list of values from a separate table [Authorised
Reviewers].

I want to be able to run the query and select the criteria for the query
using a drop down list rather than having to type the name in full.

I have tried referencing the list of names straight from their own table (
=[Authorised Reviewers]![Name]) and I also created a new form [ReviewNames]
as you suggested with a Combo box [ReviewName] which had a control source of
=[Authorised Reviewers]![Name] and then pointed to this in the criteria of
the query (= Forms![ReviewNames]![ReviewName]) but it is still looking for a
manual input for the criteria rather than a drop down list.

Please help!!

Dan Artuso said:
Hi,
You would have to use a form with a combo box populated with all possible
choices and then reference the combo box as criteria from the query.
Something like:
= Forms!yourFormName!yourCombo

Also, you shouldn't be using lookup fields in tables, they can cause
Click to expand...
you
problems.
A form with a combo box is the way to go.

--
HTH
Dan Artuso, Access MVP


SELECT [Main Data Table].Review, [Main Data Table].EC, [Main Data
Table].Error_Code, [Main Data Table].UpndReview1, [Main Data
Table].Error_description, [Main Data Table].Error_contact, [Main Data
Table].Err_date, [Main Data Table].RevDate
FROM [Main Data Table]
WHERE ((([Main Data Table].Config_Name)=[Enter Name Below]) AND
Click to expand...
(([Main
Data
Table].RelDate) Is Null))
ORDER BY [Main Data Table].Error_Code;

The above query lists all records assigned to a person using [Config_Name]
field as the criteria. This field in the [Main Data Table] is a
Click to expand...
Combo
Box.
How do I amend the query so it gives me a combo box to select the criteria,
rather than having to type the name in manually each time?
Click to expand...
Click to expand...
Click to expand...
 
Yeah. I've decided to stop trying to explain it on my own and just use the
link. I also browsed through some of the other articles on the site and think
that I may start referring some of the questions to the appropriate URL. I may
put a short explanation in front of it and then tell the poster to look here
(Insert URL) for a detailed explanation - with pictures.

Dan said:
Wow, that really is a detailed explanation!

--
HTH
Dan Artuso, Access MVP

John Spencer (MVP) said:
Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm


End Quoting John Vinson

The said:
Thanks for the reply Dan, although I'm not entirely sure what you mean and I
probably didn't explain it as I should have.

The [Config_Name] field in [Main Data Table] is a text field which is
populated in a form using a list of values from a separate table [Authorised
Reviewers].

I want to be able to run the query and select the criteria for the query
using a drop down list rather than having to type the name in full.

I have tried referencing the list of names straight from their own table (
=[Authorised Reviewers]![Name]) and I also created a new form [ReviewNames]
as you suggested with a Combo box [ReviewName] which had a control source of
=[Authorised Reviewers]![Name] and then pointed to this in the criteria of
the query (= Forms![ReviewNames]![ReviewName]) but it is still looking for a
manual input for the criteria rather than a drop down list.

Please help!!

Hi,
You would have to use a form with a combo box populated with all possible
choices and then reference the combo box as criteria from the query.
Something like:
= Forms!yourFormName!yourCombo

Also, you shouldn't be using lookup fields in tables, they can cause you
problems.
A form with a combo box is the way to go.

--
HTH
Dan Artuso, Access MVP


SELECT [Main Data Table].Review, [Main Data Table].EC, [Main Data
Table].Error_Code, [Main Data Table].UpndReview1, [Main Data
Table].Error_description, [Main Data Table].Error_contact, [Main Data
Table].Err_date, [Main Data Table].RevDate
FROM [Main Data Table]
WHERE ((([Main Data Table].Config_Name)=[Enter Name Below]) AND (([Main
Data
Table].RelDate) Is Null))
ORDER BY [Main Data Table].Error_Code;

The above query lists all records assigned to a person using
[Config_Name]
field as the criteria. This field in the [Main Data Table] is a Combo
Box.

How do I amend the query so it gives me a combo box to select the
criteria,
rather than having to type the name in manually each time?
Click to expand...
Click to expand...
Click to expand...
 
That's one heck of a site. A lot of work went into it.
I will start referring people there as well.

--
HTH
Dan Artuso, Access MVP


John Spencer (MVP) said:
Yeah. I've decided to stop trying to explain it on my own and just use the
link. I also browsed through some of the other articles on the site and think
that I may start referring some of the questions to the appropriate URL. I may
put a short explanation in front of it and then tell the poster to look here
(Insert URL) for a detailed explanation - with pictures.

Dan said:
Wow, that really is a detailed explanation!

--
HTH
Dan Artuso, Access MVP

John Spencer (MVP) said:
Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm


End Quoting John Vinson

The Pikey wrote:

Thanks for the reply Dan, although I'm not entirely sure what you mean and I
probably didn't explain it as I should have.

The [Config_Name] field in [Main Data Table] is a text field which is
populated in a form using a list of values from a separate table [Authorised
Reviewers].

I want to be able to run the query and select the criteria for the query
using a drop down list rather than having to type the name in full.

I have tried referencing the list of names straight from their own table (
=[Authorised Reviewers]![Name]) and I also created a new form [ReviewNames]
as you suggested with a Combo box [ReviewName] which had a control source of
=[Authorised Reviewers]![Name] and then pointed to this in the criteria of
the query (= Forms![ReviewNames]![ReviewName]) but it is still looking for a
manual input for the criteria rather than a drop down list.

Please help!!

Hi,
You would have to use a form with a combo box populated with all possible
choices and then reference the combo box as criteria from the query.
Something like:
= Forms!yourFormName!yourCombo

Also, you shouldn't be using lookup fields in tables, they can cause you
problems.
A form with a combo box is the way to go.

--
HTH
Dan Artuso, Access MVP


SELECT [Main Data Table].Review, [Main Data Table].EC, [Main Data
Table].Error_Code, [Main Data Table].UpndReview1, [Main Data
Table].Error_description, [Main Data Table].Error_contact, [Main Data
Table].Err_date, [Main Data Table].RevDate
FROM [Main Data Table]
WHERE ((([Main Data Table].Config_Name)=[Enter Name Below]) AND (([Main
Data
Table].RelDate) Is Null))
ORDER BY [Main Data Table].Error_Code;

The above query lists all records assigned to a person using
[Config_Name]
field as the criteria. This field in the [Main Data Table] is a Combo
Box.

How do I amend the query so it gives me a combo box to select the
criteria,
rather than having to type the name in manually each time?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top