Using 2 form fields as one criterium

[Dom]

Hi guys,
I know it sounds simple, but I cannot get it to work. I was willing to
create a query which uses a form as criteria. Basically what I want to do is
allow an user (via the form) to choose what he/she wants to search for. For
example, I'd like to allow the user to define whether to use "greater than",
"less than" or "equal" and to choose which amount. Example: select all
companies that have assets greater than 100 (>100). I have no knowledge of
VBA so I've trying to create that by using two fields in a form (one field
showing the operator - >, < =; and the second showing the values - 100, 200,
etc), both combo boxes which I tried to concatenate as a criteria in a query
(Forms![frm01_Main]![cmb_Filed1] & Forms![frm01_Main]![cmb_Filed2])

I'd be happy with a free text field as well, where the user can type ">100".
The most important thing is to have it in a form that will allow users to
determine what they want to search for.

 

[tina]

when you concatenate the form references in a query, the expression returns
a *string*, as

">100"

this will give you a WHERE clause in the SQL statement that is essentially

NumberFieldName = ">100"

so you get no return values, and may get a Type Mismatch error, depending on
the field's data type.

i can't think of any way to do this without using VBA to write the SQL
statement. if you'll post the SQL statement of your query, and explain
where/how you're using the query, maybe we can help you tackle the VBA
issue.

hth

 

[Dom]

Hi Tina,

thanks for your reply. Well, the SQL statement of my query is this:

SELECT tbl_MIS_Data.[Total assets]
FROM tbl_MIS_Data
GROUP BY tbl_MIS_Data.[Total assets]
HAVING (((tbl_MIS_Data.[Total assets])>100));

I'd like to have the "HAVING" statement being read from a form where I could
let the user choose two vairables at a time: the operator (<,>,=) and the
value (100, 200, etc) that would work together to create one single
critereon. This way, the user would be able to choose coys which have assets
above, below or even equal to a value that they would pick. Does it make
sense? The statement that I copied above works pefectly, however it does not
allow the user to define a different value outside the query, through a form.

when you concatenate the form references in a query, the expression returns
a *string*, as

">100"

this will give you a WHERE clause in the SQL statement that is essentially

NumberFieldName = ">100"

so you get no return values, and may get a Type Mismatch error, depending on
the field's data type.

i can't think of any way to do this without using VBA to write the SQL
statement. if you'll post the SQL statement of your query, and explain
where/how you're using the query, maybe we can help you tackle the VBA
issue.

hth

Hi guys,
I know it sounds simple, but I cannot get it to work. I was willing to
create a query which uses a form as criteria. Basically what I want to do is
allow an user (via the form) to choose what he/she wants to search for. For
example, I'd like to allow the user to define whether to use "greater than",
"less than" or "equal" and to choose which amount. Example: select all
companies that have assets greater than 100 (>100). I have no knowledge of
VBA so I've trying to create that by using two fields in a form (one field
showing the operator - >, < =; and the second showing the values - 100, 200,
etc), both combo boxes which I tried to concatenate as a criteria in a query
(Forms![frm01_Main]![cmb_Filed1] & Forms![frm01_Main]![cmb_Filed2])

I'd be happy with a free text field as well, where the user can type ">100".
The most important thing is to have it in a form that will allow users to
determine what they want to search for.

 

[tina]

it's simple enough to create the SQL string in VBA, but then we'll have to
do something with it. where and how are you using the query now: are you
opening the query directly? or opening a form or report based on the query?
or...?

hth

Hi Tina,

thanks for your reply. Well, the SQL statement of my query is this:

SELECT tbl_MIS_Data.[Total assets]
FROM tbl_MIS_Data
GROUP BY tbl_MIS_Data.[Total assets]
HAVING (((tbl_MIS_Data.[Total assets])>100));

I'd like to have the "HAVING" statement being read from a form where I could
let the user choose two vairables at a time: the operator (<,>,=) and the
value (100, 200, etc) that would work together to create one single
critereon. This way, the user would be able to choose coys which have assets
above, below or even equal to a value that they would pick. Does it make
sense? The statement that I copied above works pefectly, however it does not
allow the user to define a different value outside the query, through a form.

when you concatenate the form references in a query, the expression returns
a *string*, as

">100"

this will give you a WHERE clause in the SQL statement that is essentially

NumberFieldName = ">100"

so you get no return values, and may get a Type Mismatch error, depending on
the field's data type.

