User defined date range

[Guest]

I have probably asked this question before, but cannot find my post to refer
to. I need to create criteria in a query that will ask the user the date
range they want to retrieve records for (preferrably via a pop up dialog box
where the user will enter the first date of the range and then another box
where the user will enter the second date of the range). For the life of me
I cannot think of how to do this.

 

[Guest]

Between [Enter beginning date] And [Enter ending date]

 

[Guest]

Thanks Jerry, I got the pop up boxes I wanted asking for the dates. But when
I run the query with the date range being 11/30/2005 to 12/5/2005 it gives me
everything, not just the dates in that range (for example it listed a record
with the date of 12/1/2005 AND another record with the date of 12/21/206).
Is there some sort of limiter or anything that needs to be included in this?

Thank you again for your help!

Teri.

Between [Enter beginning date] And [Enter ending date]
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have probably asked this question before, but cannot find my post to refer
to. I need to create criteria in a query that will ask the user the date
range they want to retrieve records for (preferrably via a pop up dialog box
where the user will enter the first date of the range and then another box
where the user will enter the second date of the range). For the life of me
I cannot think of how to do this.

 

[Guest]

Teri:

Its always a good idea to declare date/time parameters as a date parameter
entered in short date format can otherwise be interpreted as an arithmetical
expression. This won't cause an error, but can give incorrect results.

Also the use of a BETWEEN….AND operation might fail to return rows on the
final day of the range if the values include a non-zero time of day (there is
no such thing as a Date value per se in Access only Date/Time values). A
time of day can easily be present in the data without you being aware of it,
so its best to assume this possibility no matter how confident you are of the
dates all having zero time of day elements. You can be sure of returning all
the rows in the range by using the alternative method of defining the range
in the query below, which also shows how to declare the parameters:

PARAMETERS
[Enter Start Date:] DATETIME,
[Enter End Date:] DATETIME;
SELECT*
FROM MyTable
WHERE MyDate >=[Enter Start Date:]
AND MyDate < [Enter End Date:]+1;

Ken Sheridan
Stafford, England

Thanks Jerry, I got the pop up boxes I wanted asking for the dates. But when
I run the query with the date range being 11/30/2005 to 12/5/2005 it gives me
everything, not just the dates in that range (for example it listed a record
with the date of 12/1/2005 AND another record with the date of 12/21/206).
Is there some sort of limiter or anything that needs to be included in this?

Thank you again for your help!

Teri.

Between [Enter beginning date] And [Enter ending date]
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have probably asked this question before, but cannot find my post to refer
to. I need to create criteria in a query that will ask the user the date
range they want to retrieve records for (preferrably via a pop up dialog box
where the user will enter the first date of the range and then another box
where the user will enter the second date of the range). For the life of me
I cannot think of how to do this.

 

[Guest]

Ken,

Your response is very infomative and I truly appreciate your help, but as
new as I am to Access, I'm not real sure how or where to put in a parameter,
I find this whole query thing a little confusing (or parameters not part of a
query?)

Thank you again,
Teri.

Teri:

Its always a good idea to declare date/time parameters as a date parameter
entered in short date format can otherwise be interpreted as an arithmetical
expression. This won't cause an error, but can give incorrect results.

Also the use of a BETWEEN….AND operation might fail to return rows on the
final day of the range if the values include a non-zero time of day (there is
no such thing as a Date value per se in Access only Date/Time values). A
time of day can easily be present in the data without you being aware of it,
so its best to assume this possibility no matter how confident you are of the
dates all having zero time of day elements. You can be sure of returning all
the rows in the range by using the alternative method of defining the range
in the query below, which also shows how to declare the parameters:

PARAMETERS
[Enter Start Date:] DATETIME,
[Enter End Date:] DATETIME;
SELECT*
FROM MyTable
WHERE MyDate >=[Enter Start Date:]
AND MyDate < [Enter End Date:]+1;

Ken Sheridan
Stafford, England

Thanks Jerry, I got the pop up boxes I wanted asking for the dates. But when
I run the query with the date range being 11/30/2005 to 12/5/2005 it gives me
everything, not just the dates in that range (for example it listed a record
with the date of 12/1/2005 AND another record with the date of 12/21/206).
Is there some sort of limiter or anything that needs to be included in this?

