Query asking for date paramter query twice for no apparent reason

[Dkline]

SELECT [Data Viewer Table].[Asset Location], [Policy
Inventory].PolicyNumber, [Total Mkt Value by Asset Location].[ALPN ID],
[Data Viewer Table].Program, [Data Viewer Table].InvestmentAccountName,
[Data Viewer Table].Custodian, [Data Viewer Table].[Account Type], [Total
Mkt Value by Asset Location].[SumOfMarket Value], [Policy Inventory].[Issue
Date]
FROM ([Data Viewer Table] LEFT JOIN [Total Mkt Value by Asset Location] ON
[Data Viewer Table].[ALPN ID] = [Total Mkt Value by Asset Location].[ALPN
ID]) LEFT JOIN [Policy Inventory] ON [Data Viewer Table].[Policy Number] =
[Policy Inventory].PolicyNumber
WHERE ((([Data Viewer Table].[Asset Location])<>172 And ([Data Viewer
Table].[Asset Location])<>179) AND (([Policy Inventory].[Issue
Date])<=[Enter Effective Date]));

When this query is run, the parameter query for the date is asked twice. Why
it asks twice is amystery to me.

The odd thing here is when I edit the query and then run it, it only asks
for the date once. Run the query again and it asks twice.

The objective of the query is to get all the records where the [Asset
Location] <> 172 AND <> 179 AND the [Issue Date] is less than the parameter.

So:
1. Regardless of [Issue Date] we don't any [Asset Location] = 172 or 179.
2. Regardless of [Asset Location] we don't want anything with an effective
[Issue Date] before parameter [Enter Effective Date]

I just can't get it to work without having to enter the parameter twice.

 

[Allen Browne]

Try explicitly declaring your parameter.

In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter:
[Enter Effective Date] Date/Time

This does 2 things:
- It declares the name, so Access knows what it is.
- it declares the type, so only valid dates can be entered and Access will
perform the right kind of comparison.

The fact Access has been inconsistent in the way it asks for the parameter
suggets that you may have a corruption in the database, and that corruption
may relate to the names of things. Naming the parameter might solve this
symptom, but turning off Name AutoCorrect could fix the source of the
corruption. For more info, see:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html

 

[Dkline]

Thanks for the reply.

I'm missing a trick here with the explicit declaration. In Query Design
View, from the menu I select the \Query\Parameters. Enter a Parameter and
select the type - date in this case. Run the query and it prompts for the
date but has no visible effect on query.

I can't figure out how to tell it I need to filter for dates after the
entered date. All it seems to do is add a line to the SQG statement:
PARAMETERS [Enter Date] DateTime

How do persuade it it should be part of the WHERE and that I want it to be
<= the date?

Try explicitly declaring your parameter.

In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter:
[Enter Effective Date] Date/Time

This does 2 things:
- It declares the name, so Access knows what it is.
- it declares the type, so only valid dates can be entered and Access will
perform the right kind of comparison.

The fact Access has been inconsistent in the way it asks for the parameter
suggets that you may have a corruption in the database, and that
corruption may relate to the names of things. Naming the parameter might
solve this symptom, but turning off Name AutoCorrect could fix the source
of the corruption. For more info, see:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html

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

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

SELECT [Data Viewer Table].[Asset Location], [Policy
Inventory].PolicyNumber, [Total Mkt Value by Asset Location].[ALPN ID],
[Data Viewer Table].Program, [Data Viewer Table].InvestmentAccountName,
[Data Viewer Table].Custodian, [Data Viewer Table].[Account Type], [Total
Mkt Value by Asset Location].[SumOfMarket Value], [Policy
Inventory].[Issue Date]
FROM ([Data Viewer Table] LEFT JOIN [Total Mkt Value by Asset Location]
ON [Data Viewer Table].[ALPN ID] = [Total Mkt Value by Asset
Location].[ALPN ID]) LEFT JOIN [Policy Inventory] ON [Data Viewer
Table].[Policy Number] = [Policy Inventory].PolicyNumber
WHERE ((([Data Viewer Table].[Asset Location])<>172 And ([Data Viewer
Table].[Asset Location])<>179) AND (([Policy Inventory].[Issue
Date])<=[Enter Effective Date]));

When this query is run, the parameter query for the date is asked twice.
Why it asks twice is amystery to me.

The odd thing here is when I edit the query and then run it, it only asks
for the date once. Run the query again and it asks twice.

The objective of the query is to get all the records where the [Asset
Location] <> 172 AND <> 179 AND the [Issue Date] is less than the
parameter.