i can't think of any way to do this without using VBA to write the SQL
statement. if you'll post the SQL statement of your query, and explain
where/how you're using the query, maybe we can help you tackle the VBA
issue.

hth

Hi guys,
I know it sounds simple, but I cannot get it to work. I was willing to
create a query which uses a form as criteria. Basically what I want to

do
is

allow an user (via the form) to choose what he/she wants to search

for.
For

example, I'd like to allow the user to define whether to use "greater than",
"less than" or "equal" and to choose which amount. Example: select all
companies that have assets greater than 100 (>100). I have no knowledge of
VBA so I've trying to create that by using two fields in a form (one field
showing the operator - >, < =; and the second showing the values -

100,
200,

etc), both combo boxes which I tried to concatenate as a criteria in a query
(Forms![frm01_Main]![cmb_Filed1] & Forms![frm01_Main]![cmb_Filed2])

I'd be happy with a free text field as well, where the user can type ">100".
The most important thing is to have it in a form that will allow users to
determine what they want to search for.

 

[Dom]

Hi Tina,

sorry for the delay in coming back with more info. I'm using a form to input
criteria into a query and then opening it from that form. At the end, I'm
just running a query which uses criteria defined in a form. I've not created
a report to show the results of the query. The query itself is the output I'm
looking for.

Also, I'd need to have a way that when there is no criteria, the query
should display everything. Like using the "*" to show all: Like
iif(isnull(Forms![frm01_Main]![cmb_Filed1]),"*",Forms![frm01_Main]![cmb_Filed1])

it's simple enough to create the SQL string in VBA, but then we'll have to
do something with it. where and how are you using the query now: are you
opening the query directly? or opening a form or report based on the query?
or...?

hth

Hi Tina,

thanks for your reply. Well, the SQL statement of my query is this:

SELECT tbl_MIS_Data.[Total assets]
FROM tbl_MIS_Data
GROUP BY tbl_MIS_Data.[Total assets]
HAVING (((tbl_MIS_Data.[Total assets])>100));

I'd like to have the "HAVING" statement being read from a form where I could
let the user choose two vairables at a time: the operator (<,>,=) and the
value (100, 200, etc) that would work together to create one single
critereon. This way, the user would be able to choose coys which have assets
above, below or even equal to a value that they would pick. Does it make
sense? The statement that I copied above works pefectly, however it does not
allow the user to define a different value outside the query, through a form.

when you concatenate the form references in a query, the expression returns
a *string*, as

">100"

this will give you a WHERE clause in the SQL statement that is essentially

NumberFieldName = ">100"

so you get no return values, and may get a Type Mismatch error, depending on
the field's data type.

i can't think of any way to do this without using VBA to write the SQL
statement. if you'll post the SQL statement of your query, and explain
where/how you're using the query, maybe we can help you tackle the VBA
issue.

hth


Hi guys,
I know it sounds simple, but I cannot get it to work. I was willing to
create a query which uses a form as criteria. Basically what I want to do
is
allow an user (via the form) to choose what he/she wants to search for.
For
example, I'd like to allow the user to define whether to use "greater
than",
"less than" or "equal" and to choose which amount. Example: select all
companies that have assets greater than 100 (>100). I have no knowledge of
VBA so I've trying to create that by using two fields in a form (one field
showing the operator - >, < =; and the second showing the values - 100,
200,
etc), both combo boxes which I tried to concatenate as a criteria in a
query
(Forms![frm01_Main]![cmb_Filed1] & Forms![frm01_Main]![cmb_Filed2])

I'd be happy with a free text field as well, where the user can type
">100".
The most important thing is to have it in a form that will allow users to
determine what they want to search for.

 

[tina]

okay, then, try the following code, as

Dim qry As QueryDef, str1 As String, str2 As String
Dim bln1 As Boolean, bln2 As Boolean

bln1 = IsNull(Me!cmb_Filed1)
bln2 = IsNull(Me!cmb_Filed2)

If Not (bln1 = bln2) Then
Msgbox "Enter criteria in both fields, or leave both blank."
Exit Sub
End If

Set qry = CurrentDb.QueryDefs("QueryName")
str1 = "SELECT tbl_MIS_Data.[Total assets] " _
& "FROM tbl_MIS_Data " _
& "GROUP BY tbl_MIS_Data.[Total assets] "