Thank you again for your help!

Teri.

Between [Enter beginning date] And [Enter ending date]
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have probably asked this question before, but cannot find my post to refer
to. I need to create criteria in a query that will ask the user the date
range they want to retrieve records for (preferrably via a pop up dialog box
where the user will enter the first date of the range and then another box
where the user will enter the second date of the range). For the life of me
I cannot think of how to do this.

 

[Guest]

Making assumptions on my part, I went to my query and zoomed in the criteria
portion of it and typed the following under the "Date Recieved" field. It
tells me "The expression you entered contains invalid syntax" and that "You
may have entered an operand without an operator. I know I must have done
something wrong, but am not sure what.

[Enter Start Date:] DATETIME, [Enter End Date:] DATETIME * FROM Payment
Information WHERE Date Recieved>=[Enter Start Date:] and Date Received <
[Enter End Date:]+1

Again, thanks,
Teri.

Teri:

Its always a good idea to declare date/time parameters as a date parameter
entered in short date format can otherwise be interpreted as an arithmetical
expression. This won't cause an error, but can give incorrect results.

Also the use of a BETWEEN….AND operation might fail to return rows on the
final day of the range if the values include a non-zero time of day (there is
no such thing as a Date value per se in Access only Date/Time values). A
time of day can easily be present in the data without you being aware of it,
so its best to assume this possibility no matter how confident you are of the
dates all having zero time of day elements. You can be sure of returning all
the rows in the range by using the alternative method of defining the range
in the query below, which also shows how to declare the parameters:

PARAMETERS
[Enter Start Date:] DATETIME,
[Enter End Date:] DATETIME;
SELECT*
FROM MyTable
WHERE MyDate >=[Enter Start Date:]
AND MyDate < [Enter End Date:]+1;

Ken Sheridan
Stafford, England

Thanks Jerry, I got the pop up boxes I wanted asking for the dates. But when
I run the query with the date range being 11/30/2005 to 12/5/2005 it gives me
everything, not just the dates in that range (for example it listed a record
with the date of 12/1/2005 AND another record with the date of 12/21/206).
Is there some sort of limiter or anything that needs to be included in this?

Thank you again for your help!

Teri.

Between [Enter beginning date] And [Enter ending date]
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have probably asked this question before, but cannot find my post to refer
to. I need to create criteria in a query that will ask the user the date
range they want to retrieve records for (preferrably via a pop up dialog box
where the user will enter the first date of the range and then another box
where the user will enter the second date of the range). For the life of me
I cannot think of how to do this.

 

[Guest]

Teri:

Once you've gained some experience using queries its easier to do this sort
of thing in SQL view, but you can achieve the same thing in query design view
as follows:

1. In the criteria row of the Date Received column enter:

=[Enter Start Date:] And [Date Received] < [Enter End Date:]+1

Note that the name of the Date Received field needs to be in square brackets
because it contains a space.

2. Still in query design view select Parameters from the Query menu. In
the first line of the dialogue enter [Enter Start Date:] in the Parameter
column and in the Data Type column select Date/Time from the pull down list.
In the second line enter [Enter End Date:] in the Parameter column and in the
Data Type column again select Date/Time from the pull down list.

After you save the query, if you open it again in design view you'll
probably find that Access has moved things around a bit. Don't worry; the
end result will be just the same. If you look at it in SQL view it will be
something similar to the SQL I sent you, but not neatly broken up over
several lines and with lots of parentheses thrown in.

Ken Sheridan
Stafford, England

Making assumptions on my part, I went to my query and zoomed in the criteria
portion of it and typed the following under the "Date Recieved" field. It
tells me "The expression you entered contains invalid syntax" and that "You
may have entered an operand without an operator. I know I must have done
something wrong, but am not sure what.

[Enter Start Date:] DATETIME, [Enter End Date:] DATETIME * FROM Payment
Information WHERE Date Recieved>=[Enter Start Date:] and Date Received <
[Enter End Date:]+1

Again, thanks,
Teri.

 

[Guest]

Thanks Ken, these instructions make more sense to me. But when I run my
query it still gives me dates outside of the range I am seeking. I don't
know if it makes a difference or not, but I have also put [Enter Name] in my
query so that I can pull only records for a specific person for a certain
date range. Is that what is messing me up? I have both criteria on the same
line too.

