Showing a report with records between two dates

SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
(((Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtStartDate])
And
(Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtFinishDate])));

SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
(((Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtStartDate])
And
(Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtFinishDate])));


"John Spencer" wrote:


Please copy and post the SQL of your query.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

jyanks wrote:
I'm honestly not having luck with any of the aforementioned solutions to the
problem. It either doesn't show me any of the records, or it shows me the
error described above.

Any further thoughts? Maybe I could email my database to someone for
troubleshooting? I might be overlooking something that I didn't think was
worth mentioning...

By using the CVDate function the values entered in the form are probably
being treated as arithmetical expressions, so the date to which it evaluates
will be that for which the result of the arithmetical expression is the
underlying value. You can se this if you enter the following in the Debug
window:

? Format(Cvdate(01/01/2000),"dd mmmm yyyy hh:nn:ss")

It will return 30 December 1899 00:00:43 because 30 December 1899 is
'day-zero 'in Access's date/time implementation. 01/01/2000 as an
arithmetical expression evaluates to 0.000505 which is why the above returns
a date/time value of 43 seconds into day-zero.

Do as I showed you and declare the parameters, and allow for values at the
end of the range with non-zero times of day:

PARAMETERS
[Forms]![frmAdminCreateSalesReport]![txtStartDate] DATETIME,
[Forms]![frmAdminCreateSalesReport]![txtFinishDate] DATETIME;
SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
Shifts.shift_date =
[Forms]![frmAdminCreateSalesReport]![txtStartDate]
AND Shifts.shift_date
DATEADD("d",1,[Forms]![frmAdminCreateSalesReport]![txtFinishDate]);

Ken Sheridan
Stafford, England

"jyanks" wrote:

SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
(((Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtStartDate])
And
(Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtFinishDate])));


"John Spencer" wrote:


Please copy and post the SQL of your query.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

jyanks wrote:
I'm honestly not having luck with any of the aforementioned solutions to the
problem. It either doesn't show me any of the records, or it shows me the
error described above.

Any further thoughts? Maybe I could email my database to someone for
troubleshooting? I might be overlooking something that I didn't think was
worth mentioning...

The report still shows no records, even though there are records set within
the input parameters.

"Ken Sheridan" wrote:

By using the CVDate function the values entered in the form are probably
being treated as arithmetical expressions, so the date to which it evaluates
will be that for which the result of the arithmetical expression is the
underlying value. You can se this if you enter the following in the Debug
window:

? Format(Cvdate(01/01/2000),"dd mmmm yyyy hh:nn:ss")

It will return 30 December 1899 00:00:43 because 30 December 1899 is
'day-zero 'in Access's date/time implementation. 01/01/2000 as an
arithmetical expression evaluates to 0.000505 which is why the above returns
a date/time value of 43 seconds into day-zero.

Do as I showed you and declare the parameters, and allow for values at the
end of the range with non-zero times of day:

PARAMETERS
[Forms]![frmAdminCreateSalesReport]![txtStartDate] DATETIME,
[Forms]![frmAdminCreateSalesReport]![txtFinishDate] DATETIME;
SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
Shifts.shift_date =
[Forms]![frmAdminCreateSalesReport]![txtStartDate]
AND Shifts.shift_date
DATEADD("d",1,[Forms]![frmAdminCreateSalesReport]![txtFinishDate]);

Ken Sheridan
Stafford, England

"jyanks" wrote:

SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
(((Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtStartDate])
And
(Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtFinishDate])));


"John Spencer" wrote:


Please copy and post the SQL of your query.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

jyanks wrote:
I'm honestly not having luck with any of the aforementioned solutions to the
problem. It either doesn't show me any of the records, or it shows me the
error described above.

Any further thoughts? Maybe I could email my database to someone for
troubleshooting? I might be overlooking something that I didn't think was
worth mentioning...

Try the following.

Parameters [Forms]![frmAdminCreateSalesReport]![txtStartDate] dateTime,
[Forms]![frmAdminCreateSalesReport]![txtFinishDate] dateTime;
SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE Shifts.shift_date Between
[Forms]![frmAdminCreateSalesReport]![txtStartDate]
And [Forms]![frmAdminCreateSalesReport]![txtFinishDate]

If that fails, then try hard coding the dates in and see if you get any results.

SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE Shifts.shift_date Between #2008/01/01# AND #2008/12/31#

If you get an error message, then post the error message.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

jyanks wrote:
SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
(((Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtStartDate])
And
(Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtFinishDate])));