So:
1. Regardless of [Issue Date] we don't any [Asset Location] = 172 or 179.
2. Regardless of [Asset Location] we don't want anything with an
effective [Issue Date] before parameter [Enter Effective Date]

I just can't get it to work without having to enter the parameter twice.

 

[Allen Browne]

You still need the parameter in the WHERE clause like it was before.

Note that the declared parameter must have exactly the same name as that in
the WHERE clause, spaces and all. In your example it was:
Enter Effective Date

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

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

Thanks for the reply.

I'm missing a trick here with the explicit declaration. In Query Design
View, from the menu I select the \Query\Parameters. Enter a Parameter and
select the type - date in this case. Run the query and it prompts for the
date but has no visible effect on query.

I can't figure out how to tell it I need to filter for dates after the
entered date. All it seems to do is add a line to the SQG statement:
PARAMETERS [Enter Date] DateTime

How do persuade it it should be part of the WHERE and that I want it to be
<= the date?

Try explicitly declaring your parameter.

In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter:
[Enter Effective Date] Date/Time

This does 2 things:
- It declares the name, so Access knows what it is.
- it declares the type, so only valid dates can be entered and Access
will perform the right kind of comparison.

The fact Access has been inconsistent in the way it asks for the
parameter suggets that you may have a corruption in the database, and
that corruption may relate to the names of things. Naming the parameter
might solve this symptom, but turning off Name AutoCorrect could fix the
source of the corruption. For more info, see:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html

SELECT [Data Viewer Table].[Asset Location], [Policy
Inventory].PolicyNumber, [Total Mkt Value by Asset Location].[ALPN ID],
[Data Viewer Table].Program, [Data Viewer Table].InvestmentAccountName,
[Data Viewer Table].Custodian, [Data Viewer Table].[Account Type],
[Total Mkt Value by Asset Location].[SumOfMarket Value], [Policy
Inventory].[Issue Date]
FROM ([Data Viewer Table] LEFT JOIN [Total Mkt Value by Asset Location]
ON [Data Viewer Table].[ALPN ID] = [Total Mkt Value by Asset
Location].[ALPN ID]) LEFT JOIN [Policy Inventory] ON [Data Viewer
Table].[Policy Number] = [Policy Inventory].PolicyNumber
WHERE ((([Data Viewer Table].[Asset Location])<>172 And ([Data Viewer
Table].[Asset Location])<>179) AND (([Policy Inventory].[Issue
Date])<=[Enter Effective Date]));

When this query is run, the parameter query for the date is asked twice.
Why it asks twice is amystery to me.

The odd thing here is when I edit the query and then run it, it only
asks for the date once. Run the query again and it asks twice.

The objective of the query is to get all the records where the [Asset
Location] <> 172 AND <> 179 AND the [Issue Date] is less than the
parameter.

So:
1. Regardless of [Issue Date] we don't any [Asset Location] = 172 or
179.
2. Regardless of [Asset Location] we don't want anything with an
effective [Issue Date] before parameter [Enter Effective Date]

I just can't get it to work without having to enter the parameter twice.

 

[Dkline]

Prior to your reply, I had actually figured it out by looking at the tips on
your website. Specifically the one titled "Limiting a Report to a Date
Range" http://members.iinet.net.au/~allenbrowne/casu-08.html

Works as advertised. Our problem is solved - we only get asked once for the
date.

If I upsize this to SQL Server, does this carry over? I ask because while it
is in the SQL View of the query in Access, I've never seen the keyword
PARAMETERS.

You still need the parameter in the WHERE clause like it was before.

Note that the declared parameter must have exactly the same name as that
in the WHERE clause, spaces and all. In your example it was:
Enter Effective Date

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

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

Thanks for the reply.

I'm missing a trick here with the explicit declaration. In Query Design
View, from the menu I select the \Query\Parameters. Enter a Parameter and
select the type - date in this case. Run the query and it prompts for the
date but has no visible effect on query.

I can't figure out how to tell it I need to filter for dates after the
entered date. All it seems to do is add a line to the SQG statement:
PARAMETERS [Enter Date] DateTime

How do persuade it it should be part of the WHERE and that I want it to
be <= the date?

Try explicitly declaring your parameter.

In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter:
[Enter Effective Date] Date/Time

This does 2 things:
- It declares the name, so Access knows what it is.
- it declares the type, so only valid dates can be entered and Access
will perform the right kind of comparison.

