how to filter combo box with if statement

[Daniel M]

I have a combo box i need to filter with a query. the query is needs to be
different based on a text box. the text box will be a number, ie: 1000-1999
will be one selection in the combo box, 2000-2999 will be a different
selection in the combo box. Can someone give me an idea on how to do this?
Thanks.

 

[Arvin Meyer [MVP]]

In your query, make a reference to the textbox. In your query's criteria
box, enter:

Forms!FormName!TextboxName

 

[Daniel M]

Ok i'm not sure exactly how to do this. let me explain a little more.

I have a table for returns, it lists failures and productids.
i have a table for products that lists producttypes and productids.
We have serialnumbers that define our products. ie: 1000-1999 would be
product A, id=1. serialnumber 2000-2999 would be product B, id=2.

I need to select all failures from the returns table where the productid
matches the product in text box 1.

Can you help be a little more specific on how to filter based on text box 1?
Thanks.

 

[Arvin Meyer [MVP]]

In your query, in the criteria box of productid, right-click and use Build,
or just put in:

Forms![YourForm Name]![Textbox 1 Name]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Daniel M]

I'm still not sure i understand. can you help with the syntax?

if Forms![AEntry]![txtSerialNumber] =1000 to 1999 then productid =1
select failures from returnfailures where productid =1

I dont know how to incorperate the if into the query.

thanks.

In your query, in the criteria box of productid, right-click and use Build,
or just put in:

Forms![YourForm Name]![Textbox 1 Name]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ok i'm not sure exactly how to do this. let me explain a little more.

I have a table for returns, it lists failures and productids.
i have a table for products that lists producttypes and productids.
We have serialnumbers that define our products. ie: 1000-1999 would be
product A, id=1. serialnumber 2000-2999 would be product B, id=2.

I need to select all failures from the returns table where the productid
matches the product in text box 1.

Can you help be a little more specific on how to filter based on text box
1?
Thanks.

 

[Arvin Meyer [MVP]]

I'm not sure I understand what you are trying to do. What does the select
statement have to do with the form?

If you are trying to do an update query to change all productid's to 1 if
the serial number is between 1000 and 1999, then do that in an update query.
It doesn't need the form at all. Nor do you need an If statement.

UPDATE returnfailures
SET productid =1
WHERE (((SerialNumber)>=1000 And (SerialNumber)<=1999));

The second query would then be the select query that you run after you've
updated the productid field. Or even simpler, just run a query like:

SELECT failures
FROM returnfailures
WHERE (((SerialNumber)>=1000 And (SerialNumber)<=1999));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I'm still not sure i understand. can you help with the syntax?

if Forms![AEntry]![txtSerialNumber] =1000 to 1999 then productid =1
select failures from returnfailures where productid =1

I dont know how to incorperate the if into the query.

thanks.

In your query, in the criteria box of productid, right-click and use
Build,
or just put in:

Forms![YourForm Name]![Textbox 1 Name]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ok i'm not sure exactly how to do this. let me explain a little more.

I have a table for returns, it lists failures and productids.
i have a table for products that lists producttypes and productids.
We have serialnumbers that define our products. ie: 1000-1999 would be
product A, id=1. serialnumber 2000-2999 would be product B, id=2.

I need to select all failures from the returns table where the
productid
matches the product in text box 1.

Can you help be a little more specific on how to filter based on text
box
1?
Thanks.

:

In your query, make a reference to the textbox. In your query's
criteria
box, enter:

Forms!FormName!TextboxName
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a combo box i need to filter with a query. the query is needs
to
be
different based on a text box. the text box will be a number, ie:
1000-1999
will be one selection in the combo box, 2000-2999 will be a
different
selection in the combo box. Can someone give me an idea on how to do
this?
Thanks.

 

[Daniel M]

Ok here is what i am trying to do. I have a serial number text box. based on
that number i need to filter another combo box. here is the logic.

if Forms![AEntry]![txtSerialNumber] =1000 to 1999 then productid =1
the combo box should filter based on productid of 1
select failures from returnfailures where productid =1

if Forms![AEntry]![txtSerialNumber] =2000 to 2999 then productid =2
the combo box should filter based on productid of 2
select failures from returnfailures where productid =2

I need to figure out how to determine the productid to filter on from the
serialnumber text box and then filter the combo box for that id.

thanks for the help!

I'm not sure I understand what you are trying to do. What does the select
statement have to do with the form?

