Access 2007 Query or SQL

[DJ'sGiGi]

Very much a novice with Access so please forgive me if the terminology is incorrect. I've created a form that I'm trying to filter based on a "value list" field named Bethel Sub-1. I only want Access to show records that contain "Active Adult" or "Active Youth" in the Bethel Sub-1 field. I noticed that when I tried to create a query, Access created a column called Bethel Sub-1.Value. Can this be done with my current set up, or will it be easier to create the drop down in Bethel Sub-1 using a table reference versus a field value drop down list? Any help is appreciated (I've been at this for 3 days now, I GIVE UP!!!)

 

[Xander117]

What fields are you using to create your query? You should just be able to set your criteria to "active adult" or "active youth" so that it only looks for those.

 

[DJ'sGiGi]

I'm using Bethel Contacts.* (to show all fields), then adding Bethel Sub-1 to filter for "Active Adult" and "Active Youth"

 

[Norie]

What do you mean by a 'value-list' field?

Is it a text field that you've used Lookup with?

Have you tried just setting criteria against Bethel_1.Value?

eg 'Active Adult' Or 'Active Youth'

That's all you should need to return all records for either Active Adult or Active Youth.

The .Value, as far as I can see anyway, is just to return the value from the field.

It might be getting used because it is possible to create a dropdown with multiple columns.

 

[DJ'sGiGi]

The "value list" allows you to input a drop down list. So in the field named Bethel Sub-1, I have the following in the drop down list: Active Adult, Active Youth, Inactive, Deacesed, Former Member. In the report, I only want to show records that are labeled Active Adult or Active Youth. Generally, I can put these options into the query criteria rows and it works fine. For some reason, it's not working this time. This is my first time using the "value list" to create a drop down so I'm not sure if that's the problem or not.

 

[Norie]

Where exactly did you create the dropdown?

If it's on a form it would probably be better to use a query to populate it.

What I really don't get is where a dropdown would come into things if you are creating a report or query.

I can only think you might have created the dropdown in the actual table itself, which generally isn't a good idea.

 

[Xander117]

I think if understand correctly that you created a combo/list box on the form, and then set the row source type to value list, and in row source you have something like "active youth" "active adult" ?

If thats the case that box needs to relate to something in your table "bethel contacts" it would be better to have a column in your table like status or activity, then add that field to your form, right click on it and change the type to combo or whatever you need your drop down to be. Then you can create your query with a criteria to only display records with those choices.

 

[DJ'sGiGi]

That's exactly what I did (created it in the table.) So you think it would work better to create the drop down on the form or report? How would this affect the table?

 

[DJ'sGiGi]

@Xander117. I actually created the box in the design view of the table and added the value list there. In the query, I'm attempting to reference the field in the table and tell Access to only pull the data into the query if either of the conditions apply (i.e. Active Adult or Active Youth), the report is being fed by the query and is not functioning properly when I open the report.

 

[Norie]

It wouldn't really affect the table at all.

The only difference would be that you wouldn't have the dropdown when you openened the table in datasheet view.

No relationships would change, no data would be lost etc

If you are using the table's datasheet to input data it might be a little inconvenient but you should really use forms to input data anyway.