If bln1 Then
str2 = ""
Else
str2 = "HAVING tbl_MIS_Data.[Total assets] " _
& Me!cmb_Filed1 & " " & cmb_Filed2
End If
DoCmd.OpenQuery "QueryName"

replace QueryName with the correct name of the query, of course. from your
first post in this thread, cmb_Filed1 has the operator (< = >) and
cmb_Filed2 has the value (100, 200, etc). or you could, as you suggested,
use a single "free text" control for the user to enter.

to put the scenario together: put a command button on the form that has the
"criteria" controls, and put the above code in its' Click event procedure.
after the user enters criteria, s/he clicks the button and the code runs,
changing the SQL statement to the chosen criteria, and then opening the
query.

personally, i never, ever, ever allow users to open a table or query
directly (this is one of the very few times that i feel justified in using
the word "never"). once the user has such direct access to the table data,
you have no control over what happens next. <cringe> bad, bad habit to get
into. i strongly recommend that you create a form and bind your query to it,
then have the code open the form, instead of the query directly. as long as
the form is closed when the SQL statement is changed, it will work just
fine. and in a form, you have total control over what to allow the user to
do with the data.

hth

Hi Tina,

sorry for the delay in coming back with more info. I'm using a form to input
criteria into a query and then opening it from that form. At the end, I'm
just running a query which uses criteria defined in a form. I've not created
a report to show the results of the query. The query itself is the output I'm
looking for.

Also, I'd need to have a way that when there is no criteria, the query
should display everything. Like using the "*" to show all: Like
iif(isnull(Forms![frm01_Main]![cmb_Filed1]),"*",Forms![frm01_Main]![cmb_File
d1])

it's simple enough to create the SQL string in VBA, but then we'll have to
do something with it. where and how are you using the query now: are you
opening the query directly? or opening a form or report based on the query?
or...?

hth

Hi Tina,

thanks for your reply. Well, the SQL statement of my query is this:

SELECT tbl_MIS_Data.[Total assets]
FROM tbl_MIS_Data
GROUP BY tbl_MIS_Data.[Total assets]
HAVING (((tbl_MIS_Data.[Total assets])>100));

I'd like to have the "HAVING" statement being read from a form where I could
let the user choose two vairables at a time: the operator (<,>,=) and the
value (100, 200, etc) that would work together to create one single
critereon. This way, the user would be able to choose coys which have assets
above, below or even equal to a value that they would pick. Does it make
sense? The statement that I copied above works pefectly, however it

does
not

allow the user to define a different value outside the query, through

a
form.

:

when you concatenate the form references in a query, the expression returns
a *string*, as

">100"

this will give you a WHERE clause in the SQL statement that is essentially

NumberFieldName = ">100"

so you get no return values, and may get a Type Mismatch error, depending on
the field's data type.

i can't think of any way to do this without using VBA to write the SQL
statement. if you'll post the SQL statement of your query, and explain
where/how you're using the query, maybe we can help you tackle the VBA
issue.

hth


Hi guys,
I know it sounds simple, but I cannot get it to work. I was willing to
create a query which uses a form as criteria. Basically what I

want to
do

is
allow an user (via the form) to choose what he/she wants to search for.
For
example, I'd like to allow the user to define whether to use "greater
than",
"less than" or "equal" and to choose which amount. Example: select all
companies that have assets greater than 100 (>100). I have no knowledge of
VBA so I've trying to create that by using two fields in a form

(one
field

showing the operator - >, < =; and the second showing the values - 100,
200,
etc), both combo boxes which I tried to concatenate as a criteria in a
query
(Forms![frm01_Main]![cmb_Filed1] & Forms![frm01_Main]![cmb_Filed2])

I'd be happy with a free text field as well, where the user can type
">100".
The most important thing is to have it in a form that will allow

users
to

determine what they want to search for.

 

[Dom]

Thanks so much, Tina. You're a champ!!

And you're 100% right about the users having direct access to the query. But
at the end the users will be myself and another colleague only. The info we
extract will go to other people, of course.

Dom

okay, then, try the following code, as

Dim qry As QueryDef, str1 As String, str2 As String
Dim bln1 As Boolean, bln2 As Boolean

bln1 = IsNull(Me!cmb_Filed1)
bln2 = IsNull(Me!cmb_Filed2)

If Not (bln1 = bln2) Then
Msgbox "Enter criteria in both fields, or leave both blank."
Exit Sub
End If

