Create query based on fields specified in list box of form

[Burton]

I have a combo box with 3 values which represent fields (not 3 values in a
single field) in a table. As users select one of these fields in the combo
box and hit the command button I want the query to execute the query on that
field and the criteria to be -1. However, if they enter nothing, I want it to
return all records and not have -1 as the criteria.

The table name is staffing_matrix
The combo box is called combo37 and it is in a form called form1
The field names are "PHD", "Master's" and "Bachelors". They are populated
with -1's and 0's. For example, if someone has a PHD, then it is populated
with a -1, otherwise it is a zero.

I have seen several people ask somewhat similar questions, and it seems like
for them, they have always written coding into the command button (which is
called command21). I am a novice at coding and so the code that is typically
written there is over my head. I am wondering if there is a way to do it in
the query instead as the sql makes a little more sense to me than the VBA.
For example, in the "Field" row of the query, could I put something like
[form].[form1].[combo37] to assign the field from the combo box to the
"field" value in the query (I have tried this, by the way, and it didn't
work)?

Any help would be greatly appreciated. Again, I am somewhat of a novice, so
the more detail the better.

Thanks in advance

 

[Piet Linden]

I have a combo box with 3 values which represent fields (not 3 values in a
single field) in a table. As users select one of these fields in the combo
box and hit the command button I want the query to execute the query on that
field and the criteria to be -1. However, if they enter nothing, I want it to
return all records and not have -1 as the criteria.

The table name is staffing_matrix
The combo box is called combo37 and it is in a form called form1
The field names are "PHD", "Master's" and "Bachelors". They are populated
with -1's and 0's. For example, if someone has a PHD, then it is populated
with a -1, otherwise it is a zero.

I have seen several people ask somewhat similar questions, and it seems like
for them, they have always written coding into the command button (which is
called command21). I am a novice at coding and so the code that is typically
written there is over my head. I am wondering if there is a way to do it in
the query instead as the sql makes a little more sense to me than the VBA..
For example, in the "Field" row of the query, could I put something like
[form].[form1].[combo37] to assign the field from the combo box to the
"field" value in the query (I have tried this, by the way, and it didn't
work)?

Any help would be greatly appreciated. Again, I am somewhat of a novice, so
the more detail the better.

Thanks in advance

Sounds like the easiest way for you would be to create the 3 queries
and then use an If statement ot determine which query to execute.
Manipulating *anything* in a query except the WHERE clause will
require you to build the SQL statement on the fly. And then you'd
have to assign the new SQL statement to an existing query... if you
don't know what you're doing, it could get ugly fast. So I'd go with
the 3 queries approach.

 

[Burton]

Yea, that was an option; however, I am going to have several of these combo
boxes and some of them will have upwards of 15 options. My command button
will then execute all of the queries and come out with the results that meet
the criteria of all of the combo/list boxes. Consequently, creating a query
for each isn't really an option as it will get way too messy.

Thanks,

Burton

I have a combo box with 3 values which represent fields (not 3 values in a
single field) in a table. As users select one of these fields in the combo
box and hit the command button I want the query to execute the query on that
field and the criteria to be -1. However, if they enter nothing, I want it to
return all records and not have -1 as the criteria.

The table name is staffing_matrix
The combo box is called combo37 and it is in a form called form1
The field names are "PHD", "Master's" and "Bachelors". They are populated
with -1's and 0's. For example, if someone has a PHD, then it is populated
with a -1, otherwise it is a zero.

I have seen several people ask somewhat similar questions, and it seems like
for them, they have always written coding into the command button (which is
called command21). I am a novice at coding and so the code that is typically
written there is over my head. I am wondering if there is a way to do it in
the query instead as the sql makes a little more sense to me than the VBA..
For example, in the "Field" row of the query, could I put something like
[form].[form1].[combo37] to assign the field from the combo box to the
"field" value in the query (I have tried this, by the way, and it didn't
work)?

Any help would be greatly appreciated. Again, I am somewhat of a novice, so
the more detail the better.

Thanks in advance

Sounds like the easiest way for you would be to create the 3 queries
and then use an If statement ot determine which query to execute.
Manipulating *anything* in a query except the WHERE clause will
require you to build the SQL statement on the fly. And then you'd
have to assign the new SQL statement to an existing query... if you
don't know what you're doing, it could get ugly fast. So I'd go with
the 3 queries approach.

 

[John W. Vinson]

I have a combo box with 3 values which represent fields (not 3 values in a
single field) in a table. As users select one of these fields in the combo
box and hit the command button I want the query to execute the query on that
field and the criteria to be -1. However, if they enter nothing, I want it to
return all records and not have -1 as the criteria.

The table name is staffing_matrix
The combo box is called combo37 and it is in a form called form1
The field names are "PHD", "Master's" and "Bachelors". They are populated
with -1's and 0's. For example, if someone has a PHD, then it is populated
with a -1, otherwise it is a zero.

You're facing this problem because of a questionable table design.

