Combo Box input that will update field reference in WHERE statement

[superfly5203]

I have a query that get its parameters from a form, and everything except one thing is working great, thanks to the many suggestions from this forum. However, I have one last problem that is giving me a headache.

I have a combo box that has 7 options, one for each "stage" a record is in. In SQL i have the following statement:

"WHERE tblChangeBasic.contract IN(" & strCriteria & ") AND tblChangeTypes.TypeCode IN(" & strCriteria2 & ") AND tblChangeDates.actualdef BETWEEN [Forms]![Date_Search]![startdate] and [Forms]![Date_Search]![enddate] ; "

What I need to happen is, when the user selects one of the options from the combo box, for example "PPM Complete" the last part of my statement would change to tblChangeDates.actualPPM or whatever else corresponds to their selection. This way they can select what stage they want to look at, instead of being forced to look at the hard coded stage. Right now my combo box values don't directly correspond to a field name, so I know i have to make those match up, but I just can't figure out how to make this work.

Thanks for any suggestions.

 

[jdraw]

For focused responses I suggest you give us an overview of the "business" involved.

Stage x of Y stages would seem too indicate another table (to me). But I'm guessing, since we have no details nor context in which to consider your post.

 

[superfly5203]

Sorry about that.

My database tracks information relating to contract changes (a change is essentially a small project). The DB has 5 tables, one for basic info, one for dates (this is the one my question relates too), one for dollars, one for types (there are 12 different "types" or categories one change can be), and a people table. The tblChangeDates has all the dates for stages inside, since this is a historical database and 99% of the time once a record is input it will never change, I didn't think I needed a separate table for each stage.

The form based query I'm working on now displays all the records that meet the criteria the user selects. Right now the form has a list box for one of the three contracts to select from, who worked on the change, and what types the user wants to view. Those three work great (well besides me still trying to figure out the if null show all records code) and aren't giving me issues. I also have to text boxes where dates can be input, a start and end date for a search. Right now these two boxes are tied directly to the last stage of a change (when the change is finished), but I would like to have the user be able to select the stage (via a combo box) they want to base the search off of (this will update my sql code and change the field to the corresponding selection made in the combo box).

For example, it is often useful to see how many changes were initiated in a time frame. So the code would change from:

"WHERE tblChangeBasic.contract IN(" & strCriteria & ") AND tblChangeTypes.TypeCode IN(" & strCriteria2 & ") AND tblChangeDates.actualdef BETWEEN [Forms]![Date_Search]![startdate] and [Forms]![Date_Search]![enddate] ; "

to

"WHERE tblChangeBasic.contract IN(" & strCriteria & ") AND tblChangeTypes.TypeCode IN(" & strCriteria2 & ") AND tblChangeDates.actualproposal BETWEEN [Forms]![Date_Search]![startdate] and [Forms]![Date_Search]![enddate] ; "

The only change made there is the reference right before the BETWEEN statement.

As I said previously, I already have a combo box, stageselect, with the different stages of a change (this one doesn't need multi select like the contract, people, and type fields) but it doesn't do anything right now.

I hope that helps, if not please let me know what i need to expand more on.

Thanks

 

[superfly5203]

Do I need to provide some additional info? or is what i'm trying to do not possible?

 

[jdraw]

You need to describe the business process(es) and things involved (simple English). Not the combo and the form, textbox etc...

Seems you have Contract, Stage, People and some sort of Change and Date info.

 

[superfly5203]

OK, I'll try again.

The database tracks small projects, each project is completed by one of three different companies. The timeline for each project is as follows; the project is started, a proposal is delivered, the proposal is evaluated, documents are drafted, the work is negotiated with the supplier, and finally the project is complete. Each of these steps happens after the one before it is completed, they can't be completed out of order.

Each project can fall into one of 12 different categories (ex. High dollar value, high priority, high dollar value and high priority).

My question is, on one of my queries based on an unbound form, I want to show all projects that are on one of the steps I mentioned earlier, but I need the user to select the step they want to base the query on. Right now it defaults to the project is complete stage, I would like to have the user be able to say they want to base their search on any step.

I hope that helped.

 

[jdraw]

I mocked up a model based on your posts. Hopefully, it hits the main parts. Your database will have a structure similar to this.


For your form and query (in overview)

A combo could list the various stages that a project must follow. These are records in the Stage table in the diagram.
You would select the Stage from the combo
You would query the Project and related tables where the ProjectStage = Stage selected form the combo and use this as the recordsource of the form.

good luck.