How to search with a ranged criteria?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi again,

I know I must sound like a broken record but I still have one question

How can I search a field for a range of numbers. Now I know most of you
will want me to use queries but my boss wants it in VB so I have little
choice. Does anyone know a code that will work. preferably one that will
require the user to enter only one number, but I am ok with the user inputing
the range ie from # to #.

Thanks!
James
 
What do you want to return? If you only want one record, the DLookup
function might do it, but I doubt it. I don't think you are going to be able
to do this without a query. You don't have to construct a query, you can
code it in VBA as an SQL call.
 
James

Sounds like a parameterized query to me.

SELECT foo, bar
FROM FooBar
WHERE foo Between [Enter Min foo] And [Enter Max Foo]
ORDER BY foo, bar

If you can supply the DDL and some test data along with a good
specification, I will be happy write the code for you.


Ron W
 
Hi Klatuu,

DLookup wont work, i need it to return multiple records. an SQL statement
might work but I would like it to open a particular form I have created for
the table. If I remember correctly the other problem you had helped me with
would only open a table or query. If you can help me with this I would
apreciate it.

Thanks,
James
 
Ok, here is some data

The form name is tblPropellant Query, which is the same name as the query it
was made from.
The Field name to search is ITD,
and the data entry field names are MinDTI and MaxDTI,
and so there is no confusion the field names are supposed to be the flip of
ITD
if you need any other data just ask :)

Thanks,
James

Ron Weiner said:
James

Sounds like a parameterized query to me.

SELECT foo, bar
FROM FooBar
WHERE foo Between [Enter Min foo] And [Enter Max Foo]
ORDER BY foo, bar

If you can supply the DDL and some test data along with a good
specification, I will be happy write the code for you.


Ron W
James said:
Hi again,

I know I must sound like a broken record but I still have one question

How can I search a field for a range of numbers. Now I know most of you
will want me to use queries but my boss wants it in VB so I have little
choice. Does anyone know a code that will work. preferably one that will
require the user to enter only one number, but I am ok with the user inputing
the range ie from # to #.

Thanks!
James
Click to expand...
Click to expand...
 
James

You are kinda' light on the info provided so I have no choice but to make
some assumptions.

* I assume the form is a continuous form
* I assume the two text boxes are in the forms header and are not bound
to anything
* I assume there is a button named cmdFilter also in the forms header
* I assume the that ITD is some kind of a number

Then in the code behind the form in the click event of the cmdFilter button
add the following.

Private Sub cmdFilter_Click()
' Purpose Filter the records to include only the records
' whose ITD value is between MinDTI and MaxDTI
Me.filter = "ITD between " & Nz(MinDTI.Value, 0) _
& " and " & Nz(MaxDTI.Value, 0)
Me.FilterOn = True
End Sub

User enters some values in the min and max text boxes, pushes the button,
and the form filters OUT all of the records that are not between the min and
max values. If either text box is null then the Nz() function supplies a 0
to the filter. If the user enters a non numeric value it will generate an
error, so you need to insure the users only enter values that are
appropriate for the data you are manipulating before you apply the filter.

If any of my assumptions are not correct then this solution is a non
solution, sorry:-(

Watch for Newsreader wrap and good luck with your project.

Ron W

James said:
Ok, here is some data

The form name is tblPropellant Query, which is the same name as the query it
was made from.
The Field name to search is ITD,
and the data entry field names are MinDTI and MaxDTI,
and so there is no confusion the field names are supposed to be the flip of
ITD
if you need any other data just ask :)

Thanks,
James

Ron Weiner said:
James

Sounds like a parameterized query to me.

SELECT foo, bar
FROM FooBar
WHERE foo Between [Enter Min foo] And [Enter Max Foo]
ORDER BY foo, bar

If you can supply the DDL and some test data along with a good
specification, I will be happy write the code for you.


Ron W
James said:
Hi again,

I know I must sound like a broken record but I still have one question

How can I search a field for a range of numbers. Now I know most of you
will want me to use queries but my boss wants it in VB so I have little
choice. Does anyone know a code that will work. preferably one that will
require the user to enter only one number, but I am ok with the user inputing
the range ie from # to #.

Thanks!
James
Click to expand...
Click to expand...
Click to expand...
 
Ron,

sorry about being light on the info, I actualy have the text boxes in a
search form that I want to use to open the data form for the search criteria.
so would I change Me.Filter to the form I want to opens Name.Filter?
Also I am getting an object required runtime error for the first line of
code Me.Filter=...
any ideas?

