Showing a report with records between two dates

[jyanks]

Now I know that the way you display a report with records between two dates
is to set the Query criteria to something like: >#2/2/2006# and <#2/4/2006#

Say I want to have the user input two dates and then hit a button that
creates the sales report with sales in between those two dates.

I have the form, named 'frmAdminCreateSalesReport' with two text boxes named
'txtStartDate' and 'txtFinishDate'. I then have the query setup so that it
has a sales_date criteria of:

=[Forms]![frmAdminCreateSalesReport]![txtStartDate] And

<=[Forms]![frmAdminCreateSalesReport]![txtFinishDate]

When I hit the button to create the report, it's always blank, even if there
are records in between these two dates. The text box format is set to short
date, which is the same as the sales_date field. Any thoughts on why this
isn't working?

 

[MikeJohnB]

If I have understood your requirements properly, try in the query criteria
the following

Between [Forms]![frmAdminCreateSalesReport]![txtStartDate] And
Forms]![frmAdminCreateSalesReport]![txtStartDate]

I have not tested that but I would start with that expression if I wanted to
do the same using a form input.

Another way to do the same job but not as pretty is set up a parameter
query. The same as your select query but put the following in the criteria
row.

Between [Enter the First Date] And [Enter the Second Date]

Now each time the query is called from opening the form or report, there
will be a little message box saying what you have typed in square brackets
and giving a entry box for the answer.

As I say, it is no so pretty but works.

Hope this helps?????

Regards

Mike b
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Now I know that the way you display a report with records between two dates
is to set the Query criteria to something like: >#2/2/2006# and <#2/4/2006#

Say I want to have the user input two dates and then hit a button that
creates the sales report with sales in between those two dates.

I have the form, named 'frmAdminCreateSalesReport' with two text boxes named
'txtStartDate' and 'txtFinishDate'. I then have the query setup so that it
has a sales_date criteria of:

=[Forms]![frmAdminCreateSalesReport]![txtStartDate] And

<=Forms]![frmAdminCreateSalesReport]![txtStartDate]

When I hit the button to create the report, it's always blank, even if there
are records in between these two dates. The text box format is set to short
date, which is the same as the sales_date field. Any thoughts on why this
isn't working?

 

[fredg]

Now I know that the way you display a report with records between two dates
is to set the Query criteria to something like: >#2/2/2006# and <#2/4/2006#

Say I want to have the user input two dates and then hit a button that
creates the sales report with sales in between those two dates.

I have the form, named 'frmAdminCreateSalesReport' with two text boxes named
'txtStartDate' and 'txtFinishDate'. I then have the query setup so that it
has a sales_date criteria of:

=[Forms]![frmAdminCreateSalesReport]![txtStartDate] And

<=[Forms]![frmAdminCreateSalesReport]![txtFinishDate]

When I hit the button to create the report, it's always blank, even if there
are records in between these two dates. The text box format is set to short
date, which is the same as the sales_date field. Any thoughts on why this
isn't working?

That form must remain open when the report is run. I suspect it's not
open.

How are you opening the form?
How are you opening the Report?
How and when are you closing the form?

My method is to open the Report.
The report's open event is then used to open the parameter form in
dialog:

DoCmd.OpenForm "FormName", , , , , acDialog

Then a command button on the form is coded:
Me.Visible = False

Code the Report's Close event:
DoCmd.Close acForm, "FormName"


When you wish to run the report, open the report.
It opens the form into which you enter the start and end dates.
When you click the form's command button, the form hides and the
report runs.
When the report closes, the form is also closed.

 

[MikeJohnB]

By the way, I have just noticed that your criteria dates are both set as
Forms]![frmAdminCreateSalesReport]![txtStartDate] (txtStartDate), no
txtEndDate, is your problem because these are the same dates and therefore
there are no records for that period??? or was it just a typo????

regards

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

If I have understood your requirements properly, try in the query criteria
the following

Between [Forms]![frmAdminCreateSalesReport]![txtStartDate] And
Forms]![frmAdminCreateSalesReport]![txtStartDate]

I have not tested that but I would start with that expression if I wanted to
do the same using a form input.

Another way to do the same job but not as pretty is set up a parameter
query. The same as your select query but put the following in the criteria
row.

Between [Enter the First Date] And [Enter the Second Date]