Set qry = CurrentDb.QueryDefs("QueryName")
str1 = "SELECT tbl_MIS_Data.[Total assets] " _
& "FROM tbl_MIS_Data " _
& "GROUP BY tbl_MIS_Data.[Total assets] "

If bln1 Then
str2 = ""
Else
str2 = "HAVING tbl_MIS_Data.[Total assets] " _
& Me!cmb_Filed1 & " " & cmb_Filed2
End If
DoCmd.OpenQuery "QueryName"

replace QueryName with the correct name of the query, of course. from your
first post in this thread, cmb_Filed1 has the operator (< = >) and
cmb_Filed2 has the value (100, 200, etc). or you could, as you suggested,
use a single "free text" control for the user to enter.

to put the scenario together: put a command button on the form that has the
"criteria" controls, and put the above code in its' Click event procedure.
after the user enters criteria, s/he clicks the button and the code runs,
changing the SQL statement to the chosen criteria, and then opening the
query.

personally, i never, ever, ever allow users to open a table or query
directly (this is one of the very few times that i feel justified in using
the word "never"). once the user has such direct access to the table data,
you have no control over what happens next. <cringe> bad, bad habit to get
into. i strongly recommend that you create a form and bind your query to it,
then have the code open the form, instead of the query directly. as long as
the form is closed when the SQL statement is changed, it will work just
fine. and in a form, you have total control over what to allow the user to
do with the data.

hth

Hi Tina,

sorry for the delay in coming back with more info. I'm using a form to input
criteria into a query and then opening it from that form. At the end, I'm
just running a query which uses criteria defined in a form. I've not created
a report to show the results of the query. The query itself is the output I'm
looking for.

Also, I'd need to have a way that when there is no criteria, the query
should display everything. Like using the "*" to show all: Like
iif(isnull(Forms![frm01_Main]![cmb_Filed1]),"*",Forms![frm01_Main]![cmb_File
d1])

it's simple enough to create the SQL string in VBA, but then we'll have to
do something with it. where and how are you using the query now: are you
opening the query directly? or opening a form or report based on the query?
or...?

hth


Hi Tina,

thanks for your reply. Well, the SQL statement of my query is this:

SELECT tbl_MIS_Data.[Total assets]
FROM tbl_MIS_Data
GROUP BY tbl_MIS_Data.[Total assets]
HAVING (((tbl_MIS_Data.[Total assets])>100));

I'd like to have the "HAVING" statement being read from a form where I
could
let the user choose two vairables at a time: the operator (<,>,=) and the
value (100, 200, etc) that would work together to create one single
critereon. This way, the user would be able to choose coys which have
assets
above, below or even equal to a value that they would pick. Does it make
sense? The statement that I copied above works pefectly, however it does
not
allow the user to define a different value outside the query, through a
form.

:

when you concatenate the form references in a query, the expression
returns
a *string*, as

">100"

this will give you a WHERE clause in the SQL statement that is
essentially

NumberFieldName = ">100"

so you get no return values, and may get a Type Mismatch error,
depending on
the field's data type.

i can't think of any way to do this without using VBA to write the SQL
statement. if you'll post the SQL statement of your query, and explain
where/how you're using the query, maybe we can help you tackle the VBA
issue.

hth


Hi guys,
I know it sounds simple, but I cannot get it to work. I was willing to
create a query which uses a form as criteria. Basically what I want to
do
is
allow an user (via the form) to choose what he/she wants to search
for.
For
example, I'd like to allow the user to define whether to use "greater
than",
"less than" or "equal" and to choose which amount. Example: select all
companies that have assets greater than 100 (>100). I have no
knowledge of
VBA so I've trying to create that by using two fields in a form (one
field
showing the operator - >, < =; and the second showing the values -
100,
200,
etc), both combo boxes which I tried to concatenate as a criteria in a
query
(Forms![frm01_Main]![cmb_Filed1] & Forms![frm01_Main]![cmb_Filed2])

I'd be happy with a free text field as well, where the user can type
">100".
The most important thing is to have it in a form that will allow users
to
determine what they want to search for.

 

[tina]

well, you're welcome, Dom, but not quite so much a champ. i didn't finish
the code, so that is would actually update the SQL statement, duh - so
sorry! here's the completed code, that should work as i intended it to:

Dim qry As QueryDef, str1 As String, str2 As String
Dim bln1 As Boolean, bln2 As Boolean