If there is any information that would make this easier for you just tell me
where to find it and I will send it to you, I am still fairly new at this.

Thanks,
James

Ron Weiner said:
James

You are kinda' light on the info provided so I have no choice but to make
some assumptions.

* I assume the form is a continuous form
* I assume the two text boxes are in the forms header and are not bound
to anything
* I assume there is a button named cmdFilter also in the forms header
* I assume the that ITD is some kind of a number

Then in the code behind the form in the click event of the cmdFilter button
add the following.

Private Sub cmdFilter_Click()
' Purpose Filter the records to include only the records
' whose ITD value is between MinDTI and MaxDTI
Me.filter = "ITD between " & Nz(MinDTI.Value, 0) _
& " and " & Nz(MaxDTI.Value, 0)
Me.FilterOn = True
End Sub

User enters some values in the min and max text boxes, pushes the button,
and the form filters OUT all of the records that are not between the min and
max values. If either text box is null then the Nz() function supplies a 0
to the filter. If the user enters a non numeric value it will generate an
error, so you need to insure the users only enter values that are
appropriate for the data you are manipulating before you apply the filter.

If any of my assumptions are not correct then this solution is a non
solution, sorry:-(

Watch for Newsreader wrap and good luck with your project.

Ron W

James said:
Ok, here is some data

The form name is tblPropellant Query, which is the same name as the query it
was made from.
The Field name to search is ITD,
and the data entry field names are MinDTI and MaxDTI,
and so there is no confusion the field names are supposed to be the flip of
ITD
if you need any other data just ask :)

Thanks,
James

Ron Weiner said:
James

Sounds like a parameterized query to me.

SELECT foo, bar
FROM FooBar
WHERE foo Between [Enter Min foo] And [Enter Max Foo]
ORDER BY foo, bar

If you can supply the DDL and some test data along with a good
specification, I will be happy write the code for you.


Ron W
Hi again,

I know I must sound like a broken record but I still have one question

How can I search a field for a range of numbers. Now I know most of you
will want me to use queries but my boss wants it in VB so I have little
choice. Does anyone know a code that will work. preferably one that will
require the user to enter only one number, but I am ok with the user
inputing
the range ie from # to #.

Thanks!
James
Click to expand...
Click to expand...
Click to expand...
 
Then open the form with the Where Condition set same as the filter in my
previous post. Eg.

DoCmd.OpenForm "tblPropellant Query",,,"ITD between " & Nz(MinDTI.Value, 0)
& " and " & Nz(MaxDTI.Value, 0)

The above line should all be on one line. Watch for newsreader wrap

This time the assumptions here are:

* I assume the two text boxes are on the form that is opening
"tblPropellant Query" form
* I assume the name of the form you are opening is "tblPropellant Query"

I still think you should stop writing code for a day or two and start
looking at solutions written by others and reading books. In the end you
will be far more productive. Before you knew how to read it would have been
fruitless for your parents to want you the write the next great novel. Try
to master the basics first.

Also try to use names that are more appropriate for the kinds of objects you
are working with. "tblPropellant Query" could be the worst name I have seen
for a form. As long as you are gonna' go into study and learn mode, spend a
few minutes at http://www.xoc.net/standards/rvbanc.asp to get a solid
foundation on Naming Conventions.

Ron W


James said:
Ron,

sorry about being light on the info, I actualy have the text boxes in a
search form that I want to use to open the data form for the search criteria.
so would I change Me.Filter to the form I want to opens Name.Filter?
Also I am getting an object required runtime error for the first line of
code Me.Filter=...
any ideas?

If there is any information that would make this easier for you just tell me
where to find it and I will send it to you, I am still fairly new at this.

Thanks,
James

Ron Weiner said:
James

You are kinda' light on the info provided so I have no choice but to make
some assumptions.

* I assume the form is a continuous form
* I assume the two text boxes are in the forms header and are not bound
to anything
* I assume there is a button named cmdFilter also in the forms header
* I assume the that ITD is some kind of a number

Then in the code behind the form in the click event of the cmdFilter button
add the following.

Private Sub cmdFilter_Click()
' Purpose Filter the records to include only the records
' whose ITD value is between MinDTI and MaxDTI
Me.filter = "ITD between " & Nz(MinDTI.Value, 0) _
& " and " & Nz(MaxDTI.Value, 0)
Me.FilterOn = True
End Sub