Thanks again, you are a gem!!
Teri.

Teri:

Once you've gained some experience using queries its easier to do this sort
of thing in SQL view, but you can achieve the same thing in query design view
as follows:

1. In the criteria row of the Date Received column enter:

=[Enter Start Date:] And [Date Received] < [Enter End Date:]+1

Note that the name of the Date Received field needs to be in square brackets
because it contains a space.

2. Still in query design view select Parameters from the Query menu. In
the first line of the dialogue enter [Enter Start Date:] in the Parameter
column and in the Data Type column select Date/Time from the pull down list.
In the second line enter [Enter End Date:] in the Parameter column and in the
Data Type column again select Date/Time from the pull down list.

After you save the query, if you open it again in design view you'll
probably find that Access has moved things around a bit. Don't worry; the
end result will be just the same. If you look at it in SQL view it will be
something similar to the SQL I sent you, but not neatly broken up over
several lines and with lots of parentheses thrown in.

Ken Sheridan
Stafford, England

Making assumptions on my part, I went to my query and zoomed in the criteria
portion of it and typed the following under the "Date Recieved" field. It
tells me "The expression you entered contains invalid syntax" and that "You
may have entered an operand without an operator. I know I must have done
something wrong, but am not sure what.

[Enter Start Date:] DATETIME, [Enter End Date:] DATETIME * FROM Payment
Information WHERE Date Recieved>=[Enter Start Date:] and Date Received <
[Enter End Date:]+1

Again, thanks,
Teri.

 

[Guest]

Teri:

One possibility is that you've created a Boolean OR operation rather than an
AND operation. This would be the case if you've put the date and name
parameters on separate rows in the query design grid. If you ask the query
to return those rows where the name is 'Ken' OR the dates are within 2006 it
would return all rows for me, regardless of date, plus the rows for everyone
else which fall within 2006. An AND operation on the other hand would return
only rows for me which fall in 2006 and no rows for anyone else.

If that doesn't help the best thing would be for you to switch the query
into SQL view and copy the SQL to the clipboard, then paste it into your
reply here. Then the dog might be able to see the rabbit. I think I'd
rather be a dog than a gem if its all the same with you; gems are usually
smooth, round and shiny, whereas I've still got all my own hair and hope to
stay that way<G>.

Ken Sheridan
Stafford, England

 

[Guest]

Ken,

Here is what I found in the SQL view:

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received], Sum([Payment Information].[Amount Payment Type
1]) AS [SumOfAmount Payment Type 1], Sum([Payment Information].[Amount
Payment Type 2]) AS [SumOfAmount Payment Type 2]
FROM Contacts INNER JOIN [Payment Information] ON Contacts.[Last Name] =
[Payment Information].[Last Name]
GROUP BY Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received], [Payment Information].[Last Name]
HAVING (((Contacts.[Last Name])=[Enter Name]) AND (([Payment
Information].[Date Received])<[Enter End Date:]+"1 "))
ORDER BY Contacts.[Last Name];

None of this makes much sense to me, at least not at this point. I am hopin
it will someday.

Gems are also quite bright and usually worth a lot more than they appear at
first glance, still want to be a dog?

Teri.

Teri:

One possibility is that you've created a Boolean OR operation rather than an
AND operation. This would be the case if you've put the date and name
parameters on separate rows in the query design grid. If you ask the query
to return those rows where the name is 'Ken' OR the dates are within 2006 it
would return all rows for me, regardless of date, plus the rows for everyone
else which fall within 2006. An AND operation on the other hand would return
only rows for me which fall in 2006 and no rows for anyone else.

If that doesn't help the best thing would be for you to switch the query
into SQL view and copy the SQL to the clipboard, then paste it into your
reply here. Then the dog might be able to see the rabbit. I think I'd
rather be a dog than a gem if its all the same with you; gems are usually
smooth, round and shiny, whereas I've still got all my own hair and hope to
stay that way<G>.

Ken Sheridan
Stafford, England

Thanks Ken, these instructions make more sense to me. But when I run my
query it still gives me dates outside of the range I am seeking. I don't
know if it makes a difference or not, but I have also put [Enter Name] in my
query so that I can pull only records for a specific person for a certain
date range. Is that what is messing me up? I have both criteria on the same
line too.