bln1 = IsNull(Me!cmb_Filed1)
bln2 = IsNull(Me!cmb_Filed2)

If Not (bln1 = bln2) Then
Msgbox "Enter criteria in both fields, or leave both blank."
Exit Sub
End If

Set qry = CurrentDb.QueryDefs("QueryName")
str1 = "SELECT tbl_MIS_Data.[Total assets] " _
& "FROM tbl_MIS_Data " _
& "GROUP BY tbl_MIS_Data.[Total assets] "

If bln1 Then
str2 = ""
Else
str2 = "HAVING tbl_MIS_Data.[Total assets] " _
& Me!cmb_Filed1 & " " & cmb_Filed2
End If
qry.SQL = str1 & str2
DoCmd.OpenQuery "QueryName"

hth

Thanks so much, Tina. You're a champ!!

And you're 100% right about the users having direct access to the query. But
at the end the users will be myself and another colleague only. The info we
extract will go to other people, of course.

Dom

okay, then, try the following code, as

Dim qry As QueryDef, str1 As String, str2 As String
Dim bln1 As Boolean, bln2 As Boolean

bln1 = IsNull(Me!cmb_Filed1)
bln2 = IsNull(Me!cmb_Filed2)

If Not (bln1 = bln2) Then
Msgbox "Enter criteria in both fields, or leave both blank."
Exit Sub
End If

Set qry = CurrentDb.QueryDefs("QueryName")
str1 = "SELECT tbl_MIS_Data.[Total assets] " _
& "FROM tbl_MIS_Data " _
& "GROUP BY tbl_MIS_Data.[Total assets] "

If bln1 Then
str2 = ""
Else
str2 = "HAVING tbl_MIS_Data.[Total assets] " _
& Me!cmb_Filed1 & " " & cmb_Filed2
End If
DoCmd.OpenQuery "QueryName"

replace QueryName with the correct name of the query, of course. from your
first post in this thread, cmb_Filed1 has the operator (< = >) and
cmb_Filed2 has the value (100, 200, etc). or you could, as you suggested,
use a single "free text" control for the user to enter.

to put the scenario together: put a command button on the form that has the
"criteria" controls, and put the above code in its' Click event procedure.
after the user enters criteria, s/he clicks the button and the code runs,
changing the SQL statement to the chosen criteria, and then opening the
query.

personally, i never, ever, ever allow users to open a table or query
directly (this is one of the very few times that i feel justified in using
the word "never"). once the user has such direct access to the table data,
you have no control over what happens next. <cringe> bad, bad habit to get
into. i strongly recommend that you create a form and bind your query to it,
then have the code open the form, instead of the query directly. as long as
the form is closed when the SQL statement is changed, it will work just
fine. and in a form, you have total control over what to allow the user to
do with the data.

hth

Hi Tina,

sorry for the delay in coming back with more info. I'm using a form to input
criteria into a query and then opening it from that form. At the end, I'm
just running a query which uses criteria defined in a form. I've not created
a report to show the results of the query. The query itself is the

output
I'm

looking for.

Also, I'd need to have a way that when there is no criteria, the query
should display everything. Like using the "*" to show all: Like

iif(isnull(Forms![frm01_Main]![cmb_Filed1]),"*",Forms![frm01_Main]![cmb_File
d1])

:

it's simple enough to create the SQL string in VBA, but then we'll

have
to

do something with it. where and how are you using the query now: are you
opening the query directly? or opening a form or report based on the query?
or...?

hth


Hi Tina,

thanks for your reply. Well, the SQL statement of my query is this:

SELECT tbl_MIS_Data.[Total assets]
FROM tbl_MIS_Data
GROUP BY tbl_MIS_Data.[Total assets]
HAVING (((tbl_MIS_Data.[Total assets])>100));

I'd like to have the "HAVING" statement being read from a form where I
could
let the user choose two vairables at a time: the operator (<,>,=)

and
the

value (100, 200, etc) that would work together to create one single
critereon. This way, the user would be able to choose coys which have
assets
above, below or even equal to a value that they would pick. Does

it
make

sense? The statement that I copied above works pefectly, however

it
does

not
allow the user to define a different value outside the query,

through
a

form.

:

when you concatenate the form references in a query, the expression
returns
a *string*, as

">100"

this will give you a WHERE clause in the SQL statement that is
essentially

NumberFieldName = ">100"

so you get no return values, and may get a Type Mismatch error,
depending on
the field's data type.