User enters some values in the min and max text boxes, pushes the button,
and the form filters OUT all of the records that are not between the min and
max values. If either text box is null then the Nz() function supplies a 0
to the filter. If the user enters a non numeric value it will generate an
error, so you need to insure the users only enter values that are
appropriate for the data you are manipulating before you apply the filter.

If any of my assumptions are not correct then this solution is a non
solution, sorry:-(

Watch for Newsreader wrap and good luck with your project.

Ron W

James said:
Ok, here is some data

The form name is tblPropellant Query, which is the same name as the
Click to expand...
query
it
was made from.
The Field name to search is ITD,
and the data entry field names are MinDTI and MaxDTI,
and so there is no confusion the field names are supposed to be the
Click to expand...
flip
of
ITD
if you need any other data just ask :)

Thanks,
James

:

James

Sounds like a parameterized query to me.

SELECT foo, bar
FROM FooBar
WHERE foo Between [Enter Min foo] And [Enter Max Foo]
ORDER BY foo, bar

If you can supply the DDL and some test data along with a good
specification, I will be happy write the code for you.


Ron W
Hi again,

I know I must sound like a broken record but I still have one question

How can I search a field for a range of numbers. Now I know most
Click to expand...
of
you
will want me to use queries but my boss wants it in VB so I have little
choice. Does anyone know a code that will work. preferably one
Click to expand...
that
will
require the user to enter only one number, but I am ok with the user
inputing
the range ie from # to #.

Thanks!
James
Click to expand...
Click to expand...
Click to expand...
 
Ron,
when I begin the search it asks for ITD in a user input box then no matter
what I enter it opens the form for all records. My guess is it has something
to do with either the quotes or the datatype, but I am unsure how to check
either of them.

Thanks,
James

Ron Weiner said:
Then open the form with the Where Condition set same as the filter in my
previous post. Eg.

DoCmd.OpenForm "tblPropellant Query",,,"ITD between " & Nz(MinDTI.Value, 0)
& " and " & Nz(MaxDTI.Value, 0)

The above line should all be on one line. Watch for newsreader wrap

This time the assumptions here are:

* I assume the two text boxes are on the form that is opening
"tblPropellant Query" form
* I assume the name of the form you are opening is "tblPropellant Query"

I still think you should stop writing code for a day or two and start
looking at solutions written by others and reading books. In the end you
will be far more productive. Before you knew how to read it would have been
fruitless for your parents to want you the write the next great novel. Try
to master the basics first.

Also try to use names that are more appropriate for the kinds of objects you
are working with. "tblPropellant Query" could be the worst name I have seen
for a form. As long as you are gonna' go into study and learn mode, spend a
few minutes at http://www.xoc.net/standards/rvbanc.asp to get a solid
foundation on Naming Conventions.

Ron W


James said:
Ron,

sorry about being light on the info, I actualy have the text boxes in a
search form that I want to use to open the data form for the search criteria.
so would I change Me.Filter to the form I want to opens Name.Filter?
Also I am getting an object required runtime error for the first line of
code Me.Filter=...
any ideas?

If there is any information that would make this easier for you just tell me
where to find it and I will send it to you, I am still fairly new at this.

Thanks,
James

Ron Weiner said:
James

You are kinda' light on the info provided so I have no choice but to make
some assumptions.

* I assume the form is a continuous form
* I assume the two text boxes are in the forms header and are not bound
to anything
* I assume there is a button named cmdFilter also in the forms header
* I assume the that ITD is some kind of a number

Then in the code behind the form in the click event of the cmdFilter button
add the following.

Private Sub cmdFilter_Click()
' Purpose Filter the records to include only the records
' whose ITD value is between MinDTI and MaxDTI
Me.filter = "ITD between " & Nz(MinDTI.Value, 0) _
& " and " & Nz(MaxDTI.Value, 0)
Me.FilterOn = True
End Sub

User enters some values in the min and max text boxes, pushes the button,
and the form filters OUT all of the records that are not between the min and
max values. If either text box is null then the Nz() function supplies a 0
to the filter. If the user enters a non numeric value it will generate an
error, so you need to insure the users only enter values that are
appropriate for the data you are manipulating before you apply the filter.

If any of my assumptions are not correct then this solution is a non
solution, sorry:-(

Watch for Newsreader wrap and good luck with your project.

Ron W

Ok, here is some data

The form name is tblPropellant Query, which is the same name as the query
it
was made from.
The Field name to search is ITD,
and the data entry field names are MinDTI and MaxDTI,
and so there is no confusion the field names are supposed to be the flip
of
ITD
if you need any other data just ask :)

