Setting Criteria using parameters from a form

[Guest]

Hello,

I am really new to access and I was wondering how to set up a query criteria
so that it takes information from a form and applies it to the query data.

I have created a form that uses a combo box to pick from a selection of
comparison values (ie <,>,=...) and then a control box which allows the user
to enter an amount to format the data in the database based on the selections.

I have been able to do this type of method with dates and going between
date1 and date2, however everytime I write the criteria calling the data from
the form it never works I either get no data back or run time error 2001.

Basicall, to summarize what I need it to do is the user chooses either < or

..etc and then types in a number into the control box and clicks the open

button which opens the query in normal view and you can see the filtered list.

For example, using the data below if the user picks > from the combo box and
types in 1000 in the control box the reult in the query should be:

ID Field1 Field2 Field3
1 100 100 100
2 250 250 500

Table Combo box:
ID Field1
1 <
2 >
3 =

Table Data
ID Field1 Field2 Field3
1 100 100 100
2 250 250 500
3 500 500 1000
4 1000 1000 2000
5 2000 2000 4000
6 4000 4000 8000

Query
SELECT test.Field1 AS Expr1, test.Field2 AS Expr2, test.Field3 AS Expr3,
test.id
FROM test
WHERE
((([test].[Field3])=IIf([Forms]![junk].[comp]=1,"<"+[Forms]![junk].[Num],">"+[Forms]![junk].[Num])));


Any Suggestions would be greately appreciated,

Misha

 

[Guest]

Try this --
SELECT test.Field1 AS Expr1, test.Field2 AS Expr2, test.Field3 AS Expr3,
test.id
FROM test
WHERE
((([test].[Field3])=IIf([Forms]![junk].[comp]=1,<[Forms]![junk].[Num],>[Forms]![junk].[Num])));

You will need to further nest an IIF for the = to work.

Hello,

I am really new to access and I was wondering how to set up a query criteria
so that it takes information from a form and applies it to the query data.

I have created a form that uses a combo box to pick from a selection of
comparison values (ie <,>,=...) and then a control box which allows the user
to enter an amount to format the data in the database based on the selections.

I have been able to do this type of method with dates and going between
date1 and date2, however everytime I write the criteria calling the data from
the form it never works I either get no data back or run time error 2001.

Basicall, to summarize what I need it to do is the user chooses either < or

..etc and then types in a number into the control box and clicks the open

button which opens the query in normal view and you can see the filtered list.

For example, using the data below if the user picks > from the combo box and
types in 1000 in the control box the reult in the query should be:

ID Field1 Field2 Field3
1 100 100 100
2 250 250 500

Table Combo box:
ID Field1
1 <
2 >
3 =

Table Data
ID Field1 Field2 Field3
1 100 100 100
2 250 250 500
3 500 500 1000
4 1000 1000 2000
5 2000 2000 4000
6 4000 4000 8000

Query
SELECT test.Field1 AS Expr1, test.Field2 AS Expr2, test.Field3 AS Expr3,
test.id
FROM test
WHERE
((([test].[Field3])=IIf([Forms]![junk].[comp]=1,"<"+[Forms]![junk].[Num],">"+[Forms]![junk].[Num])));


Any Suggestions would be greately appreciated,

Misha

 

[Guest]

Karl,

Thanks for the response...I tried that and I get an error:
"Syntax error (missing operator) in query expression
'((([test].[Field3])=IIf([Forms]![junk].[comp]=1,<[Forms]![junk].[Num],>[Forms]![junk].[Num])))'"

Try this --
SELECT test.Field1 AS Expr1, test.Field2 AS Expr2, test.Field3 AS Expr3,
test.id
FROM test
WHERE
((([test].[Field3])=IIf([Forms]![junk].[comp]=1,<[Forms]![junk].[Num],>[Forms]![junk].[Num])));

You will need to further nest an IIF for the = to work.

Hello,

I am really new to access and I was wondering how to set up a query criteria
so that it takes information from a form and applies it to the query data.