i can't think of any way to do this without using VBA to write

the
SQL

statement. if you'll post the SQL statement of your query, and explain
where/how you're using the query, maybe we can help you tackle

the
VBA

issue.

hth


Hi guys,
I know it sounds simple, but I cannot get it to work. I was willing to
create a query which uses a form as criteria. Basically what I want to
do
is
allow an user (via the form) to choose what he/she wants to search
for.
For
example, I'd like to allow the user to define whether to use "greater
than",
"less than" or "equal" and to choose which amount. Example:

select
all

companies that have assets greater than 100 (>100). I have no
knowledge of
VBA so I've trying to create that by using two fields in a

form
(one

field
showing the operator - >, < =; and the second showing the values -
100,
200,
etc), both combo boxes which I tried to concatenate as a

criteria
in a

query
(Forms![frm01_Main]![cmb_Filed1] & Forms![frm01_Main]![cmb_Filed2])

I'd be happy with a free text field as well, where the user

can
type

">100".
The most important thing is to have it in a form that will

allow
users

to
determine what they want to search for.

 

[Dom]

Hi Tina,

I was on leave for a while and ended up not coming back to you sooner. Using
your help, I've prepared the code below which works fine. However, I've
trying to include an ORDER BY in descending order statement but it didn't
work. When I insert the statement without filling in the combo boxes, the
order by works. However, as soon as I fill in the combo boxes, the order by
statement does not work anymore. Can you please help me with that?

The statement I tried to include was:

& "ORDER BY tbl_MIS_Data.[Total assets] DESC"



Private Sub cmdAsset_Click()
Dim qry As QueryDef, str1 As String, str2 As String
Dim bln1 As Boolean, bln2 As Boolean

bln1 = IsNull(Me!cmb_MathStatem)
bln2 = IsNull(Me!cmb_DollarAmount)

If Not (bln1 = bln2) Then
MsgBox "Enter criteria in both fields, or leave both blank."
Exit Sub
End If

Set qry = CurrentDb.QueryDefs("qry00_Asset_Liab_Adj_Ratios_2")
str1 = "SELECT tbl_MIS_Data.[ARSN], tbl_MIS_Data.[Scheme
name],Sum(tbl_MIS_Data.[Total assets])AS [Sum of Total Assets],
Sum(tbl_MIS_Data.[Total liabilities]) AS [Sum of Total Liabilities]" _
& "FROM tbl_MIS_Data " _
& "GROUP BY tbl_MIS_Data.[ARSN], tbl_MIS_Data.[Scheme name],
tbl_MIS_Data.[Total assets], tbl_MIS_Data.[Total liabilities]"



If bln1 Then
str2 = ""
Else
str2 = "HAVING SUM(tbl_MIS_Data.[Total assets])" _
& Me!cmb_MathStatem & " " & cmb_DollarAmount



End If
qry.SQL = str1 & str2
DoCmd.OpenQuery "qry00_Asset_Liab_Adj_Ratios_2"


End Sub

well, you're welcome, Dom, but not quite so much a champ. i didn't finish
the code, so that is would actually update the SQL statement, duh - so
sorry! here's the completed code, that should work as i intended it to:

Dim qry As QueryDef, str1 As String, str2 As String
Dim bln1 As Boolean, bln2 As Boolean

bln1 = IsNull(Me!cmb_Filed1)
bln2 = IsNull(Me!cmb_Filed2)

If Not (bln1 = bln2) Then
Msgbox "Enter criteria in both fields, or leave both blank."
Exit Sub
End If

Set qry = CurrentDb.QueryDefs("QueryName")
str1 = "SELECT tbl_MIS_Data.[Total assets] " _
& "FROM tbl_MIS_Data " _
& "GROUP BY tbl_MIS_Data.[Total assets] "

If bln1 Then
str2 = ""
Else
str2 = "HAVING tbl_MIS_Data.[Total assets] " _
& Me!cmb_Filed1 & " " & cmb_Filed2
End If
qry.SQL = str1 & str2
DoCmd.OpenQuery "QueryName"

hth

Thanks so much, Tina. You're a champ!!

And you're 100% right about the users having direct access to the query. But
at the end the users will be myself and another colleague only. The info we
extract will go to other people, of course.

Dom

okay, then, try the following code, as

Dim qry As QueryDef, str1 As String, str2 As String
Dim bln1 As Boolean, bln2 As Boolean