Thanks,
James

:

James

Sounds like a parameterized query to me.

SELECT foo, bar
FROM FooBar
WHERE foo Between [Enter Min foo] And [Enter Max Foo]
ORDER BY foo, bar

If you can supply the DDL and some test data along with a good
specification, I will be happy write the code for you.


Ron W
Hi again,

I know I must sound like a broken record but I still have one question

How can I search a field for a range of numbers. Now I know most of
you
will want me to use queries but my boss wants it in VB so I have
little
choice. Does anyone know a code that will work. preferably one that
will
require the user to enter only one number, but I am ok with the user
inputing
the range ie from # to #.

Thanks!
James
Click to expand...
Click to expand...
Click to expand...
 
James

Please send the Sql statement that is the Query that is the record source of
the form "tblPropellant Query"

Ron W
James said:
Ron,
when I begin the search it asks for ITD in a user input box then no matter
what I enter it opens the form for all records. My guess is it has something
to do with either the quotes or the datatype, but I am unsure how to check
either of them.

Thanks,
James

Ron Weiner said:
Then open the form with the Where Condition set same as the filter in my
previous post. Eg.

DoCmd.OpenForm "tblPropellant Query",,,"ITD between " & Nz(MinDTI.Value, 0)
& " and " & Nz(MaxDTI.Value, 0)

The above line should all be on one line. Watch for newsreader wrap

This time the assumptions here are:

* I assume the two text boxes are on the form that is opening
"tblPropellant Query" form
* I assume the name of the form you are opening is "tblPropellant Query"

I still think you should stop writing code for a day or two and start
looking at solutions written by others and reading books. In the end you
will be far more productive. Before you knew how to read it would have been
fruitless for your parents to want you the write the next great novel. Try
to master the basics first.

Also try to use names that are more appropriate for the kinds of objects you
are working with. "tblPropellant Query" could be the worst name I have seen
for a form. As long as you are gonna' go into study and learn mode, spend a
few minutes at http://www.xoc.net/standards/rvbanc.asp to get a solid
foundation on Naming Conventions.

Ron W


James said:
Ron,

sorry about being light on the info, I actualy have the text boxes in a
search form that I want to use to open the data form for the search criteria.
so would I change Me.Filter to the form I want to opens Name.Filter?
Also I am getting an object required runtime error for the first line of
code Me.Filter=...
any ideas?

If there is any information that would make this easier for you just
Click to expand...
tell
me
where to find it and I will send it to you, I am still fairly new at this.

Thanks,
James

:

James

You are kinda' light on the info provided so I have no choice but to make
some assumptions.

* I assume the form is a continuous form
* I assume the two text boxes are in the forms header and are
Click to expand...
not
bound
to anything
* I assume there is a button named cmdFilter also in the forms header
* I assume the that ITD is some kind of a number

Then in the code behind the form in the click event of the cmdFilter button
add the following.

Private Sub cmdFilter_Click()
' Purpose Filter the records to include only the records
' whose ITD value is between MinDTI and MaxDTI
Me.filter = "ITD between " & Nz(MinDTI.Value, 0) _
& " and " & Nz(MaxDTI.Value, 0)
Me.FilterOn = True
End Sub

User enters some values in the min and max text boxes, pushes the button,
and the form filters OUT all of the records that are not between the
Click to expand...
min
and
max values. If either text box is null then the Nz() function
Click to expand...
supplies
a 0
to the filter. If the user enters a non numeric value it will
Click to expand...
generate
an
error, so you need to insure the users only enter values that are
appropriate for the data you are manipulating before you apply the filter.

If any of my assumptions are not correct then this solution is a non
solution, sorry:-(

Watch for Newsreader wrap and good luck with your project.

Ron W

Ok, here is some data

The form name is tblPropellant Query, which is the same name as
Click to expand...
the
query
it
was made from.
The Field name to search is ITD,
and the data entry field names are MinDTI and MaxDTI,
and so there is no confusion the field names are supposed to be
Click to expand...
the
flip
of
ITD
if you need any other data just ask :)

Thanks,
James

:

James

Sounds like a parameterized query to me.

SELECT foo, bar
FROM FooBar
WHERE foo Between [Enter Min foo] And [Enter Max Foo]
ORDER BY foo, bar

If you can supply the DDL and some test data along with a good
specification, I will be happy write the code for you.


Ron W
Hi again,

I know I must sound like a broken record but I still have one question