I have created a form that uses a combo box to pick from a selection of
comparison values (ie <,>,=...) and then a control box which allows the user
to enter an amount to format the data in the database based on the selections.

I have been able to do this type of method with dates and going between
date1 and date2, however everytime I write the criteria calling the data from
the form it never works I either get no data back or run time error 2001.

Basicall, to summarize what I need it to do is the user chooses either < or

..etc and then types in a number into the control box and clicks the open

button which opens the query in normal view and you can see the filtered list.

For example, using the data below if the user picks > from the combo box and
types in 1000 in the control box the reult in the query should be:

ID Field1 Field2 Field3
1 100 100 100
2 250 250 500

Table Combo box:
ID Field1
1 <
2 >
3 =

Table Data
ID Field1 Field2 Field3
1 100 100 100
2 250 250 500
3 500 500 1000
4 1000 1000 2000
5 2000 2000 4000
6 4000 4000 8000

Query
SELECT test.Field1 AS Expr1, test.Field2 AS Expr2, test.Field3 AS Expr3,
test.id
FROM test
WHERE
((([test].[Field3])=IIf([Forms]![junk].[comp]=1,"<"+[Forms]![junk].[Num],">"+[Forms]![junk].[Num])));


Any Suggestions would be greately appreciated,

Misha

 

[John Spencer]

The best way to do this is to build the SQL statement using VBA. You can't
set the operator in SQL using a parameter.

You can do it with something like the following. BUT it would get very
complicated, very quickly.

SELECT test.Field1 AS Expr1, test.Field2 AS Expr2, test.Field3 AS Expr3,
test.id
FROM test
WHERE (Test.Field3 = Forms!Junk!Num AND Forms!Junk!Comp = "=")
OR (Test.Field3 > Forms!Junk!Num AND Forms!Junk!Comp = ">")
OR (Test.Field3 < Forms!Junk!Num AND Forms!Junk!Comp = "<")

Can you open a report or a continuous form instead of opening a query? If
so, there are solutions that would work better.

Hello,

I am really new to access and I was wondering how to set up a query
criteria
so that it takes information from a form and applies it to the query data.

I have created a form that uses a combo box to pick from a selection of
comparison values (ie <,>,=...) and then a control box which allows the
user
to enter an amount to format the data in the database based on the
selections.

I have been able to do this type of method with dates and going between
date1 and date2, however everytime I write the criteria calling the data
from
the form it never works I either get no data back or run time error 2001.

Basicall, to summarize what I need it to do is the user chooses either <
or

..etc and then types in a number into the control box and clicks the open

button which opens the query in normal view and you can see the filtered
list.

For example, using the data below if the user picks > from the combo box
and
types in 1000 in the control box the reult in the query should be:

ID Field1 Field2 Field3
1 100 100 100
2 250 250 500

Table Combo box:
ID Field1
1 <
2 >
3 =

Table Data
ID Field1 Field2 Field3
1 100 100 100
2 250 250 500
3 500 500 1000
4 1000 1000 2000
5 2000 2000 4000
6 4000 4000 8000

Query
SELECT test.Field1 AS Expr1, test.Field2 AS Expr2, test.Field3 AS Expr3,
test.id
FROM test
WHERE
((([test].[Field3])=IIf([Forms]![junk].[comp]=1,"<"+[Forms]![junk].[Num],">"+[Forms]![junk].[Num])));


Any Suggestions would be greately appreciated,

Misha

 

[Guest]

The reason I was going for the Query was to use it in a report

The best way to do this is to build the SQL statement using VBA. You can't
set the operator in SQL using a parameter.

You can do it with something like the following. BUT it would get very
complicated, very quickly.

SELECT test.Field1 AS Expr1, test.Field2 AS Expr2, test.Field3 AS Expr3,
test.id
FROM test
WHERE (Test.Field3 = Forms!Junk!Num AND Forms!Junk!Comp = "=")
OR (Test.Field3 > Forms!Junk!Num AND Forms!Junk!Comp = ">")
OR (Test.Field3 < Forms!Junk!Num AND Forms!Junk!Comp = "<")