Now each time the query is called from opening the form or report, there
will be a little message box saying what you have typed in square brackets
and giving a entry box for the answer.

As I say, it is no so pretty but works.

Hope this helps?????

Regards

Mike b
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Now I know that the way you display a report with records between two dates
is to set the Query criteria to something like: >#2/2/2006# and <#2/4/2006#

Say I want to have the user input two dates and then hit a button that
creates the sales report with sales in between those two dates.

I have the form, named 'frmAdminCreateSalesReport' with two text boxes named
'txtStartDate' and 'txtFinishDate'. I then have the query setup so that it
has a sales_date criteria of:

=[Forms]![frmAdminCreateSalesReport]![txtStartDate] And

<=Forms]![frmAdminCreateSalesReport]![txtStartDate]

When I hit the button to create the report, it's always blank, even if there
are records in between these two dates. The text box format is set to short
date, which is the same as the sales_date field. Any thoughts on why this
isn't working?

 

[KARL DEWEY]

The text box format is set to short date
I would remove the formatting and change query like this --

=CVDate([Forms]![frmAdminCreateSalesReport]![txtStartDate]) And <=CVDate([Forms]![frmAdminCreateSalesReport]![txtFinishDate])

--
KARL DEWEY
Build a little - Test a little

Now I know that the way you display a report with records between two dates
is to set the Query criteria to something like: >#2/2/2006# and <#2/4/2006#

Say I want to have the user input two dates and then hit a button that
creates the sales report with sales in between those two dates.

I have the form, named 'frmAdminCreateSalesReport' with two text boxes named
'txtStartDate' and 'txtFinishDate'. I then have the query setup so that it
has a sales_date criteria of:

=[Forms]![frmAdminCreateSalesReport]![txtStartDate] And

<=[Forms]![frmAdminCreateSalesReport]![txtFinishDate]

When I hit the button to create the report, it's always blank, even if there
are records in between these two dates. The text box format is set to short
date, which is the same as the sales_date field. Any thoughts on why this
isn't working?

 

[jyanks]

Yes, that was a typo, but it was correct when I had it in the criteria box.

I did what you said and now I'm getting the error:
"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"

By the way, I have just noticed that your criteria dates are both set as
Forms]![frmAdminCreateSalesReport]![txtStartDate] (txtStartDate), no
txtEndDate, is your problem because these are the same dates and therefore
there are no records for that period??? or was it just a typo????

regards

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

If I have understood your requirements properly, try in the query criteria
the following

Between [Forms]![frmAdminCreateSalesReport]![txtStartDate] And
Forms]![frmAdminCreateSalesReport]![txtStartDate]

I have not tested that but I would start with that expression if I wanted to
do the same using a form input.

Another way to do the same job but not as pretty is set up a parameter
query. The same as your select query but put the following in the criteria
row.

Between [Enter the First Date] And [Enter the Second Date]

Now each time the query is called from opening the form or report, there
will be a little message box saying what you have typed in square brackets
and giving a entry box for the answer.

As I say, it is no so pretty but works.

Hope this helps?????

Regards

Mike b
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Now I know that the way you display a report with records between two dates
is to set the Query criteria to something like: >#2/2/2006# and <#2/4/2006#

Say I want to have the user input two dates and then hit a button that
creates the sales report with sales in between those two dates.

I have the form, named 'frmAdminCreateSalesReport' with two text boxes named
'txtStartDate' and 'txtFinishDate'. I then have the query setup so that it
has a sales_date criteria of:
=[Forms]![frmAdminCreateSalesReport]![txtStartDate] And
<=Forms]![frmAdminCreateSalesReport]![txtStartDate]

When I hit the button to create the report, it's always blank, even if there
are records in between these two dates. The text box format is set to short
date, which is the same as the sales_date field. Any thoughts on why this
isn't working?

 

[MikeJohnB]

mmmm, I am not sure why it would throw up that error, its the same criteria
that you had with the exception of Between, I wonder if its a format issue?
However, take a look at the replies from both Fredg and Karl, both seem to
have a valid solution and I concur with Fred on the fact that the form would
have to stay open.
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Yes, that was a typo, but it was correct when I had it in the criteria box.

I did what you said and now I'm getting the error:
"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"