Thanks again, you are a gem!!
Teri.

 

[Guest]

Teri:

The fundamental problem is that while you are declaring a start date
parameter you are not actually restricting the result set with it, so even
though you are entering the date when prompted its having no effect.

There are one or two other points about the query, which are not
contributing to the problem, but nevertheless would be best corrected:

1. The query is adding "1" to the end date as a string (its delimited with
quotes) not as a number (which doesn’t need to be delimited).

2. The query is being restricted by a HAVING Clause not a WHERE clause.
This results from putting the criteria under the GROUP BY columns in design
view and is a very common mistake. When using design view what should be
done to create a WHERE clause is to add the Last Name and Date Received
columns twice to the grid. In one of each select WHERE from the 'total'
row's drop down list, uncheck the 'show' check box and put the criteria in
those columns not the GROUP BY ones. The difference is that a WHERE clause
acts on the data before its grouped, a HAVING clause after its grouped. The
latter is normally used to restrict a query on the basis of a group of rows,
e.g. if the average value of orders by a customer is > 1000 in a
GetGoodCustomers query.

So taking these points into account the SQL should look like this:

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT Contacts.[Last Name], Contacts.[First Name],
[Payment Information].[Date Received],
Sum([Payment Information].[Amount Payment Type 1]) AS [SumOfAmount Payment
Type 1], Sum([Payment Information].[Amount Payment Type 2]) AS [SumOfAmount
Payment Type 2]
FROM Contacts INNER JOIN [Payment Information]
ON Contacts.[Last Name] = [Payment Information].[Last Name]
WHERE Contacts.[Last Name] = [Enter Name])
AND [Payment Information].[Date Received] >=[Enter Start Date:]
AND [Payment Information].[Date Received] <[Enter End Date:]+1
GROUP BY Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received];

Copy this to the clipboard and paste it in to the query in SQL view in place
of the existing SQL and it should work.

Incidentally I see you are joining the tables on the LastName columns.
Names don't make good keys as they are not necessarily unique. Even
combinations of first and last names can be duplicated; I used to work with
two Maggie Taylors. Its much better to use a numeric key such as ContactID.
In the contacts table this can be an autonumber, but in the Payment
Information table it would be a long integer number data type. If you add
these columns to the tables the ContactID in Contacts would be filled
automatically, and you can easily populate the foreign key ContactID in
Payment Information with an update query:

UPDATE [Payment Information] INNER JOIN Contacts
ON [Payment Information].[Last Name] = Contacts.[Last Name]
SET [Payment Information].ContactID = [Contacts].[ContactID];

After running this query and satisfying yourself that the ContactID values
are correct you can delete the Last Name column from payment Information.
Each contact is now uniquely identified even if two have the same names.

I think I'll stay a dog. They have more fun than gems who just sit around
looking pretty all day!

Ken Sheridan
Stafford, England

Ken,

Here is what I found in the SQL view:

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received], Sum([Payment Information].[Amount Payment Type
1]) AS [SumOfAmount Payment Type 1], Sum([Payment Information].[Amount
Payment Type 2]) AS [SumOfAmount Payment Type 2]
FROM Contacts INNER JOIN [Payment Information] ON Contacts.[Last Name] =
[Payment Information].[Last Name]
GROUP BY Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received], [Payment Information].[Last Name]
HAVING (((Contacts.[Last Name])=[Enter Name]) AND (([Payment
Information].[Date Received])<[Enter End Date:]+"1 "))
ORDER BY Contacts.[Last Name];

None of this makes much sense to me, at least not at this point. I am hopin
it will someday.

Gems are also quite bright and usually worth a lot more than they appear at
first glance, still want to be a dog?

Teri.

 

[Guest]

Ken,

You are proving to be a very patient person, and I thank you very much for
that!! Tackling one issue at a time, I have copy and pasted the information
into my query (SQL view), but when I click to save it I get a message
stating: Invalid Bracketing of name 'SumOfAmount Payment Type 1'. E-gads!
Now what? I was looking at it and it seemed to me that there should be
parenthesis around the phrase (as well as the brackets) so I tried that and
got another message stating: The SELECT statement includes a reserved word or
an argument name that is misspelled or missing, or the punctuation is
incorrect. I have removed the parenthesis and have received the first
message again.