Suppose things change and you find that you also need to account for MD, LLD
and DTh degrees... you'll need to restructure your table, your queries, your
forms, and all of your reports! Storing data in fieldnames is ALWAYS a bad
idea; a person's degree is data which should be stored in a Degree field. If
you want to allow for multiple degrees, you have a typical many to many
relationship, requiring a table of Degrees (with probably just one record with
values BA, BS, MA, MS, PhD, etc.) and a table of DegreesAttained (with fields
for the person's ID and the degree).

I have seen several people ask somewhat similar questions, and it seems like
for them, they have always written coding into the command button (which is
called command21). I am a novice at coding and so the code that is typically
written there is over my head. I am wondering if there is a way to do it in
the query instead as the sql makes a little more sense to me than the VBA.
For example, in the "Field" row of the query, could I put something like
[form].[form1].[combo37] to assign the field from the combo box to the
"field" value in the query (I have tried this, by the way, and it didn't
work)?

You *can* without code; you could use a criterion on the PHD field of

=(Forms!Form1!combo37 = "PhD")

and similarly for the other two fields.

I'd really suggest giving your forms and controls meaningful names rather than
the cryptic defaults, though.

 

[John W. Vinson]

You *can* without code; you could use a criterion on the PHD field of

=(Forms!Form1!combo37 = "PhD")

and similarly for the other two fields.

On second thought this will fail if someone has more than one degree!

Try a criterion in SQL view like:

([Forms]![Form1]![cbo37] = "Bachelor's" AND [Bachelor] = -1)
OR
([Forms]![Form1]![cbo37] = "Master's" AND [Masters] = -1)
OR
([Forms]![Form1]![cbo37] = "PhD" AND [PHD] = -1)

 

[Burton]

Yea, the fact that somebody can have more than one degree is the problem with
just having a degree field and then having the values be PHD, Masters,
Bachelors Associates, because it is conceivable that someone could have all
four in which case you would need to have 4 fields anyway. Additionally, if I
tried to put everything in one field, it would be even more tricky because I
have other instances where someone could have 15 different attributes for the
same field. This is the reason I steered away from doing this.

Thanks,

Burton

You *can* without code; you could use a criterion on the PHD field of

=(Forms!Form1!combo37 = "PhD")

and similarly for the other two fields.

On second thought this will fail if someone has more than one degree!

Try a criterion in SQL view like:

([Forms]![Form1]![cbo37] = "Bachelor's" AND [Bachelor] = -1)
OR
([Forms]![Form1]![cbo37] = "Master's" AND [Masters] = -1)
OR
([Forms]![Form1]![cbo37] = "PhD" AND [PHD] = -1)

 

[kate]

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> a écrit dans le
message de groupe de discussion :
(e-mail address removed)...

I have a combo box with 3 values which represent fields (not 3 values in a
single field) in a table. As users select one of these fields in the combo
box and hit the command button I want the query to execute the query on
that
field and the criteria to be -1. However, if they enter nothing, I want it
to
return all records and not have -1 as the criteria.

The table name is staffing_matrix
The combo box is called combo37 and it is in a form called form1
The field names are "PHD", "Master's" and "Bachelors". They are populated
with -1's and 0's. For example, if someone has a PHD, then it is populated
with a -1, otherwise it is a zero.

You're facing this problem because of a questionable table design.

Suppose things change and you find that you also need to account for MD,
LLD
and DTh degrees... you'll need to restructure your table, your queries,
your
forms, and all of your reports! Storing data in fieldnames is ALWAYS a bad
idea; a person's degree is data which should be stored in a Degree field.
If
you want to allow for multiple degrees, you have a typical many to many
relationship, requiring a table of Degrees (with probably just one record
with
values BA, BS, MA, MS, PhD, etc.) and a table of DegreesAttained (with
fields
for the person's ID and the degree).

I have seen several people ask somewhat similar questions, and it seems
like
for them, they have always written coding into the command button (which
is
called command21). I am a novice at coding and so the code that is
typically
written there is over my head. I am wondering if there is a way to do it
in
the query instead as the sql makes a little more sense to me than the VBA.
For example, in the "Field" row of the query, could I put something like
[form].[form1].[combo37] to assign the field from the combo box to the
"field" value in the query (I have tried this, by the way, and it didn't
work)?

You *can* without code; you could use a criterion on the PHD field of

=(Forms!Form1!combo37 = "PhD")

and similarly for the other two fields.

I'd really suggest giving your forms and controls meaningful names rather
than
the cryptic defaults, though.

 

[John W. Vinson]

Yea, the fact that somebody can have more than one degree is the problem with
just having a degree field and then having the values be PHD, Masters,
Bachelors Associates, because it is conceivable that someone could have all
four in which case you would need to have 4 fields anyway. Additionally, if I
tried to put everything in one field, it would be even more tricky because I
have other instances where someone could have 15 different attributes for the
same field. This is the reason I steered away from doing this.

Well, that's where you need another TABLE to model the many to many
relationship. You don't need fifteen fields, you need fifteen *records*.

 

[kate]

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> a écrit dans le
message de groupe de discussion :
(e-mail address removed)...

You *can* without code; you could use a criterion on the PHD field of

=(Forms!Form1!combo37 = "PhD")

and similarly for the other two fields.

On second thought this will fail if someone has more than one degree!

Try a criterion in SQL view like:

([Forms]![Form1]![cbo37] = "Bachelor's" AND [Bachelor] = -1)
OR
([Forms]![Form1]![cbo37] = "Master's" AND [Masters] = -1)
OR
([Forms]![Form1]![cbo37] = "PhD" AND [PHD] = -1)