Parameter query problem

[john134]

I have the following paramater query that is somewhat puzzling.

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT DISTINCT [Individual Donor Table].[Full Name], Sum([Individual Gift Amount Table].[Gift Amount]) AS [SumOfGift Amount]
FROM [Individual Donor Table] INNER JOIN [Individual Gift Amount Table] ON [Individual Donor Table].[Full Name] = [Individual Gift Amount Table].[Full Name]
GROUP BY [Individual Donor Table].[Full Name];

It works fine without the first line. However, with that first line, I am asked for the Start and End Dates twice. How to I resolve this issue?

john134

[pbaldy]

You don't use them, so why are they there at all? I'd expect 2 parameter prompts, one for StartDate and one for EndDate.

[kd2017]

I'm slightly confused by your question. You are asked for StartDate twice and asked for EndDate twice?

It appears that you are not using the parameters in your query it self? If so then why define them to begin with? Any time your query has PARAMETERS that means that access is going to prompt you for input for each variable defined in by the parameters, so it makes sense that it would ask you once for StartDate and once for EndDate.

[john134]

Sorry, I didn't make myself clear. It asks for Start Date then End Date and then again
Start Date and End Date. Each of the two parameters twice.

John134

[pbaldy]

Again, why not just take out the PARAMETERS clause, since you don't use them? I couldn't duplicate the issue, I only got prompted once each. Can you attach the db here?

[john134]

Thank you for your reply, Paul. It is much appreciated.

I use the parameters to filter the union query between two dates. Perhaps,
there is a better way to do if you have any suggestions.

I also had it working correctly the first time I queried but then the second
and subsequent queries I got the issue described above.

If you do not get the same results the second time around I will include the
tables.

John

[pbaldy]

If you get prompted twice, I'd wonder if the parameters were spelled differently. I don't see that you need them there anyway, and the PARAMETERS clause is optional for regular queries (not optional for crosstabs). What's the SQL of the other query?

[john134]

I've checked the spelling and there are no typos. If I don't
need the parameters how do I llimit the union query to a date
range?

Here is the SQL for the Union Query with the same problem.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Individual Donor Table].[Full Name], [Individual Gift Amount Table].[Gift Date], [Individual Gift Amount Table].[Gift Amount], [Individual Donor Table].ConfCode, [Individual Donor Table].[Street Address], [Individual Donor Table].City, [Individual Donor Table].State, [Individual Donor Table].ZIP, [Individual Donor Table].Telephone, [Individual Donor Table].[E-Mail]
FROM [Individual Donor Table] INNER JOIN [Individual Gift Amount Table] ON [Individual Donor Table].[Full Name] = [Individual Gift Amount Table].[Full Name]
GROUP BY [Individual Donor Table].[Full Name], [Individual Gift Amount Table].[Gift Date], [Individual Gift Amount Table].[Gift Amount], [Individual Donor Table].ConfCode, [Individual Donor Table].[Street Address], [Individual Donor Table].City, [Individual Donor Table].State, [Individual Donor Table].ZIP, [Individual Donor Table].Telephone, [Individual Donor Table].[E-Mail]
HAVING ((([Individual Gift Amount Table].[Gift Date])>=[Start Date] And ([Individual Gift Amount Table].[Gift Date])<=[End Date]) AND (([Individual Donor Table].ConfCode)="D"))
ORDER BY [Individual Donor Table].[Full Name], [Individual Gift Amount Table].[Gift Date]
UNION SELECT [Organization Donor Table].[Org Name], [Organization Gift Amount Table].GiftDte, [Organization Gift Amount Table].[Gift Amount], [Organization Donor Table].ConfCode, [Organization Donor Table].[Street Address], [Organization Donor Table].City, [Organization Donor Table].State, [Organization Donor Table].ZIP, [Organization Donor Table].Telephone, [Organization Donor Table].[E-Mail]
FROM [Organization Donor Table] INNER JOIN [Organization Gift Amount Table] ON [Organization Donor Table].[Org Name] = [Organization Gift Amount Table].[Organ Name]
WHERE ((([Organization Gift Amount Table].GiftDte)>=[Start Date] And ([Organization Gift Amount Table].GiftDte)<=[End Date]));