I will start working on the other issues once this one is resolved, I don't
want to get any more confused than I already am about the whole thing.

You are right, dogs are more fun than gems! And from what my aunt tells she
paid for her new little puppy, they can be worth just as much!!

Thank you again for your help and your patience!

Teri.

Teri:

The fundamental problem is that while you are declaring a start date
parameter you are not actually restricting the result set with it, so even
though you are entering the date when prompted its having no effect.

There are one or two other points about the query, which are not
contributing to the problem, but nevertheless would be best corrected:

1. The query is adding "1" to the end date as a string (its delimited with
quotes) not as a number (which doesn’t need to be delimited).

2. The query is being restricted by a HAVING Clause not a WHERE clause.
This results from putting the criteria under the GROUP BY columns in design
view and is a very common mistake. When using design view what should be
done to create a WHERE clause is to add the Last Name and Date Received
columns twice to the grid. In one of each select WHERE from the 'total'
row's drop down list, uncheck the 'show' check box and put the criteria in
those columns not the GROUP BY ones. The difference is that a WHERE clause
acts on the data before its grouped, a HAVING clause after its grouped. The
latter is normally used to restrict a query on the basis of a group of rows,
e.g. if the average value of orders by a customer is > 1000 in a
GetGoodCustomers query.

So taking these points into account the SQL should look like this:

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT Contacts.[Last Name], Contacts.[First Name],
[Payment Information].[Date Received],
Sum([Payment Information].[Amount Payment Type 1]) AS [SumOfAmount Payment
Type 1], Sum([Payment Information].[Amount Payment Type 2]) AS [SumOfAmount
Payment Type 2]
FROM Contacts INNER JOIN [Payment Information]
ON Contacts.[Last Name] = [Payment Information].[Last Name]
WHERE Contacts.[Last Name] = [Enter Name])
AND [Payment Information].[Date Received] >=[Enter Start Date:]
AND [Payment Information].[Date Received] <[Enter End Date:]+1
GROUP BY Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received];

Copy this to the clipboard and paste it in to the query in SQL view in place
of the existing SQL and it should work.

Incidentally I see you are joining the tables on the LastName columns.
Names don't make good keys as they are not necessarily unique. Even
combinations of first and last names can be duplicated; I used to work with
two Maggie Taylors. Its much better to use a numeric key such as ContactID.
In the contacts table this can be an autonumber, but in the Payment
Information table it would be a long integer number data type. If you add
these columns to the tables the ContactID in Contacts would be filled
automatically, and you can easily populate the foreign key ContactID in
Payment Information with an update query:

UPDATE [Payment Information] INNER JOIN Contacts
ON [Payment Information].[Last Name] = Contacts.[Last Name]
SET [Payment Information].ContactID = [Contacts].[ContactID];

After running this query and satisfying yourself that the ContactID values
are correct you can delete the Last Name column from payment Information.
Each contact is now uniquely identified even if two have the same names.

I think I'll stay a dog. They have more fun than gems who just sit around
looking pretty all day!

Ken Sheridan
Stafford, England

Ken,

Here is what I found in the SQL view:

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received], Sum([Payment Information].[Amount Payment Type
1]) AS [SumOfAmount Payment Type 1], Sum([Payment Information].[Amount
Payment Type 2]) AS [SumOfAmount Payment Type 2]
FROM Contacts INNER JOIN [Payment Information] ON Contacts.[Last Name] =
[Payment Information].[Last Name]
GROUP BY Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received], [Payment Information].[Last Name]
HAVING (((Contacts.[Last Name])=[Enter Name]) AND (([Payment
Information].[Date Received])<[Enter End Date:]+"1 "))
ORDER BY Contacts.[Last Name];

None of this makes much sense to me, at least not at this point. I am hopin
it will someday.

Gems are also quite bright and usually worth a lot more than they appear at
first glance, still want to be a dog?

Teri.

 

[Guest]

Teri:

Patience is my middle name. My mother wanted to call me Florence but my
father insisted.

One of the problems is due to the way my reply was formatted by the time it
reached you. I think this is because I draft my posts in Word to take
advantage of the spell-checker (I'm the world's worst speller!) and paste
them into the reply window. I find that carriage returns/line feeds
sometimes get inserted in the final result. Usually this is not a problem as
SQL can include carriage returns and they are used to make it more readable,
but here its happened in the middle of a column name.