The fact Access has been inconsistent in the way it asks for the
parameter suggets that you may have a corruption in the database, and
that corruption may relate to the names of things. Naming the parameter
might solve this symptom, but turning off Name AutoCorrect could fix the
source of the corruption. For more info, see:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html


SELECT [Data Viewer Table].[Asset Location], [Policy
Inventory].PolicyNumber, [Total Mkt Value by Asset Location].[ALPN ID],
[Data Viewer Table].Program, [Data Viewer Table].InvestmentAccountName,
[Data Viewer Table].Custodian, [Data Viewer Table].[Account Type],
[Total Mkt Value by Asset Location].[SumOfMarket Value], [Policy
Inventory].[Issue Date]
FROM ([Data Viewer Table] LEFT JOIN [Total Mkt Value by Asset Location]
ON [Data Viewer Table].[ALPN ID] = [Total Mkt Value by Asset
Location].[ALPN ID]) LEFT JOIN [Policy Inventory] ON [Data Viewer
Table].[Policy Number] = [Policy Inventory].PolicyNumber
WHERE ((([Data Viewer Table].[Asset Location])<>172 And ([Data Viewer
Table].[Asset Location])<>179) AND (([Policy Inventory].[Issue
Date])<=[Enter Effective Date]));

When this query is run, the parameter query for the date is asked
twice. Why it asks twice is amystery to me.

The odd thing here is when I edit the query and then run it, it only
asks for the date once. Run the query again and it asks twice.

The objective of the query is to get all the records where the [Asset
Location] <> 172 AND <> 179 AND the [Issue Date] is less than the
parameter.

So:
1. Regardless of [Issue Date] we don't any [Asset Location] = 172 or
179.
2. Regardless of [Asset Location] we don't want anything with an
effective [Issue Date] before parameter [Enter Effective Date]

I just can't get it to work without having to enter the parameter
twice.

 

[Allen Browne]

Yes, SQL Server supports parameters.
You will find them discussed under "Stored Procedures".

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

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

Prior to your reply, I had actually figured it out by looking at the tips
on your website. Specifically the one titled "Limiting a Report to a Date
Range" http://members.iinet.net.au/~allenbrowne/casu-08.html

Works as advertised. Our problem is solved - we only get asked once for
the date.

If I upsize this to SQL Server, does this carry over? I ask because while
it is in the SQL View of the query in Access, I've never seen the keyword
PARAMETERS.

You still need the parameter in the WHERE clause like it was before.

Note that the declared parameter must have exactly the same name as that
in the WHERE clause, spaces and all. In your example it was:
Enter Effective Date

Thanks for the reply.

I'm missing a trick here with the explicit declaration. In Query Design
View, from the menu I select the \Query\Parameters. Enter a Parameter
and select the type - date in this case. Run the query and it prompts
for the date but has no visible effect on query.

I can't figure out how to tell it I need to filter for dates after the
entered date. All it seems to do is add a line to the SQG statement:
PARAMETERS [Enter Date] DateTime

How do persuade it it should be part of the WHERE and that I want it to
be <= the date?



Try explicitly declaring your parameter.

In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter:
[Enter Effective Date] Date/Time

This does 2 things:
- It declares the name, so Access knows what it is.
- it declares the type, so only valid dates can be entered and Access
will perform the right kind of comparison.

The fact Access has been inconsistent in the way it asks for the
parameter suggets that you may have a corruption in the database, and
that corruption may relate to the names of things. Naming the parameter
might solve this symptom, but turning off Name AutoCorrect could fix
the source of the corruption. For more info, see:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html


SELECT [Data Viewer Table].[Asset Location], [Policy
Inventory].PolicyNumber, [Total Mkt Value by Asset Location].[ALPN
ID], [Data Viewer Table].Program, [Data Viewer
Table].InvestmentAccountName, [Data Viewer Table].Custodian, [Data
Viewer Table].[Account Type], [Total Mkt Value by Asset
Location].[SumOfMarket Value], [Policy Inventory].[Issue Date]
FROM ([Data Viewer Table] LEFT JOIN [Total Mkt Value by Asset
Location] ON [Data Viewer Table].[ALPN ID] = [Total Mkt Value by Asset
Location].[ALPN ID]) LEFT JOIN [Policy Inventory] ON [Data Viewer
Table].[Policy Number] = [Policy Inventory].PolicyNumber
WHERE ((([Data Viewer Table].[Asset Location])<>172 And ([Data Viewer
Table].[Asset Location])<>179) AND (([Policy Inventory].[Issue
Date])<=[Enter Effective Date]));

