Setting Criteria using parameters from a form

M

Misha

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
Click to expand...
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
 
K

KARL DEWEY

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.

Misha said:
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
Click to expand...
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
Click to expand...
 
M

Misha

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])))'"

KARL DEWEY said:
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.

Misha said:
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
Click to expand...
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
Click to expand...
Click to expand...
 
J

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.

Misha said:
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
Click to expand...
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
Click to expand...
 
M

Misha

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

John Spencer said:
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.

Misha said:
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
Click to expand...
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
Click to expand...
Click to expand...
 
J

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

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

John Spencer said:
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.

Misha said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a Search using a combo box and then creating a report. 4
Incorrect Data in Combo Box 3
Need to create totals using a Crosstab Query 3
data from different tables 6
sql 2
Select to reference an other record or row 1
IIF Function Within A Query 8
Parsing Month WorkSheet (one row/Project) into Folder & SubFolders by Project 0

Top