I also went back to the Northwind database and changed one of the queries so
that it used a "Between [Type the beginning date] And [Type the ending date]"
parameter, and it worked when I did it in the Northwind's Invoices query. I'm
wondering what the difference between my sales query and the Northwind
Invoices query is.

I will say that I do not store a sale's date in the sales table- the date of
the sale is the date of the shift that the sale occurred on, which is why the
sales table stores a shift_ID rather than a sales date. Could this be the
missing information?

"Ken Sheridan" wrote:

By using the CVDate function the values entered in the form are probably
being treated as arithmetical expressions, so the date to which it evaluates
will be that for which the result of the arithmetical expression is the
underlying value. You can se this if you enter the following in the Debug
window:

? Format(Cvdate(01/01/2000),"dd mmmm yyyy hh:nn:ss")

It will return 30 December 1899 00:00:43 because 30 December 1899 is
'day-zero 'in Access's date/time implementation. 01/01/2000 as an
arithmetical expression evaluates to 0.000505 which is why the above returns
a date/time value of 43 seconds into day-zero.

Do as I showed you and declare the parameters, and allow for values at the
end of the range with non-zero times of day:

PARAMETERS
[Forms]![frmAdminCreateSalesReport]![txtStartDate] DATETIME,
[Forms]![frmAdminCreateSalesReport]![txtFinishDate] DATETIME;
SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
Shifts.shift_date =
[Forms]![frmAdminCreateSalesReport]![txtStartDate]
AND Shifts.shift_date
DATEADD("d",1,[Forms]![frmAdminCreateSalesReport]![txtFinishDate]);

Ken Sheridan
Stafford, England

"jyanks" wrote:

SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
(((Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtStartDate])
And
(Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtFinishDate])));


"John Spencer" wrote:


Please copy and post the SQL of your query.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

jyanks wrote:
I'm honestly not having luck with any of the aforementioned solutions to the
problem. It either doesn't show me any of the records, or it shows me the
error described above.

Any further thoughts? Maybe I could email my database to someone for
troubleshooting? I might be overlooking something that I didn't think was
worth mentioning...

The only other things which I can think of a

1. That one or other of the text box controls on the form has not been
updated and is therefore Null when the report opens. This would be the case
if a value had been entered but not updated by pressing the Enter or Tab
keys, moving focus off the control with the mouse etc. Normally, however,
with a dialogue form to enter parameters for a report the report would be
opened from a button on the form so moving focus to the button ensures that
the parameter controls are updated.

2. The problem is in the report not the query. Does the query also return
no rows if opened independently of the report while the form is open and with
updated values in both controls?

Ken Sheridan
Stafford, England

"jyanks" wrote:

The report still shows no records, even though there are records set within
the input parameters.

"Ken Sheridan" wrote:

By using the CVDate function the values entered in the form are probably
being treated as arithmetical expressions, so the date to which it evaluates
will be that for which the result of the arithmetical expression is the
underlying value. You can se this if you enter the following in the Debug
window:

? Format(Cvdate(01/01/2000),"dd mmmm yyyy hh:nn:ss")

It will return 30 December 1899 00:00:43 because 30 December 1899 is
'day-zero 'in Access's date/time implementation. 01/01/2000 as an
arithmetical expression evaluates to 0.000505 which is why the above returns
a date/time value of 43 seconds into day-zero.

Do as I showed you and declare the parameters, and allow for values at the
end of the range with non-zero times of day:

PARAMETERS
[Forms]![frmAdminCreateSalesReport]![txtStartDate] DATETIME,
[Forms]![frmAdminCreateSalesReport]![txtFinishDate] DATETIME;
SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
Shifts.shift_date =
[Forms]![frmAdminCreateSalesReport]![txtStartDate]
AND Shifts.shift_date
DATEADD("d",1,[Forms]![frmAdminCreateSalesReport]![txtFinishDate]);

Ken Sheridan
Stafford, England

"jyanks" wrote:

SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
(((Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtStartDate])
And
(Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtFinishDate])));


"John Spencer" wrote:


Please copy and post the SQL of your query.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

jyanks wrote:
I'm honestly not having luck with any of the aforementioned solutions to the
problem. It either doesn't show me any of the records, or it shows me the
error described above.

Any further thoughts? Maybe I could email my database to someone for
troubleshooting? I might be overlooking something that I didn't think was
worth mentioning...

How would I go about testing the second case?

"Ken Sheridan" wrote:

The only other things which I can think of a