bln1 = IsNull(Me!cmb_Filed1)
bln2 = IsNull(Me!cmb_Filed2)

If Not (bln1 = bln2) Then
Msgbox "Enter criteria in both fields, or leave both blank."
Exit Sub
End If

Set qry = CurrentDb.QueryDefs("QueryName")
str1 = "SELECT tbl_MIS_Data.[Total assets] " _
& "FROM tbl_MIS_Data " _
& "GROUP BY tbl_MIS_Data.[Total assets] "

If bln1 Then
str2 = ""
Else
str2 = "HAVING tbl_MIS_Data.[Total assets] " _
& Me!cmb_Filed1 & " " & cmb_Filed2
End If
DoCmd.OpenQuery "QueryName"

replace QueryName with the correct name of the query, of course. from your
first post in this thread, cmb_Filed1 has the operator (< = >) and
cmb_Filed2 has the value (100, 200, etc). or you could, as you suggested,
use a single "free text" control for the user to enter.

to put the scenario together: put a command button on the form that has the
"criteria" controls, and put the above code in its' Click event procedure.
after the user enters criteria, s/he clicks the button and the code runs,
changing the SQL statement to the chosen criteria, and then opening the
query.

personally, i never, ever, ever allow users to open a table or query
directly (this is one of the very few times that i feel justified in using
the word "never"). once the user has such direct access to the table data,
you have no control over what happens next. <cringe> bad, bad habit to get
into. i strongly recommend that you create a form and bind your query to it,
then have the code open the form, instead of the query directly. as long as
the form is closed when the SQL statement is changed, it will work just
fine. and in a form, you have total control over what to allow the user to
do with the data.

hth


Hi Tina,

sorry for the delay in coming back with more info. I'm using a form to
input
criteria into a query and then opening it from that form. At the end, I'm
just running a query which uses criteria defined in a form. I've not
created
a report to show the results of the query. The query itself is the output
I'm
looking for.

Also, I'd need to have a way that when there is no criteria, the query
should display everything. Like using the "*" to show all: Like

iif(isnull(Forms![frm01_Main]![cmb_Filed1]),"*",Forms![frm01_Main]![cmb_File
d1])


:

it's simple enough to create the SQL string in VBA, but then we'll have
to
do something with it. where and how are you using the query now: are you
opening the query directly? or opening a form or report based on the
query?
or...?

hth


Hi Tina,

thanks for your reply. Well, the SQL statement of my query is this:

SELECT tbl_MIS_Data.[Total assets]
FROM tbl_MIS_Data
GROUP BY tbl_MIS_Data.[Total assets]
HAVING (((tbl_MIS_Data.[Total assets])>100));

I'd like to have the "HAVING" statement being read from a form where I
could
let the user choose two vairables at a time: the operator (<,>,=) and
the
value (100, 200, etc) that would work together to create one single
critereon. This way, the user would be able to choose coys which have
assets
above, below or even equal to a value that they would pick. Does it
make
sense? The statement that I copied above works pefectly, however it
does
not
allow the user to define a different value outside the query, through
a
form.

:

when you concatenate the form references in a query, the expression
returns
a *string*, as

">100"

this will give you a WHERE clause in the SQL statement that is
essentially

NumberFieldName = ">100"

so you get no return values, and may get a Type Mismatch error,
depending on
the field's data type.

i can't think of any way to do this without using VBA to write the
SQL
statement. if you'll post the SQL statement of your query, and
explain
where/how you're using the query, maybe we can help you tackle the
VBA
issue.

hth


Hi guys,
I know it sounds simple, but I cannot get it to work. I was
willing to
create a query which uses a form as criteria. Basically what I
want to
do
is
allow an user (via the form) to choose what he/she wants to search
for.
For
example, I'd like to allow the user to define whether to use
"greater
than",
"less than" or "equal" and to choose which amount. Example: select
all
companies that have assets greater than 100 (>100). I have no
knowledge of
VBA so I've trying to create that by using two fields in a form
(one
field
showing the operator - >, < =; and the second showing the values -
100,
200,
etc), both combo boxes which I tried to concatenate as a criteria
in a
query
(Forms![frm01_Main]![cmb_Filed1] &
Forms![frm01_Main]![cmb_Filed2])

I'd be happy with a free text field as well, where the user can
type
">100".
The most important thing is to have it in a form that will allow
users
to
determine what they want to search for.