By the way, I have just noticed that your criteria dates are both set as
Forms]![frmAdminCreateSalesReport]![txtStartDate] (txtStartDate), no
txtEndDate, is your problem because these are the same dates and therefore
there are no records for that period??? or was it just a typo????

regards

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

If I have understood your requirements properly, try in the query criteria
the following

Between [Forms]![frmAdminCreateSalesReport]![txtStartDate] And
Forms]![frmAdminCreateSalesReport]![txtStartDate]

I have not tested that but I would start with that expression if I wanted to
do the same using a form input.

Another way to do the same job but not as pretty is set up a parameter
query. The same as your select query but put the following in the criteria
row.

Between [Enter the First Date] And [Enter the Second Date]

Now each time the query is called from opening the form or report, there
will be a little message box saying what you have typed in square brackets
and giving a entry box for the answer.

As I say, it is no so pretty but works.

Hope this helps?????

Regards

Mike b
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


:

Now I know that the way you display a report with records between two dates
is to set the Query criteria to something like: >#2/2/2006# and <#2/4/2006#

Say I want to have the user input two dates and then hit a button that
creates the sales report with sales in between those two dates.

I have the form, named 'frmAdminCreateSalesReport' with two text boxes named
'txtStartDate' and 'txtFinishDate'. I then have the query setup so that it
has a sales_date criteria of:
=[Forms]![frmAdminCreateSalesReport]![txtStartDate] And
<=Forms]![frmAdminCreateSalesReport]![txtStartDate]

When I hit the button to create the report, it's always blank, even if there
are records in between these two dates. The text box format is set to short
date, which is the same as the sales_date field. Any thoughts on why this
isn't working?

 

[Ken Sheridan]

There are a number of points you need to take into account here:

1. Are you completely confidant that all of your sales_date values include
zero times of day? There is no such thing in Access as a date value per se,
only date/time values. A non-zero time of day can creep in without your
realizing it; the inappropriate use of the Now() function is a common
culprit. So unless you have taken specific steps in the table definition to
allow only values with zero times of day I'd allow for this, otherwise rows
with dates on the final day of the range might not be returned. Its easy to
do; you just look for values on or after the start date and before one day
after the end date.

2. Date/time parameters in a query should always be declared as such.
Otherwise a value entered in short date format can be interpreted as an
arithmetical expression and give the wrong result. The parameters can be
declared in design view via the interface, or in SQL view.

3. When entering the criteria in design view you must include the field
name in the second part of the operation; e.g. >#2/2/2006# And sales_date
<#2/4/2006#.

Putting all this together the query would be something like this:

PARAMETERS
[Forms]![frmAdminCreateSalesReport]![txtStartDate] DATE TIME,
[Forms]![frmAdminCreateSalesReport]![txtFinishDate] DATETIME;
SELECT *
FROM Sales
WHERE sales_date >=
[Forms]![frmAdminCreateSalesReport]![txtStartDate]
AND sales_date <
DATEADD("d",1, [Forms]![frmAdminCreateSalesReport]![txtFinishDate]);

Ken Sheridan
Stafford, England

Now I know that the way you display a report with records between two dates
is to set the Query criteria to something like: >#2/2/2006# and <#2/4/2006#

Say I want to have the user input two dates and then hit a button that
creates the sales report with sales in between those two dates.

I have the form, named 'frmAdminCreateSalesReport' with two text boxes named
'txtStartDate' and 'txtFinishDate'. I then have the query setup so that it
has a sales_date criteria of:

=[Forms]![frmAdminCreateSalesReport]![txtStartDate] And

<=[Forms]![frmAdminCreateSalesReport]![txtFinishDate]

When I hit the button to create the report, it's always blank, even if there
are records in between these two dates. The text box format is set to short
date, which is the same as the sales_date field. Any thoughts on why this
isn't working?

 

[jyanks]

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...

 

[John Spencer]

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]

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])));

 

[jyanks]

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])));

 

[Ken Sheridan]

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

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])));

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]

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

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

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])));

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...

 

[John Spencer]

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]

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?

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

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])));

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...

 

[Ken Sheridan]

The only other things which I can think of are:

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

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

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

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])));


:


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...

 

[jyanks]

How would I go about testing the second case?

The only other things which I can think of are:

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

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

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

:

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])));


:


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...

 

[Ken Sheridan]

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

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

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

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])));


:


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...