The other problem is that when I stripped out all the surplus parentheses I
missed one. I've split the SQL better below to avoid inappropriate carriage
returns, but keep a look out for any in the middle of field or table names
and, if any have crept in, delete them.

PARAMETERS
[Enter Start Date:] DateTime,
[Enter End Date:] DateTime;
SELECT
Contacts.[Last Name],
Contacts.[First Name],
[Payment Information].[Date Received],
SUM([Payment Information].[Amount Payment Type 1])
AS [SumOfAmount Payment Type 1],
SUM([Payment Information].[Amount Payment Type 2])
AS [SumOfAmount Payment Type 2]
FROM Contacts INNER JOIN [Payment Information]
ON Contacts.[Last Name] = [Payment Information].[Last Name]
WHERE
Contacts.[Last Name] = [Enter Name]
AND [Payment Information].[Date Received] >=[Enter Start Date:]
AND [Payment Information].[Date Received] <[Enter End Date:]+1
GROUP BY
Contacts.[Last Name],
Contacts.[First Name],
[Payment Information].[Date Received];

Good luck

Ken Sheridan
Stafford, England

Ken,

You are proving to be a very patient person, and I thank you very much for
that!! Tackling one issue at a time, I have copy and pasted the information
into my query (SQL view), but when I click to save it I get a message
stating: Invalid Bracketing of name 'SumOfAmount Payment Type 1'. E-gads!
Now what? I was looking at it and it seemed to me that there should be
parenthesis around the phrase (as well as the brackets) so I tried that and
got another message stating: The SELECT statement includes a reserved word or
an argument name that is misspelled or missing, or the punctuation is
incorrect. I have removed the parenthesis and have received the first
message again.

I will start working on the other issues once this one is resolved, I don't
want to get any more confused than I already am about the whole thing.

You are right, dogs are more fun than gems! And from what my aunt tells she
paid for her new little puppy, they can be worth just as much!!

Thank you again for your help and your patience!

Teri.

Teri:

The fundamental problem is that while you are declaring a start date
parameter you are not actually restricting the result set with it, so even
though you are entering the date when prompted its having no effect.

There are one or two other points about the query, which are not
contributing to the problem, but nevertheless would be best corrected:

1. The query is adding "1" to the end date as a string (its delimited with
quotes) not as a number (which doesn’t need to be delimited).

2. The query is being restricted by a HAVING Clause not a WHERE clause.
This results from putting the criteria under the GROUP BY columns in design
view and is a very common mistake. When using design view what should be
done to create a WHERE clause is to add the Last Name and Date Received
columns twice to the grid. In one of each select WHERE from the 'total'
row's drop down list, uncheck the 'show' check box and put the criteria in
those columns not the GROUP BY ones. The difference is that a WHERE clause
acts on the data before its grouped, a HAVING clause after its grouped. The
latter is normally used to restrict a query on the basis of a group of rows,
e.g. if the average value of orders by a customer is > 1000 in a
GetGoodCustomers query.

So taking these points into account the SQL should look like this:

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT Contacts.[Last Name], Contacts.[First Name],
[Payment Information].[Date Received],
Sum([Payment Information].[Amount Payment Type 1]) AS [SumOfAmount Payment
Type 1], Sum([Payment Information].[Amount Payment Type 2]) AS [SumOfAmount
Payment Type 2]
FROM Contacts INNER JOIN [Payment Information]
ON Contacts.[Last Name] = [Payment Information].[Last Name]
WHERE Contacts.[Last Name] = [Enter Name])
AND [Payment Information].[Date Received] >=[Enter Start Date:]
AND [Payment Information].[Date Received] <[Enter End Date:]+1
GROUP BY Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received];

Copy this to the clipboard and paste it in to the query in SQL view in place
of the existing SQL and it should work.

Incidentally I see you are joining the tables on the LastName columns.
Names don't make good keys as they are not necessarily unique. Even
combinations of first and last names can be duplicated; I used to work with
two Maggie Taylors. Its much better to use a numeric key such as ContactID.
In the contacts table this can be an autonumber, but in the Payment
Information table it would be a long integer number data type. If you add
these columns to the tables the ContactID in Contacts would be filled
automatically, and you can easily populate the foreign key ContactID in
Payment Information with an update query:

