Queries

[TheCaptain]

Hello,

I am using a form that you input search criteria. You then hit a button and
it runs my query off of the information in the form. Problem is, when I run
the query it returns my results and results that have no relation to the
search data.
For ex.
If I search for sales between $5 and $10 the query returns all the
reccords between those two numbers. It also returns records that do not
have any sales information filled out. This is very frustrating. Here is a
sample of the SQL code I am using:

([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Between
[Forms]![Form1].[salesfrom] And [Forms]![Form1].[salesto] Is Null)

Table Name = Production
Form Name = Form1
Form Parameter names = "salesfrom" and "salesto"

Do I have to write in a statement like "if blank do not show record"? If
so, what?

Please help,

Thanks

 

[KARL DEWEY]

Just use --
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto])

But most folks wants to the records in the requested range and those that
have no entry.
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Is Null)

 

[TheCaptain]

The problem is though if I have multiple search parameters the query won't
run (using what you gave me).

Just use --
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto])

But most folks wants to the records in the requested range and those that
have no entry.
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Is Null)

--
KARL DEWEY
Build a little - Test a little

Hello,

I am using a form that you input search criteria. You then hit a button and
it runs my query off of the information in the form. Problem is, when I run
the query it returns my results and results that have no relation to the
search data.
For ex.
If I search for sales between $5 and $10 the query returns all the
reccords between those two numbers. It also returns records that do not
have any sales information filled out. This is very frustrating. Here is a
sample of the SQL code I am using:

([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Between
[Forms]![Form1].[salesfrom] And [Forms]![Form1].[salesto] Is Null)

Table Name = Production
Form Name = Form1
Form Parameter names = "salesfrom" and "salesto"

Do I have to write in a statement like "if blank do not show record"? If
so, what?

Please help,

Thanks

 

[KARL DEWEY]

I do not follow. What I gave was one search parameter. You have to either
OR or AND it with your others.
--
KARL DEWEY
Build a little - Test a little

The problem is though if I have multiple search parameters the query won't
run (using what you gave me).

Just use --
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto])

But most folks wants to the records in the requested range and those that
have no entry.
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Is Null)

--
KARL DEWEY
Build a little - Test a little

Hello,

I am using a form that you input search criteria. You then hit a button and
it runs my query off of the information in the form. Problem is, when I run
the query it returns my results and results that have no relation to the
search data.
For ex.
If I search for sales between $5 and $10 the query returns all the
reccords between those two numbers. It also returns records that do not
have any sales information filled out. This is very frustrating. Here is a
sample of the SQL code I am using:

([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Between
[Forms]![Form1].[salesfrom] And [Forms]![Form1].[salesto] Is Null)

Table Name = Production
Form Name = Form1
Form Parameter names = "salesfrom" and "salesto"

Do I have to write in a statement like "if blank do not show record"? If
so, what?

Please help,

Thanks

 

[TheCaptain]

I know, I just gave you one of my samples. Here is an example with two
parameters (how I have it currently):

([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Between
[Forms]![Form1].[salesfrom] And [Forms]![Form1].[salesto] Is Null)
AND
([Production.expenses] Between [Forms]![Form1].[expensefrom] And
[Forms]![Form1].[expenseto] OR [Production.expenses] Between
[Forms]![Form1].[expensefrom] And [Forms]![Form1].[expenseto] IS NULL);

Using the above (I know it's messy gives me my parameters. If I enter $5
to $10 for sales and $2 to $6 for expenses, the query gives my the range.
But the query also returns all of the records without sales or expenses
filled in. When I used what you gave me it, for some reason, did not work.

Thanks

I do not follow. What I gave was one search parameter. You have to either
OR or AND it with your others.
--
KARL DEWEY
Build a little - Test a little

The problem is though if I have multiple search parameters the query won't
run (using what you gave me).

Just use --
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto])

But most folks wants to the records in the requested range and those that
have no entry.
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Is Null)

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I am using a form that you input search criteria. You then hit a button and
it runs my query off of the information in the form. Problem is, when I run
the query it returns my results and results that have no relation to the
search data.
For ex.
If I search for sales between $5 and $10 the query returns all the
reccords between those two numbers. It also returns records that do not
have any sales information filled out. This is very frustrating. Here is a
sample of the SQL code I am using:

([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Between
[Forms]![Form1].[salesfrom] And [Forms]![Form1].[salesto] Is Null)

Table Name = Production
Form Name = Form1
Form Parameter names = "salesfrom" and "salesto"

Do I have to write in a statement like "if blank do not show record"? If
so, what?

Please help,

Thanks

 

[KARL DEWEY]

Try this --
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto]) AND ([Production.expenses] Between
[Forms]![Form1].[expensefrom] And [Forms]![Form1].[expenseto]);

--
KARL DEWEY
Build a little - Test a little

I know, I just gave you one of my samples. Here is an example with two
parameters (how I have it currently):

([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Between
[Forms]![Form1].[salesfrom] And [Forms]![Form1].[salesto] Is Null)
AND
([Production.expenses] Between [Forms]![Form1].[expensefrom] And
[Forms]![Form1].[expenseto] OR [Production.expenses] Between
[Forms]![Form1].[expensefrom] And [Forms]![Form1].[expenseto] IS NULL);

Using the above (I know it's messy gives me my parameters. If I enter $5
to $10 for sales and $2 to $6 for expenses, the query gives my the range.
But the query also returns all of the records without sales or expenses
filled in. When I used what you gave me it, for some reason, did not work.

Thanks

I do not follow. What I gave was one search parameter. You have to either
OR or AND it with your others.
--
KARL DEWEY
Build a little - Test a little

The problem is though if I have multiple search parameters the query won't
run (using what you gave me).

:

Just use --
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto])