1. That one or other of the text box controls on the form has not been
updated and is therefore Null when the report opens. This would be the case
if a value had been entered but not updated by pressing the Enter or Tab
keys, moving focus off the control with the mouse etc. Normally, however,
with a dialogue form to enter parameters for a report the report would be
opened from a button on the form so moving focus to the button ensures that
the parameter controls are updated.

2. The problem is in the report not the query. Does the query also return
no rows if opened independently of the report while the form is open and with
updated values in both controls?

Ken Sheridan
Stafford, England

"jyanks" wrote:

The report still shows no records, even though there are records set within
the input parameters.

"Ken Sheridan" wrote:

By using the CVDate function the values entered in the form are probably
being treated as arithmetical expressions, so the date to which it evaluates
will be that for which the result of the arithmetical expression is the
underlying value. You can se this if you enter the following in the Debug
window:

? Format(Cvdate(01/01/2000),"dd mmmm yyyy hh:nn:ss")

It will return 30 December 1899 00:00:43 because 30 December 1899 is
'day-zero 'in Access's date/time implementation. 01/01/2000 as an
arithmetical expression evaluates to 0.000505 which is why the above returns
a date/time value of 43 seconds into day-zero.

Do as I showed you and declare the parameters, and allow for values at the
end of the range with non-zero times of day:

PARAMETERS
[Forms]![frmAdminCreateSalesReport]![txtStartDate] DATETIME,
[Forms]![frmAdminCreateSalesReport]![txtFinishDate] DATETIME;
SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
Shifts.shift_date =
[Forms]![frmAdminCreateSalesReport]![txtStartDate]
AND Shifts.shift_date
DATEADD("d",1,[Forms]![frmAdminCreateSalesReport]![txtFinishDate]);

Ken Sheridan
Stafford, England

"jyanks" wrote:

SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
(((Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtStartDate])
And
(Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtFinishDate])));


"John Spencer" wrote:


Please copy and post the SQL of your query.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

jyanks wrote:
I'm honestly not having luck with any of the aforementioned solutions to the
problem. It either doesn't show me any of the records, or it shows me the
error described above.

Any further thoughts? Maybe I could email my database to someone for
troubleshooting? I might be overlooking something that I didn't think was
worth mentioning...

I don't think there's any more I can say at this distance. What you are
attempting should be very straightforward, so I'm at a loss to say where the
problem lies I'm afraid.

Ken Sheridan
Stafford, England

"jyanks" wrote:

The report still shows no records, even though there are records set within
the input parameters.

"Ken Sheridan" wrote:

By using the CVDate function the values entered in the form are probably
being treated as arithmetical expressions, so the date to which it evaluates
will be that for which the result of the arithmetical expression is the
underlying value. You can se this if you enter the following in the Debug
window:

? Format(Cvdate(01/01/2000),"dd mmmm yyyy hh:nn:ss")

It will return 30 December 1899 00:00:43 because 30 December 1899 is
'day-zero 'in Access's date/time implementation. 01/01/2000 as an
arithmetical expression evaluates to 0.000505 which is why the above returns
a date/time value of 43 seconds into day-zero.

Do as I showed you and declare the parameters, and allow for values at the
end of the range with non-zero times of day:

PARAMETERS
[Forms]![frmAdminCreateSalesReport]![txtStartDate] DATETIME,
[Forms]![frmAdminCreateSalesReport]![txtFinishDate] DATETIME;
SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
Shifts.shift_date =
[Forms]![frmAdminCreateSalesReport]![txtStartDate]
AND Shifts.shift_date
DATEADD("d",1,[Forms]![frmAdminCreateSalesReport]![txtFinishDate]);

Ken Sheridan
Stafford, England

"jyanks" wrote:

SELECT Shifts.shift_date, People.person_sex, ProductList.prod_name,
ProductList.prod_price, Sales.sale_qty, Sales.sale_total
FROM ProductList INNER JOIN ((People INNER JOIN Sales ON People.ID =
Sales.person_ID) INNER JOIN Shifts ON People.ID = Shifts.pa_ID) ON
ProductList.ID = Sales.product_ID
WHERE
(((Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtStartDate])
And
(Shifts.shift_date)=CVDate([Forms]![frmAdminCreateSalesReport]![txtFinishDate])));


"John Spencer" wrote:


Please copy and post the SQL of your query.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

jyanks wrote:
I'm honestly not having luck with any of the aforementioned solutions to the
problem. It either doesn't show me any of the records, or it shows me the
error described above.

Any further thoughts? Maybe I could email my database to someone for
troubleshooting? I might be overlooking something that I didn't think was
worth mentioning...