Query criteria from Combo box

[cbende2]

Hello all,

I have two combo boxes.



My first combo box has the options:
Calendar
Off Calendar


If "Calendar" is selected, I would like to click a button that runs query1 with this criteria: "Like "*" & "12/31" & "*" AND "*" & Forms!MyForm!cbo2"

Example:

If "Off Calendar" is selected, I would like to click the same button that runs query1 with this criteria: "Not Like "*" & "12/31" & "*" AND "*" & Forms!MyForm!cbo2"

Example:

I'm having trouble setting this up...

[orange]

Please define Calendar Year and Off Calendar Year and show sample usage.

Couldn't you use IsDate(yourDate) or similar to validate a date?

[cbende2]

EDIT:
I'm really hoping to get it down to one query..

[orange]

Glad you have it resolved.

[cbende2]

Please don't mark as solved, as I'm really trying to narrow it to one query... Actually I need it to be one query.

[cbende2]

Orange, hopefully my problem is made more clear in my original post, see post #1 please.

[orange]

Can you post sample of your form and tables? Only a few records to illustrate your data - remove/don't post anything confidential. Subset of your database in accdb format.
I really need to see the record source for the Form and rowsource of the combo.

[cbende2]

Sure, I need to clean some info up. I'll post it as soon as I can. Thanks orange

[cbende2]

Attached Database.

Right now I have two queries.

I would like to get this to work using only one query.

[orange]

There are 11 distinct PlanYearEnd in your table of 367 records. (which seems strange-- but I really don't know what you're trying to do in business terms)
What exactly do these dates mean in your business? Perhaps you could tell us what a Plan is to help with some context.
Forms!MyForm!cbo2 (from your post #1) doesn't exist --did you change this?
Year is a reserved word in Access --make life easier and give it a local name meaningful to you (eg MyYear)

You appear to have 2 buttons called "Warning", and "BtnOffCal" that are superimposed or partially overlapping???
There is no "btnCal" which causes an error in

btnCal.Visible = False

I don't use macros.

[cbende2]

Well, I'm not really sure what they are using the plan year end for. (I think its for filing certain forms and doing different testings involved with retirement plans)

I'm given orders, and I go and do the design.

my reference to Forms!MyForm!cbo2 was just an example.

Year is the name of cbo2, I will change that in the future.

The way I had it set up was with 3 buttons.

Warning was visible first, Once "Off Calendar" Or "Calendar" is chosen, either btnCal becomes visible or btnOffCal becomes visible with the two queries attached to their respective buttons.

I'm trying to set up one query that based on "Off Calendar" or "Calendar" will set the criteria as I described in post #1.

I really can't explain it in more plain English than I did in post #1.

[orange]

But there is no btnCal. The btnOffCal and another called Warning basically share the same space on the Form.

Can you tell us more about the Orders?

I'm given orders

Orders or requirements indicate WHAT they want to happen. You make a design (and/or options) and show the client; then get the confirmation that you and the client are in agreement on what will be done/delivered.How you implement it in Access/vba/macros is up to you (within some data management/maintenance/standards guidlelines) but it must do what was agreed upon.

I'm not really sure what they are using the plan year end for.

I would have expected this to be mentioned in the overview of the requirements/orders. You can always ask "Where exactly does the Plan Year End relate to the business/requirement. I want to make sure I understand."
When you are dealing with a 1 table design, all kinds of questions are in scope in my view. Understanding the requirement/orders in context is key to getting a design that meets the business needs.

It seems to me that you have a very narrow view of the overall requirement to be dealing with a form and unsure of what it's to be used for.

[June7]

Bind form or report to query and use code to set filter property of form or report.

[cbende2]

I was hoping to leave out the complete context because I know its not necessary for solving this problem.

Let me make this theoretical.

Lets say I have form1.

On form1 I have two combo boxes. Cbo1 and cbo2.

In Cbo1, I have two options, "Calendar" and "Off Calendar."

In cbo2, I have 3 options, "2013" "2014" and "2015"

Also, on form1 I have a button(btn1) that opens a query(query1).

I would like for query1's criteria to be

Like "*" & "12/31" & "*" And Like "*" & Forms!MyForm!Cbo2

IF "Calendar" is selected in Cbo1.



I would like for query1's criteria to be

Not Like "*" & "12/31" & "*" And Like "*" & Forms!MyForm!Cbo2

IF "Off Calendar" is selected in Cbo1.


I'm having trouble setting the criteria based off of a combobox, we really don't need to delve into the details of my fields/tables/recordsources. The database I attached is a bad example because I'm using two different queries when my desired outcome is using one query.

And also, the 3rd button is hidden behind the second button.

[cbende2]

Bind form or report to query and use code to set filter property of form or report.

I would prefer not to do this, because several other filters will be applied, and once toggle filter is used I will lose the original filter I'm trying to get to. This is why I need it to be in the query criteria.