But most folks wants to the records in the requested range and those that
have no entry.
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Is Null)

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I am using a form that you input search criteria. You then hit a button and
it runs my query off of the information in the form. Problem is, when I run
the query it returns my results and results that have no relation to the
search data.
For ex.
If I search for sales between $5 and $10 the query returns all the
reccords between those two numbers. It also returns records that do not
have any sales information filled out. This is very frustrating. Here is a
sample of the SQL code I am using:

([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Between
[Forms]![Form1].[salesfrom] And [Forms]![Form1].[salesto] Is Null)

Table Name = Production
Form Name = Form1
Form Parameter names = "salesfrom" and "salesto"

Do I have to write in a statement like "if blank do not show record"? If
so, what?

Please help,

Thanks

 

[KARL DEWEY]

How about this ---
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto]) OR ([Production.expenses] Between
[Forms]![Form1].[expensefrom] And [Forms]![Form1].[expenseto]);

--
KARL DEWEY
Build a little - Test a little

I tried this, but I run into a problem. I have to fill out both fields in my
search. If I don't it will not return any results. I want to be able to
only fill out either sales or expenses, or both. Any ideas?

Thanks

Try this --
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto]) AND ([Production.expenses] Between
[Forms]![Form1].[expensefrom] And [Forms]![Form1].[expenseto]);

--
KARL DEWEY
Build a little - Test a little

I know, I just gave you one of my samples. Here is an example with two
parameters (how I have it currently):

([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Between
[Forms]![Form1].[salesfrom] And [Forms]![Form1].[salesto] Is Null)
AND
([Production.expenses] Between [Forms]![Form1].[expensefrom] And
[Forms]![Form1].[expenseto] OR [Production.expenses] Between
[Forms]![Form1].[expensefrom] And [Forms]![Form1].[expenseto] IS NULL);

Using the above (I know it's messy gives me my parameters. If I enter $5
to $10 for sales and $2 to $6 for expenses, the query gives my the range.
But the query also returns all of the records without sales or expenses
filled in. When I used what you gave me it, for some reason, did not work.

Thanks

:

I do not follow. What I gave was one search parameter. You have to either
OR or AND it with your others.
--
KARL DEWEY
Build a little - Test a little


:

The problem is though if I have multiple search parameters the query won't
run (using what you gave me).

:

Just use --
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto])

But most folks wants to the records in the requested range and those that
have no entry.
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Is Null)

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I am using a form that you input search criteria. You then hit a button and
it runs my query off of the information in the form. Problem is, when I run
the query it returns my results and results that have no relation to the
search data.
For ex.
If I search for sales between $5 and $10 the query returns all the
reccords between those two numbers. It also returns records that do not
have any sales information filled out. This is very frustrating. Here is a
sample of the SQL code I am using:

([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto] OR [Production.sales] Between
[Forms]![Form1].[salesfrom] And [Forms]![Form1].[salesto] Is Null)

Table Name = Production
Form Name = Form1
Form Parameter names = "salesfrom" and "salesto"

Do I have to write in a statement like "if blank do not show record"? If
so, what?

Please help,

Thanks

 

[John Spencer]

You can't use the between ... and ... construct.

You need to use something along the line of

WHERE
(Production.Sales >= Forms!Form1!SalesFrom
Or Forms!Form1!SalesFrom is null)
AND
(Production.Sales <= Forms!Form1!Salesto
Or Forms!Form1!Salesto is null)
AND
(Production.Expenses >=Forms!Form1!ExpenseFrom
Or Forms!Form1!expenseFrom is Null)
AND
(Production.Expense <= Forms!Form1!ExpenseTo
Or Forms!Form1!ExpenseTo is Null)

Be warned that Access will turn this set of criteria into 16 lines (on
the query grid in design view) when you save the query.

If you have other criteria in the query, you may get even more lines or
criteria.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[TheCaptain]

Sorry I didn't get back to you, I went home for the day! So far so good with
your code. I am going to test it more, but it looks like it works.

Thanks

You can't use the between ... and ... construct.

You need to use something along the line of

WHERE
(Production.Sales >= Forms!Form1!SalesFrom
Or Forms!Form1!SalesFrom is null)
AND
(Production.Sales <= Forms!Form1!Salesto
Or Forms!Form1!Salesto is null)
AND
(Production.Expenses >=Forms!Form1!ExpenseFrom
Or Forms!Form1!expenseFrom is Null)
AND
(Production.Expense <= Forms!Form1!ExpenseTo
Or Forms!Form1!ExpenseTo is Null)

Be warned that Access will turn this set of criteria into 16 lines (on
the query grid in design view) when you save the query.

If you have other criteria in the query, you may get even more lines or
criteria.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

How about this ---
([Production.sales] Between [Forms]![Form1].[salesfrom] And
[Forms]![Form1].[salesto]) OR ([Production.expenses] Between
[Forms]![Form1].[expensefrom] And [Forms]![Form1].[expenseto]);