When this query is run, the parameter query for the date is asked
twice. Why it asks twice is amystery to me.

The odd thing here is when I edit the query and then run it, it only
asks for the date once. Run the query again and it asks twice.

The objective of the query is to get all the records where the [Asset
Location] <> 172 AND <> 179 AND the [Issue Date] is less than the
parameter.

So:
1. Regardless of [Issue Date] we don't any [Asset Location] = 172 or
179.
2. Regardless of [Asset Location] we don't want anything with an
effective [Issue Date] before parameter [Enter Effective Date]

I just can't get it to work without having to enter the parameter
twice.

 

[Dkline]

When you back into "Design View" from SQL View, the Expression is in its own
field. In addition the Criteria has the value "<> False". Not sure why that
is there. Is it simply because the expression starts with or contains an
"IIF"?

Yes, SQL Server supports parameters.
You will find them discussed under "Stored Procedures".

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

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

Prior to your reply, I had actually figured it out by looking at the tips
on your website. Specifically the one titled "Limiting a Report to a Date
Range" http://members.iinet.net.au/~allenbrowne/casu-08.html

Works as advertised. Our problem is solved - we only get asked once for
the date.

If I upsize this to SQL Server, does this carry over? I ask because while
it is in the SQL View of the query in Access, I've never seen the keyword
PARAMETERS.

You still need the parameter in the WHERE clause like it was before.

Note that the declared parameter must have exactly the same name as that
in the WHERE clause, spaces and all. In your example it was:
Enter Effective Date


Thanks for the reply.

I'm missing a trick here with the explicit declaration. In Query
Design View, from the menu I select the \Query\Parameters. Enter a
Parameter and select the type - date in this case. Run the query and it
prompts for the date but has no visible effect on query.

I can't figure out how to tell it I need to filter for dates after the
entered date. All it seems to do is add a line to the SQG statement:
PARAMETERS [Enter Date] DateTime

How do persuade it it should be part of the WHERE and that I want it to
be <= the date?



Try explicitly declaring your parameter.

In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter:
[Enter Effective Date] Date/Time

This does 2 things:
- It declares the name, so Access knows what it is.
- it declares the type, so only valid dates can be entered and Access
will perform the right kind of comparison.

The fact Access has been inconsistent in the way it asks for the
parameter suggets that you may have a corruption in the database, and
that corruption may relate to the names of things. Naming the
parameter might solve this symptom, but turning off Name AutoCorrect
could fix the source of the corruption. For more info, see:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html


SELECT [Data Viewer Table].[Asset Location], [Policy
Inventory].PolicyNumber, [Total Mkt Value by Asset Location].[ALPN
ID], [Data Viewer Table].Program, [Data Viewer
Table].InvestmentAccountName, [Data Viewer Table].Custodian, [Data
Viewer Table].[Account Type], [Total Mkt Value by Asset
Location].[SumOfMarket Value], [Policy Inventory].[Issue Date]
FROM ([Data Viewer Table] LEFT JOIN [Total Mkt Value by Asset
Location] ON [Data Viewer Table].[ALPN ID] = [Total Mkt Value by
Asset Location].[ALPN ID]) LEFT JOIN [Policy Inventory] ON [Data
Viewer Table].[Policy Number] = [Policy Inventory].PolicyNumber
WHERE ((([Data Viewer Table].[Asset Location])<>172 And ([Data Viewer
Table].[Asset Location])<>179) AND (([Policy Inventory].[Issue
Date])<=[Enter Effective Date]));

When this query is run, the parameter query for the date is asked
twice. Why it asks twice is amystery to me.

The odd thing here is when I edit the query and then run it, it only
asks for the date once. Run the query again and it asks twice.

The objective of the query is to get all the records where the [Asset
Location] <> 172 AND <> 179 AND the [Issue Date] is less than the
parameter.

So:
1. Regardless of [Issue Date] we don't any [Asset Location] = 172 or
179.
2. Regardless of [Asset Location] we don't want anything with an
effective [Issue Date] before parameter [Enter Effective Date]

I just can't get it to work without having to enter the parameter
twice.

 

[Allen Browne]

Yes, that's how Access displays it in Design view.

It's not realy an improvement to the SQL statement, is it.

 

[Dkline]

All it did was confuse the end user.

She had another query which needed the same WHERE. She tried to reproduce it
in the QBE. I then showed her that it was easier to go into the original in
SQL View, copy just the WHERE, open the other query in SQL View, paste it -
you're done.

Some things are just easier in SQL.

Thanks for sticking with me on this.