How can I search a field for a range of numbers. Now I know
Click to expand...
most
of
you
will want me to use queries but my boss wants it in VB so I have
little
choice. Does anyone know a code that will work. preferably
Click to expand...
one
that
will
require the user to enter only one number, but I am ok with
Click to expand...
the
user
inputing
the range ie from # to #.

Thanks!
James
Click to expand...
Click to expand...
Click to expand...
 
Ron,

I found the mistake, I reversed the names of the field to be searched and
the criteria fields, ITD & DTI, I'm sorry about that, it looks like it works
now.

Thank you very much,
James


Ron Weiner said:
James

Please send the Sql statement that is the Query that is the record source of
the form "tblPropellant Query"

Ron W
James said:
Ron,
when I begin the search it asks for ITD in a user input box then no matter
what I enter it opens the form for all records. My guess is it has something
to do with either the quotes or the datatype, but I am unsure how to check
either of them.

Thanks,
James

Ron Weiner said:
Then open the form with the Where Condition set same as the filter in my
previous post. Eg.

DoCmd.OpenForm "tblPropellant Query",,,"ITD between " & Nz(MinDTI.Value, 0)
& " and " & Nz(MaxDTI.Value, 0)

The above line should all be on one line. Watch for newsreader wrap

This time the assumptions here are:

* I assume the two text boxes are on the form that is opening
"tblPropellant Query" form
* I assume the name of the form you are opening is "tblPropellant Query"

I still think you should stop writing code for a day or two and start
looking at solutions written by others and reading books. In the end you
will be far more productive. Before you knew how to read it would have been
fruitless for your parents to want you the write the next great novel. Try
to master the basics first.

Also try to use names that are more appropriate for the kinds of objects you
are working with. "tblPropellant Query" could be the worst name I have seen
for a form. As long as you are gonna' go into study and learn mode, spend a
few minutes at http://www.xoc.net/standards/rvbanc.asp to get a solid
foundation on Naming Conventions.

Ron W


Ron,

sorry about being light on the info, I actualy have the text boxes in a
search form that I want to use to open the data form for the search
criteria.
so would I change Me.Filter to the form I want to opens Name.Filter?
Also I am getting an object required runtime error for the first line of
code Me.Filter=...
any ideas?

If there is any information that would make this easier for you just tell
me
where to find it and I will send it to you, I am still fairly new at this.

Thanks,
James

:

James

You are kinda' light on the info provided so I have no choice but to
make
some assumptions.

* I assume the form is a continuous form
* I assume the two text boxes are in the forms header and are not
bound
to anything
* I assume there is a button named cmdFilter also in the forms
header
* I assume the that ITD is some kind of a number

Then in the code behind the form in the click event of the cmdFilter
button
add the following.

Private Sub cmdFilter_Click()
' Purpose Filter the records to include only the records
' whose ITD value is between MinDTI and MaxDTI
Me.filter = "ITD between " & Nz(MinDTI.Value, 0) _
& " and " & Nz(MaxDTI.Value, 0)
Me.FilterOn = True
End Sub

User enters some values in the min and max text boxes, pushes the
button,
and the form filters OUT all of the records that are not between the min
and
max values. If either text box is null then the Nz() function supplies
a 0
to the filter. If the user enters a non numeric value it will generate
an
error, so you need to insure the users only enter values that are
appropriate for the data you are manipulating before you apply the
filter.

If any of my assumptions are not correct then this solution is a non
solution, sorry:-(

Watch for Newsreader wrap and good luck with your project.

Ron W

Ok, here is some data

The form name is tblPropellant Query, which is the same name as the
query
it
was made from.
The Field name to search is ITD,
and the data entry field names are MinDTI and MaxDTI,
and so there is no confusion the field names are supposed to be the
flip
of
ITD
if you need any other data just ask :)

Thanks,
James

:

James

Sounds like a parameterized query to me.

SELECT foo, bar
FROM FooBar
WHERE foo Between [Enter Min foo] And [Enter Max Foo]
ORDER BY foo, bar

If you can supply the DDL and some test data along with a good
specification, I will be happy write the code for you.


Ron W
Hi again,

I know I must sound like a broken record but I still have one
question

How can I search a field for a range of numbers. Now I know most
of
you
will want me to use queries but my boss wants it in VB so I have
little
choice. Does anyone know a code that will work. preferably one
that
will
require the user to enter only one number, but I am ok with the
user
inputing
the range ie from # to #.

Thanks!
James
Click to expand...
Click to expand...
Click to expand...
 
Back
Top