Must Enter Terms Twice in Query?

M

mjaeky

When I am entering a parameter value say, for Last Name and I type in Smith,
after I click "OK" to run the query, sometimes the "enter parameter value"
box reappears empty and I have to enter the data again to have the results
returned to me.

I've peeked in Design View and can't find anything amiss. Any reason why
this would happen?
 
J

Jerry Whittle

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
 
A

Allen Browne

If the parameter name occurs twice in the query, there may be a slight
difference between them, such as an extra space in one.

Also, check in the Properties box (properties for the query, not a field),
for the Filter and OrderBy properties. Delete anything you find there.

If you're still stuck, switch the query to SQL View, and paste the SQL
statement here.
 
M

mjaeky

Thanks for the quick replies!

Here's the SQL. There are no primary keys nor relationships.

SELECT [Court Calls].Date, [Court Calls].[First Name], [Court Calls].[Last
Name], [Court Calls].Platoon, [Court Calls].[Type of Case], [Court
Calls].Judge, [Court Calls].Location, [Court Calls].[DOC Number], [Court
Calls].[In Custody?], [Court Calls].[Case Manager]
FROM [Court Calls]
WHERE ((([Court Calls].[Case Manager])=[type Case Manager]))
ORDER BY [Court Calls].Date;
 
A

Allen Browne

Is [Court Calls] a table? Or is it a query?

If you open the [Court Calls] table in design view, what is the data type of
the [Case Manager] field?

Exactly what is in the title of the parameters dialog when it occurs (both
times)?

What is in the Filter property of the query?
What's in the OrderBy property?

Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact the database:
Tools | Database Utilities | Compact/Repair

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

mjaeky said:
Thanks for the quick replies!

Here's the SQL. There are no primary keys nor relationships.

SELECT [Court Calls].Date, [Court Calls].[First Name], [Court Calls].[Last
Name], [Court Calls].Platoon, [Court Calls].[Type of Case], [Court
Calls].Judge, [Court Calls].Location, [Court Calls].[DOC Number], [Court
Calls].[In Custody?], [Court Calls].[Case Manager]
FROM [Court Calls]
WHERE ((([Court Calls].[Case Manager])=[type Case Manager]))
ORDER BY [Court Calls].Date;


Allen Browne said:
If the parameter name occurs twice in the query, there may be a slight
difference between them, such as an extra space in one.

Also, check in the Properties box (properties for the query, not a
field),
for the Filter and OrderBy properties. Delete anything you find there.

If you're still stuck, switch the query to SQL View, and paste the SQL
statement here.
Click to expand...
Click to expand...
 
J

Jerry Whittle

Try this:

PARAMETERS [type Case Manager] Text ( 255 );
SELECT [Court Calls].Date,
[Court Calls].[First Name],
[Court Calls].[Last Name],
[Court Calls].Platoon,
[Court Calls].[Type of Case],
[Court Calls].Judge,
[Court Calls].Location,
[Court Calls].[DOC Number],
[Court Calls].[In Custody?],
[Court Calls].[Case Manager]
FROM [Court Calls]
WHERE [Court Calls].[Case Manager]=[type Case Manager]
ORDER BY [Court Calls].[Date];

I also put brackets around Date as it's a reserved word and Access might be
getting confused with the Date function.

One other thought: Is this query used for a report? In that case you might
have sorting and grouping on a field that is named very similar, but not
exactly the same, as the parameter field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


mjaeky said:
Thanks for the quick replies!

Here's the SQL. There are no primary keys nor relationships.

SELECT [Court Calls].Date, [Court Calls].[First Name], [Court Calls].[Last
Name], [Court Calls].Platoon, [Court Calls].[Type of Case], [Court
Calls].Judge, [Court Calls].Location, [Court Calls].[DOC Number], [Court
Calls].[In Custody?], [Court Calls].[Case Manager]
FROM [Court Calls]
WHERE ((([Court Calls].[Case Manager])=[type Case Manager]))
ORDER BY [Court Calls].Date;


Allen Browne said:
If the parameter name occurs twice in the query, there may be a slight
difference between them, such as an extra space in one.

Also, check in the Properties box (properties for the query, not a field),
for the Filter and OrderBy properties. Delete anything you find there.

If you're still stuck, switch the query to SQL View, and paste the SQL
statement here.
Click to expand...
Click to expand...
 
M

mjaeky

I'll answer Allen's questions first.

One, Court Calls is a table.
Two, the data type of the [Case Manager] field is text.
Three, the title of the paramaters dialog is type Case Manager both times it
occurs.
Four, the filter property of the query, I don't know where to look for that.
I didn't create any.
Five, the Orderby property is ORDER BY [Court Calls].Date;
Last, I did the uncheck and compact and I'm still getting the same messages.

To answer Jerry's questions....

I inserted your new parameters in the SQL and the same error message is
occurring.

The query is not used in a report. I print straight from the query.

Thanks again for everyone's help.

Matt
 
A

Allen Browne

In query design view, open the Properties Box.
Make sure the title of the dialog reads "Query", i.e. is it not giving the
properies of a field.

You will find the Filter and OrderBy properties in the dialog. (Note that
the OrderBy property is different than the ORDER BY clause in the query.)

Try creating a new query, switching to SQL View, and pasting this in:
SELECT [Court Calls].[Date],
[Court Calls].[First Name],
[Court Calls].[Last Name],
[Court Calls].Platoon,
[Court Calls].[Type of Case],
[Court Calls].Judge,
[Court Calls].Location,
[Court Calls].[DOC Number],
[Court Calls].[In Custody?],
[Court Calls].[Case Manager]
FROM [Court Calls]
WHERE ([Court Calls].[Case Manager] = [type Case Manager])
ORDER BY [Court Calls].[Date];
 
M

mjaeky

Not to be annoying but I deleted the query and created a new one. Worked
just fine. I was more curious as to why my problem was occurring. Thanks to
everyone for their time.

Matt
 
Top