UPDATE [Payment Information] INNER JOIN Contacts
ON [Payment Information].[Last Name] = Contacts.[Last Name]
SET [Payment Information].ContactID = [Contacts].[ContactID];

After running this query and satisfying yourself that the ContactID values
are correct you can delete the Last Name column from payment Information.
Each contact is now uniquely identified even if two have the same names.

I think I'll stay a dog. They have more fun than gems who just sit around
looking pretty all day!

Ken Sheridan
Stafford, England

Ken,

Here is what I found in the SQL view:

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received], Sum([Payment Information].[Amount Payment Type
1]) AS [SumOfAmount Payment Type 1], Sum([Payment Information].[Amount
Payment Type 2]) AS [SumOfAmount Payment Type 2]
FROM Contacts INNER JOIN [Payment Information] ON Contacts.[Last Name] =
[Payment Information].[Last Name]
GROUP BY Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received], [Payment Information].[Last Name]
HAVING (((Contacts.[Last Name])=[Enter Name]) AND (([Payment
Information].[Date Received])<[Enter End Date:]+"1 "))
ORDER BY Contacts.[Last Name];

None of this makes much sense to me, at least not at this point. I am hopin
it will someday.

Gems are also quite bright and usually worth a lot more than they appear at
first glance, still want to be a dog?

Teri.

 

[Guest]

Ken,

Sorry, my newly found best friend with the patience of a saint, still
problems. I have cut and pasted; I have checked and found no carriage
returns in the middle of table or field names; I am able to save the query
and able to click the run button and have it ask me the appropriate
questions. But after I answer the questions I get the message: This
expression is typed incorrectly, or it is too complex to be evaluated. For
example a numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables.

Teri

Teri:

Patience is my middle name. My mother wanted to call me Florence but my
father insisted.

One of the problems is due to the way my reply was formatted by the time it
reached you. I think this is because I draft my posts in Word to take
advantage of the spell-checker (I'm the world's worst speller!) and paste
them into the reply window. I find that carriage returns/line feeds
sometimes get inserted in the final result. Usually this is not a problem as
SQL can include carriage returns and they are used to make it more readable,
but here its happened in the middle of a column name.

The other problem is that when I stripped out all the surplus parentheses I
missed one. I've split the SQL better below to avoid inappropriate carriage
returns, but keep a look out for any in the middle of field or table names
and, if any have crept in, delete them.

PARAMETERS
[Enter Start Date:] DateTime,
[Enter End Date:] DateTime;
SELECT
Contacts.[Last Name],
Contacts.[First Name],
[Payment Information].[Date Received],
SUM([Payment Information].[Amount Payment Type 1])
AS [SumOfAmount Payment Type 1],
SUM([Payment Information].[Amount Payment Type 2])
AS [SumOfAmount Payment Type 2]
FROM Contacts INNER JOIN [Payment Information]
ON Contacts.[Last Name] = [Payment Information].[Last Name]
WHERE
Contacts.[Last Name] = [Enter Name]
AND [Payment Information].[Date Received] >=[Enter Start Date:]
AND [Payment Information].[Date Received] <[Enter End Date:]+1
GROUP BY
Contacts.[Last Name],
Contacts.[First Name],
[Payment Information].[Date Received];

Good luck

Ken Sheridan
Stafford, England

Ken,

You are proving to be a very patient person, and I thank you very much for
that!! Tackling one issue at a time, I have copy and pasted the information
into my query (SQL view), but when I click to save it I get a message
stating: Invalid Bracketing of name 'SumOfAmount Payment Type 1'. E-gads!
Now what? I was looking at it and it seemed to me that there should be
parenthesis around the phrase (as well as the brackets) so I tried that and
got another message stating: The SELECT statement includes a reserved word or
an argument name that is misspelled or missing, or the punctuation is
incorrect. I have removed the parenthesis and have received the first
message again.

I will start working on the other issues once this one is resolved, I don't
want to get any more confused than I already am about the whole thing.

You are right, dogs are more fun than gems! And from what my aunt tells she
paid for her new little puppy, they can be worth just as much!!

Thank you again for your help and your patience!

Teri.

Teri:

The fundamental problem is that while you are declaring a start date
parameter you are not actually restricting the result set with it, so even
though you are entering the date when prompted its having no effect.

There are one or two other points about the query, which are not
contributing to the problem, but nevertheless would be best corrected:

1. The query is adding "1" to the end date as a string (its delimited with
quotes) not as a number (which doesn’t need to be delimited).

2. The query is being restricted by a HAVING Clause not a WHERE clause.
This results from putting the criteria under the GROUP BY columns in design
view and is a very common mistake. When using design view what should be
done to create a WHERE clause is to add the Last Name and Date Received
columns twice to the grid. In one of each select WHERE from the 'total'
row's drop down list, uncheck the 'show' check box and put the criteria in
those columns not the GROUP BY ones. The difference is that a WHERE clause
acts on the data before its grouped, a HAVING clause after its grouped. The
latter is normally used to restrict a query on the basis of a group of rows,
e.g. if the average value of orders by a customer is > 1000 in a
GetGoodCustomers query.

So taking these points into account the SQL should look like this:

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT Contacts.[Last Name], Contacts.[First Name],
[Payment Information].[Date Received],
Sum([Payment Information].[Amount Payment Type 1]) AS [SumOfAmount Payment
Type 1], Sum([Payment Information].[Amount Payment Type 2]) AS [SumOfAmount
Payment Type 2]
FROM Contacts INNER JOIN [Payment Information]
ON Contacts.[Last Name] = [Payment Information].[Last Name]
WHERE Contacts.[Last Name] = [Enter Name])
AND [Payment Information].[Date Received] >=[Enter Start Date:]
AND [Payment Information].[Date Received] <[Enter End Date:]+1
GROUP BY Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received];

Copy this to the clipboard and paste it in to the query in SQL view in place
of the existing SQL and it should work.

Incidentally I see you are joining the tables on the LastName columns.
Names don't make good keys as they are not necessarily unique. Even
combinations of first and last names can be duplicated; I used to work with
two Maggie Taylors. Its much better to use a numeric key such as ContactID.
In the contacts table this can be an autonumber, but in the Payment
Information table it would be a long integer number data type. If you add
these columns to the tables the ContactID in Contacts would be filled
automatically, and you can easily populate the foreign key ContactID in
Payment Information with an update query:

UPDATE [Payment Information] INNER JOIN Contacts
ON [Payment Information].[Last Name] = Contacts.[Last Name]
SET [Payment Information].ContactID = [Contacts].[ContactID];

After running this query and satisfying yourself that the ContactID values
are correct you can delete the Last Name column from payment Information.
Each contact is now uniquely identified even if two have the same names.

I think I'll stay a dog. They have more fun than gems who just sit around
looking pretty all day!

Ken Sheridan
Stafford, England

:

Ken,

Here is what I found in the SQL view:

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received], Sum([Payment Information].[Amount Payment Type
1]) AS [SumOfAmount Payment Type 1], Sum([Payment Information].[Amount
Payment Type 2]) AS [SumOfAmount Payment Type 2]
FROM Contacts INNER JOIN [Payment Information] ON Contacts.[Last Name] =
[Payment Information].[Last Name]
GROUP BY Contacts.[Last Name], Contacts.[First Name], [Payment
Information].[Date Received], [Payment Information].[Last Name]
HAVING (((Contacts.[Last Name])=[Enter Name]) AND (([Payment
Information].[Date Received])<[Enter End Date:]+"1 "))
ORDER BY Contacts.[Last Name];

None of this makes much sense to me, at least not at this point. I am hopin
it will someday.

Gems are also quite bright and usually worth a lot more than they appear at
first glance, still want to be a dog?

Teri.

 

[Guest]

Teri:

Saintliness I deny completely!

I've built simple versions of your tables and opened the query and it works
fine, so I think the best thing would be for you to mail me at:

ken<dot>sheridan<at>dsl<dot>pipex<dot>com

I can then mail you a copy of the database I've created, with the tables and
the query. In fact I've included two queries, one joining the tables on the
Last Name columns as you did, and one joining them on numeric ContactID
columns. You'll see what happens in each if you add two people with the same
Last Name to the Contacts table.

I'm signing off for the night now. I don't know what time it is where you
are, but the sun's gone down here.

Regards

Ken Sheridan
Stafford, England