[pbaldy]

Do you see the problem?

StartDate
Start Date

[john134]

No, I don't see both those items. I copied it into Word and did a Find on both
words. The only one found was "Start Date."

I see I have a WHERE clause in the last select statement which includes the
Start Date and End Date words, but I don't have one in the first select statement.

I will add a WHERE clause to the first select statement to see if that solves the problem, but I doubt it.

[pbaldy]

The ones without spaces came from your first post. Is that query no longer involved?

[ssanfu]

Just a comment....

John, you shouldn't use special characters in object names. You have a dash in the field for the email address: "E-Mail".
Better is "EMail" or "E_Mail" (if you must separate the letters).


Should never use spaces in object names; field names, query, form, report or module names.


Your table names are very long, even without spaces. I aliased the names in the query:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT IDT.[Full Name], IGAT.[Gift Date], IGAT.[Gift Amount],IDT.ConfCode, IDT.[Street Address], IDT.City, IDT.State, IDT.ZIP, IDT.Telephone, IDT.[E-Mail]
FROM [Individual Donor Table] AS IDT INNER JOIN [Individual Gift Amount Table] AS IGAT ON IDT.[Full Name] = IGAT.[Full Name]
GROUP BY IDT.[Full Name], IGAT.[Gift Date], IGAT.[Gift Amount], IDT.ConfCode, IDT.[Street Address], IDT.City, IDT.State, IDT.ZIP, IDT.Telephone, IDT.[E-Mail]
HAVING (((IGAT.[Gift Date])>=[Start Date] And (IGAT.[Gift Date])<=[End Date]) AND ((IDT.ConfCode)="D"))
ORDER BY IDT.[Full Name], IGAT.[Gift Date]
UNION SELECT ODT.[Org Name], OGAT.GiftDte, OGAT.[Gift Amount], ODT.ConfCode, ODT.[Street Address], ODT.City, ODT.State, ODT.ZIP, ODT.Telephone, ODT.[E-Mail]
FROM [Organization Donor Table] AS ODT INNER JOIN [Organization Gift Amount Table] AS OGAT ON ODT.[Org Name] = OGAT.[Organ Name]
WHERE (((OGAT.GiftDte)>=[Start Date] And (OGAT.GiftDte)<=[End Date]));

This is how to alias names:

FROM [Individual Donor Table] AS IDT INNER JOIN [Individual Gift Amount Table] AS IGAT ON IDT.[Full Name] = IGAT.[Full Name]

FROM [Organization Donor Table] AS ODT INNER JOIN [Organization Gift Amount Table] AS OGAT ON ODT.[Org Name] = OGAT.[Organ Name]

Good luck with your project.....

[john134]

The ones without spaces came from your first post. Is that query no longer involved?

These are separate queries, the first just to illustrate the problem. The names chosen aren't relevant since
both have the same problem.

[pbaldy]

Can you attach the db here, or a sample that prompts twice for each parameter?

[john134]

Here is the query:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT DISTINCT [Individual Donor Table].[Full Name], Sum([Individual Gift Amount Table].[Gift Amount]) AS [SumOfGift Amount]
FROM [Individual Donor Table] INNER JOIN [Individual Gift Amount Table] ON [Individual Donor Table].[Full Name]=[Individual Gift Amount Table].[Full Name]
GROUP BY [Individual Donor Table].[Full Name]
UNION SELECT DISTINCT [Organization Donor Table].[Org Name], Sum([Organization Gift Amount Table].[Gift Amount]) AS [SumOfGift Amount]
FROM [Organization Donor Table] INNER JOIN [Organization Gift Amount Table] ON [Organization Donor Table].[Org Name] = [Organization Gift Amount Table].[Organ Name]
GROUP BY [Organization Donor Table].[Org Name];