If you are trying to do an update query to change all productid's to 1 if
the serial number is between 1000 and 1999, then do that in an update query.
It doesn't need the form at all. Nor do you need an If statement.

UPDATE returnfailures
SET productid =1
WHERE (((SerialNumber)>=1000 And (SerialNumber)<=1999));

The second query would then be the select query that you run after you've
updated the productid field. Or even simpler, just run a query like:

SELECT failures
FROM returnfailures
WHERE (((SerialNumber)>=1000 And (SerialNumber)<=1999));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I'm still not sure i understand. can you help with the syntax?

if Forms![AEntry]![txtSerialNumber] =1000 to 1999 then productid =1
select failures from returnfailures where productid =1

I dont know how to incorperate the if into the query.

thanks.

In your query, in the criteria box of productid, right-click and use
Build,
or just put in:

Forms![YourForm Name]![Textbox 1 Name]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Ok i'm not sure exactly how to do this. let me explain a little more.

I have a table for returns, it lists failures and productids.
i have a table for products that lists producttypes and productids.
We have serialnumbers that define our products. ie: 1000-1999 would be
product A, id=1. serialnumber 2000-2999 would be product B, id=2.

I need to select all failures from the returns table where the
productid
matches the product in text box 1.

Can you help be a little more specific on how to filter based on text
box
1?
Thanks.

:

In your query, make a reference to the textbox. In your query's
criteria
box, enter:

Forms!FormName!TextboxName
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a combo box i need to filter with a query. the query is needs
to
be
different based on a text box. the text box will be a number, ie:
1000-1999
will be one selection in the combo box, 2000-2999 will be a
different
selection in the combo box. Can someone give me an idea on how to do
this?
Thanks.

 

[Arvin Meyer [MVP]]

If there aren't too many entries in the combo box, I'd write a function that
would return the value in the combo. Something like this (untested aircode):

Private Sub txtSerialNumber_AfterUpdate()
Select Case Me.txtSerialNumber

Case 1000 To 1999
Me.cboWhatever.RowSource = "select failures from returnfailures where
productid =1"

Case 2000 To 2999
Me.cboWhatever.RowSource = "select failures from returnfailures where
productid =2"
Case Else

End Select
End Sub

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ok here is what i am trying to do. I have a serial number text box. based
on
that number i need to filter another combo box. here is the logic.

if Forms![AEntry]![txtSerialNumber] =1000 to 1999 then productid =1
the combo box should filter based on productid of 1
select failures from returnfailures where productid =1

if Forms![AEntry]![txtSerialNumber] =2000 to 2999 then productid =2
the combo box should filter based on productid of 2
select failures from returnfailures where productid =2

I need to figure out how to determine the productid to filter on from the
serialnumber text box and then filter the combo box for that id.

thanks for the help!

I'm not sure I understand what you are trying to do. What does the select
statement have to do with the form?

If you are trying to do an update query to change all productid's to 1 if
the serial number is between 1000 and 1999, then do that in an update
query.
It doesn't need the form at all. Nor do you need an If statement.

UPDATE returnfailures
SET productid =1
WHERE (((SerialNumber)>=1000 And (SerialNumber)<=1999));

The second query would then be the select query that you run after you've
updated the productid field. Or even simpler, just run a query like:

SELECT failures
FROM returnfailures
WHERE (((SerialNumber)>=1000 And (SerialNumber)<=1999));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I'm still not sure i understand. can you help with the syntax?

if Forms![AEntry]![txtSerialNumber] =1000 to 1999 then productid =1
select failures from returnfailures where productid =1

I dont know how to incorperate the if into the query.

thanks.
:

In your query, in the criteria box of productid, right-click and use
Build,
or just put in:

Forms![YourForm Name]![Textbox 1 Name]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Ok i'm not sure exactly how to do this. let me explain a little
more.

I have a table for returns, it lists failures and productids.
i have a table for products that lists producttypes and productids.
We have serialnumbers that define our products. ie: 1000-1999 would
be
product A, id=1. serialnumber 2000-2999 would be product B, id=2.

I need to select all failures from the returns table where the
productid
matches the product in text box 1.

Can you help be a little more specific on how to filter based on
text
box
1?
Thanks.

:

In your query, make a reference to the textbox. In your query's
criteria
box, enter:

Forms!FormName!TextboxName
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a combo box i need to filter with a query. the query is
needs
to
be
different based on a text box. the text box will be a number, ie:
1000-1999
will be one selection in the combo box, 2000-2999 will be a
different
selection in the combo box. Can someone give me an idea on how to
do
this?
Thanks.