Parameter Query

G

Guest

Hi
I'm using the following parameter query with a form, it works for the first
few times and then doesn't bring back any records at all. To get it working
again I need to rewrite the whole query! Whats happening?? Please help!

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Mobile Number], Details.[Telephone Number],
Details.[Fax Number], Details.[Current Supplier], Details.[Current Supplier],
Details.Group, Details.[Group A/C Code], Details.Turnover, Details.[Sales
Rep], Details.[Sales Rep], Details.Asian, Details.[Price - Pint],
Details.[Price - Litre], Details.[Price - 2 Litre], Details.[Price - 2
Litre], Details.[Qty - Litre], Details.[Qty - Pint], Details.[Qty - 2
Litres], Details.Cream, Details.Bread, Details.Eggs, Details.Notes,
Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.city Like
IIf(Forms![search Form]!city Is Null,"*",Forms![search Form]!city) And
Details.county Like IIf(Forms![search Form]!county Is Null,"*",Forms![search
Form]!county) And Details.[current supplier] Like IIf(Forms![search
Form]![current supplier] Is Null,"*",Forms![search Form]![current supplier])
And Details.asian Like IIf(Forms![search Form]!asian Is
Null,"*",Forms![search Form]!asian) And Details.turnover>=Forms![search
Form]!turnovergreater And Details.turnover<=Forms![search Form]!turnoverless;
 
G

Guest

I am using an input form to enter the information for the query which then
returns a report.
 
J

Jeff Boyce

We're not there ... we can't see your form. We don't know how you/your form
"returns a report". Specific information helps us come up with specific
suggestions.

By the way, what happens if you use the query ONLY as a query, filling in
the parameters/criteria for each run? Does this also fail?

Is there a chance your form isn't clearing out "old" criteria, so after a
few uses, the query is still running correctly, but no rows match the
(multiple) criteria your form holds?

--
Regards

Jeff Boyce
<Office/Access MVP>

Martc said:
I am using an input form to enter the information for the query which then
returns a report.

Martc said:
Hi
I'm using the following parameter query with a form, it works for the first
few times and then doesn't bring back any records at all. To get it working
again I need to rewrite the whole query! Whats happening?? Please help!

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Mobile Number], Details.[Telephone Number],
Details.[Fax Number], Details.[Current Supplier], Details.[Current Supplier],
Details.Group, Details.[Group A/C Code], Details.Turnover, Details.[Sales
Rep], Details.[Sales Rep], Details.Asian, Details.[Price - Pint],
Details.[Price - Litre], Details.[Price - 2 Litre], Details.[Price - 2
Litre], Details.[Qty - Litre], Details.[Qty - Pint], Details.[Qty - 2
Litres], Details.Cream, Details.Bread, Details.Eggs, Details.Notes,
Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.city Like
IIf(Forms![search Form]!city Is Null,"*",Forms![search Form]!city) And
Details.county Like IIf(Forms![search Form]!county Is Null,"*",Forms![search
Form]!county) And Details.[current supplier] Like IIf(Forms![search
Form]![current supplier] Is Null,"*",Forms![search Form]![current supplier])
And Details.asian Like IIf(Forms![search Form]!asian Is
Null,"*",Forms![search Form]!asian) And Details.turnover>=Forms![search
Form]!turnovergreater And Details.turnover<=Forms![search Form]!turnoverless;
Click to expand...
Click to expand...
 
G

Guest

Its is exactly as though the form is not clearing out the old criteria. Would
this suggest a problem with the form and not the query? When I run the query
We're not there ... we can't see your form. We don't know how you/your form
"returns a report". Specific information helps us come up with specific
suggestions.

By the way, what happens if you use the query ONLY as a query, filling in
the parameters/criteria for each run? Does this also fail?

Is there a chance your form isn't clearing out "old" criteria, so after a
few uses, the query is still running correctly, but no rows match the
(multiple) criteria your form holds?

--
Regards

Jeff Boyce
<Office/Access MVP>

Martc said:
I am using an input form to enter the information for the query which then
returns a report.

Martc said:
Hi
I'm using the following parameter query with a form, it works for the first
few times and then doesn't bring back any records at all. To get it working
again I need to rewrite the whole query! Whats happening?? Please help!

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Mobile Number], Details.[Telephone Number],
Details.[Fax Number], Details.[Current Supplier], Details.[Current Supplier],
Details.Group, Details.[Group A/C Code], Details.Turnover, Details.[Sales
Rep], Details.[Sales Rep], Details.Asian, Details.[Price - Pint],
Details.[Price - Litre], Details.[Price - 2 Litre], Details.[Price - 2
Litre], Details.[Qty - Litre], Details.[Qty - Pint], Details.[Qty - 2
Litres], Details.Cream, Details.Bread, Details.Eggs, Details.Notes,
Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.city Like
IIf(Forms![search Form]!city Is Null,"*",Forms![search Form]!city) And
Details.county Like IIf(Forms![search Form]!county Is Null,"*",Forms![search
Form]!county) And Details.[current supplier] Like IIf(Forms![search
Form]![current supplier] Is Null,"*",Forms![search Form]![current supplier])
And Details.asian Like IIf(Forms![search Form]!asian Is
Null,"*",Forms![search Form]!asian) And Details.turnover>=Forms![search
Form]!turnovergreater And Details.turnover<=Forms![search Form]!turnoverless;
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