Can you open a report or a continuous form instead of opening a query? If
so, there are solutions that would work better.

Hello,

I am really new to access and I was wondering how to set up a query
criteria
so that it takes information from a form and applies it to the query data.

I have created a form that uses a combo box to pick from a selection of
comparison values (ie <,>,=...) and then a control box which allows the
user
to enter an amount to format the data in the database based on the
selections.

I have been able to do this type of method with dates and going between
date1 and date2, however everytime I write the criteria calling the data
from
the form it never works I either get no data back or run time error 2001.

Basicall, to summarize what I need it to do is the user chooses either <
or

..etc and then types in a number into the control box and clicks the open

button which opens the query in normal view and you can see the filtered
list.

For example, using the data below if the user picks > from the combo box
and
types in 1000 in the control box the reult in the query should be:

ID Field1 Field2 Field3
1 100 100 100
2 250 250 500

Table Combo box:
ID Field1
1 <
2 >
3 =

Table Data
ID Field1 Field2 Field3
1 100 100 100
2 250 250 500
3 500 500 1000
4 1000 1000 2000
5 2000 2000 4000
6 4000 4000 8000

Query
SELECT test.Field1 AS Expr1, test.Field2 AS Expr2, test.Field3 AS Expr3,
test.id
FROM test
WHERE
((([test].[Field3])=IIf([Forms]![junk].[comp]=1,"<"+[Forms]![junk].[Num],">"+[Forms]![junk].[Num])));


Any Suggestions would be greately appreciated,

Misha

 

[John Spencer]

If you are calling the report from the form you can use code something like
the following in a button's click event.

Private Sub btnPrintReport_Click()
Dim strWhere as String

strWhere = "Field3 " & Forms!Junk!Comp & " " & Forms!Junk!Num

DoCmd.OpenReport "Your Report Name",,,strWhere

End Sub

The reason I was going for the Query was to use it in a report

The best way to do this is to build the SQL statement using VBA. You
can't
set the operator in SQL using a parameter.

You can do it with something like the following. BUT it would get very
complicated, very quickly.

SELECT test.Field1 AS Expr1, test.Field2 AS Expr2, test.Field3 AS Expr3,
test.id
FROM test
WHERE (Test.Field3 = Forms!Junk!Num AND Forms!Junk!Comp = "=")
OR (Test.Field3 > Forms!Junk!Num AND Forms!Junk!Comp = ">")
OR (Test.Field3 < Forms!Junk!Num AND Forms!Junk!Comp = "<")

Can you open a report or a continuous form instead of opening a query?
If
so, there are solutions that would work better.

Hello,

I am really new to access and I was wondering how to set up a query
criteria
so that it takes information from a form and applies it to the query
data.

I have created a form that uses a combo box to pick from a selection of
comparison values (ie <,>,=...) and then a control box which allows the
user
to enter an amount to format the data in the database based on the
selections.

I have been able to do this type of method with dates and going between
date1 and date2, however everytime I write the criteria calling the
data
from
the form it never works I either get no data back or run time error
2001.

Basicall, to summarize what I need it to do is the user chooses either
<
or
..etc and then types in a number into the control box and clicks the
open
button which opens the query in normal view and you can see the
filtered
list.

For example, using the data below if the user picks > from the combo
box
and
types in 1000 in the control box the reult in the query should be:

ID Field1 Field2 Field3
1 100 100 100
2 250 250 500

Table Combo box:
ID Field1
1 <
2 >
3 =

Table Data
ID Field1 Field2 Field3
1 100 100 100
2 250 250 500
3 500 500 1000
4 1000 1000 2000
5 2000 2000 4000
6 4000 4000 8000

Query
SELECT test.Field1 AS Expr1, test.Field2 AS Expr2, test.Field3 AS
Expr3,
test.id
FROM test
WHERE
((([test].[Field3])=IIf([Forms]![junk].[comp]=1,"<"+[Forms]![junk].[Num],">"+[Forms]![junk].[Num])));


Any Suggestions would be greately appreciated,

Misha