See in-line...

Martc said:
Its is exactly as though the form is not clearing out the old criteria. Would
this suggest a problem with the form and not the query? When I run the query
on its own it brings up exactly the same results.
Click to expand...

There's more than one way to interpret this ("exactly the same results").
Are you saying that running the query repeatedly, with differing criteria
works the first few times, then fails "exactly the same" as your form does?

Can you confirm that you have cleared ALL of the criteria between runs
(either the form or the query)?

Can you rebuild the query "from scratch" and have it work correctly?

--
Regards

Jeff Boyce
Jeff Boyce said:
We're not there ... we can't see your form. We don't know how you/your form
"returns a report". Specific information helps us come up with specific
suggestions.

By the way, what happens if you use the query ONLY as a query, filling in
the parameters/criteria for each run? Does this also fail?

Is there a chance your form isn't clearing out "old" criteria, so after a
few uses, the query is still running correctly, but no rows match the
(multiple) criteria your form holds?

--
Regards

Jeff Boyce
<Office/Access MVP>

Martc said:
I am using an input form to enter the information for the query which then
returns a report.

:

Hi
I'm using the following parameter query with a form, it works for
Click to expand...
the
first
few times and then doesn't bring back any records at all. To get it working
again I need to rewrite the whole query! Whats happening?? Please help!

SELECT Details.[Customer Name], Details.[Address 1],
Click to expand...
Details.[Address
2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Mobile Number], Details.[Telephone Number],
Details.[Fax Number], Details.[Current Supplier], Details.[Current Supplier],
Details.Group, Details.[Group A/C Code], Details.Turnover, Details.[Sales
Rep], Details.[Sales Rep], Details.Asian, Details.[Price - Pint],
Details.[Price - Litre], Details.[Price - 2 Litre], Details.[Price - 2
Litre], Details.[Qty - Litre], Details.[Qty - Pint], Details.[Qty - 2
Litres], Details.Cream, Details.Bread, Details.Eggs, Details.Notes,
Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.city Like
IIf(Forms![search Form]!city Is Null,"*",Forms![search Form]!city) And
Details.county Like IIf(Forms![search Form]!county Is Null,"*",Forms![search
Form]!county) And Details.[current supplier] Like IIf(Forms![search
Form]![current supplier] Is Null,"*",Forms![search Form]![current supplier])
And Details.asian Like IIf(Forms![search Form]!asian Is
Null,"*",Forms![search Form]!asian) And Details.turnover>=Forms![search
Form]!turnovergreater And Details.turnover<=Forms![search Form]!turnoverless;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

When running the query on its own it does work for the first few times and
then fails to return anything which is what happens when using the form.

What do you mean by clearing the criteria between runs? When I run the query
the items from the last search aren't displayed. Is there a line of code
which you have to use to clear the criteria? (Does it store the critieria in
the background somewhere?)

When I rebuild the query from scratch it does work again for a few times and
then doesn't return any records.

Jeff Boyce said:
See in-line...

Martc said:
Its is exactly as though the form is not clearing out the old criteria. Would
this suggest a problem with the form and not the query? When I run the query
on its own it brings up exactly the same results.
Click to expand...

There's more than one way to interpret this ("exactly the same results").
Are you saying that running the query repeatedly, with differing criteria
works the first few times, then fails "exactly the same" as your form does?

Can you confirm that you have cleared ALL of the criteria between runs
(either the form or the query)?

Can you rebuild the query "from scratch" and have it work correctly?

--
Regards

Jeff Boyce
Jeff Boyce said:
We're not there ... we can't see your form. We don't know how you/your form
"returns a report". Specific information helps us come up with specific
suggestions.

By the way, what happens if you use the query ONLY as a query, filling in
the parameters/criteria for each run? Does this also fail?

Is there a chance your form isn't clearing out "old" criteria, so after a
few uses, the query is still running correctly, but no rows match the
(multiple) criteria your form holds?

--
Regards

Jeff Boyce
<Office/Access MVP>

I am using an input form to enter the information for the query which then
returns a report.

:

Hi
I'm using the following parameter query with a form, it works for the
first
few times and then doesn't bring back any records at all. To get it
working
again I need to rewrite the whole query! Whats happening?? Please help!

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address
2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Mobile Number], Details.[Telephone
Number],
Details.[Fax Number], Details.[Current Supplier], Details.[Current
Supplier],
Details.Group, Details.[Group A/C Code], Details.Turnover,
Details.[Sales
Rep], Details.[Sales Rep], Details.Asian, Details.[Price - Pint],
Details.[Price - Litre], Details.[Price - 2 Litre], Details.[Price - 2
Litre], Details.[Qty - Litre], Details.[Qty - Pint], Details.[Qty - 2
Litres], Details.Cream, Details.Bread, Details.Eggs, Details.Notes,
Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.city Like
IIf(Forms![search Form]!city Is Null,"*",Forms![search Form]!city) And
Details.county Like IIf(Forms![search Form]!county Is
Null,"*",Forms![search
Form]!county) And Details.[current supplier] Like IIf(Forms![search
Form]![current supplier] Is Null,"*",Forms![search Form]![current
supplier])
And Details.asian Like IIf(Forms![search Form]!asian Is
Null,"*",Forms![search Form]!asian) And Details.turnover>=Forms![search
Form]!turnovergreater And Details.turnover<=Forms![search
Form]!turnoverless;
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

Oh ho!

So there's something about the query that fails over time. If this were my
query, I'd start out with a minimum number of fields and conditions and test
it. If it didn't break, I'd add a few more and re-test, and repeat until I
started having problems.

This should help me identify just where the query has gotten too complex to
be stable.

Good luck!

Jeff Boyce
<Office/Access MVP>

Martc said:
When running the query on its own it does work for the first few times and
then fails to return anything which is what happens when using the form.

What do you mean by clearing the criteria between runs? When I run the query
the items from the last search aren't displayed. Is there a line of code
which you have to use to clear the criteria? (Does it store the critieria in
the background somewhere?)

When I rebuild the query from scratch it does work again for a few times and
then doesn't return any records.

Jeff Boyce said:
See in-line...

Martc said:
Its is exactly as though the form is not clearing out the old
Click to expand...
criteria.
Would
this suggest a problem with the form and not the query? When I run the query
on its own it brings up exactly the same results.
Click to expand...

There's more than one way to interpret this ("exactly the same results").
Are you saying that running the query repeatedly, with differing criteria
works the first few times, then fails "exactly the same" as your form does?

Can you confirm that you have cleared ALL of the criteria between runs
(either the form or the query)?

Can you rebuild the query "from scratch" and have it work correctly?

--
Regards

Jeff Boyce
:

We're not there ... we can't see your form. We don't know how
Click to expand...
you/your
form
"returns a report". Specific information helps us come up with specific
suggestions.

By the way, what happens if you use the query ONLY as a query,
Click to expand...
filling
in
the parameters/criteria for each run? Does this also fail?

Is there a chance your form isn't clearing out "old" criteria, so
Click to expand...
after
a
few uses, the query is still running correctly, but no rows match the
(multiple) criteria your form holds?

--
Regards

Jeff Boyce
<Office/Access MVP>

I am using an input form to enter the information for the query
Click to expand...
which
then
returns a report.

:

Hi
I'm using the following parameter query with a form, it works
Click to expand...
for
the
first
few times and then doesn't bring back any records at all. To get it
working
again I need to rewrite the whole query! Whats happening??
Click to expand...
Please
help!
SELECT Details.[Customer Name], Details.[Address 1], Details.[Address
2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Mobile Number], Details.[Telephone
Number],
Details.[Fax Number], Details.[Current Supplier], Details.[Current
Supplier],
Details.Group, Details.[Group A/C Code], Details.Turnover,
Details.[Sales
Rep], Details.[Sales Rep], Details.Asian, Details.[Price - Pint],
Details.[Price - Litre], Details.[Price - 2 Litre],
Click to expand...
Details.[Price -
2
Litre], Details.[Qty - Litre], Details.[Qty - Pint],
Click to expand...
Details.[Qty -
2
Litres], Details.Cream, Details.Bread, Details.Eggs, Details.Notes,
Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.city Like
IIf(Forms![search Form]!city Is Null,"*",Forms![search
Click to expand...
Form]!city)
And
Details.county Like IIf(Forms![search Form]!county Is
Null,"*",Forms![search
Form]!county) And Details.[current supplier] Like IIf(Forms![search
Form]![current supplier] Is Null,"*",Forms![search Form]![current
supplier])
And Details.asian Like IIf(Forms![search Form]!asian Is
Null,"*",Forms![search Form]!asian) And Details.turnover>=Forms![search
Form]!turnovergreater And Details.turnover<=Forms![search
Form]!turnoverless;
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

Parameter Query using a form 8
Parameter Query Form Problem 17
Stored procedure exception in .NET works fine in Query Analyzer 9
FWT Newsletter - Weekly - October 4, 2004 2
WTD: Nvidia Videocard for 4x AGP Slot 0

Top