Need to search by date, but do not want the date reflected in results

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I know the subject line is confusing, that is because I am confused :)

I have one query the calculates the sum of plastic used in packageing an
order and the amount of trays. (see below consumenew query) and another
query to total the amounts used by container size. (see below
consumenewtotal query)

From the consumenewtotal I have made a report (consumenewtotal) that I want
to be able to search by date range and have made a calendar form with the
following code. (See calendar code).

The problem is that if I include the orderdate in the consumenewtotal query,
then when I get my list is shows a total for each day for each product
listed separately.

example

Confezione Vaschetta total
b2680 704 109120 01/11/2005
b2680 704 218240 01/11/2005

without the date I get the numbers I require as

b2680 1408 327360

How can I search by date and not have it separate the items by date?

Michael










consumenew query

SELECT [order details query].OrderID, [order details query].[Colli x
Pedana], [order details query].[N° Pedane], [order details query].confezione
AS [order details query_confezione], [order details query].confezione,
Consume.Film, Consume.Lung, Consume.desc, [Colli x Pedana]*[N°
Pedane]*[film]*[Lung]*[consume.desc]/1000 AS [consume film], [order details
query].orderdate
FROM [order details query] INNER JOIN Consume ON [order details
query].confezione = Consume.Confezione
GROUP BY [order details query].OrderID, [order details query].[Colli x
Pedana], [order details query].[N° Pedane], [order details
query].confezione, [order details query].confezione, Consume.Film,
Consume.Lung, Consume.desc, [Colli x Pedana]*[N°
Pedane]*[film]*[Lung]*[consume.desc]/1000, [order details query].orderdate;

consumenewtotals

SELECT Sum(([colli x Pedana]*[desc])) AS vas, consumenew.confezione,
Sum(consumenew.[consume film]) AS [SumOfconsume film], consumenew.orderdate
FROM consumenew
GROUP BY consumenew.confezione, consumenew.orderdate;

calendar code

Private Sub ok_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "consumenewtotal"
strField = "[OrderDate]"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub
 
Since the query is performing a GROUP BY and not returning a date, you
cannot use the WhereCondition of the OpenReport. Instead, you can add a
WHERE clause to your query, and have it read the text boxes from your form
directly.

1. Open your consumenewtotals query in deisng view.

2. Enter this into the Criteria row under your OrderDate field (substituting
your form name instead of Form1):
Between Nz([Forms].[Form1].[txtStartDate], #1/1/1900#)
And Nz([Forms].[Form1].[txtEndDate], #1/1/2299#)

3. Choose Parameters on the Query menu.
Enter 2 rows into the dialog, and tell it these are Date/Time parameters.
Use the name of your form, but it will end up like this:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time

4. Remove all code in your command button, and substitute just this:
DoCmd.OpenReport "consumenewtotal", acViewPreview

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

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

Michael said:
I know the subject line is confusing, that is because I am confused :)

I have one query the calculates the sum of plastic used in packageing an
order and the amount of trays. (see below consumenew query) and another
query to total the amounts used by container size. (see below
consumenewtotal query)

From the consumenewtotal I have made a report (consumenewtotal) that I
want to be able to search by date range and have made a calendar form with
the following code. (See calendar code).

The problem is that if I include the orderdate in the consumenewtotal
query, then when I get my list is shows a total for each day for each
product listed separately.

example

Confezione Vaschetta total
b2680 704 109120 01/11/2005
b2680 704 218240 01/11/2005

without the date I get the numbers I require as

b2680 1408 327360

How can I search by date and not have it separate the items by date?

Michael










consumenew query

SELECT [order details query].OrderID, [order details query].[Colli x
Pedana], [order details query].[N0 Pedane], [order details
query].confezione AS [order details query_confezione], [order details
query].confezione, Consume.Film, Consume.Lung, Consume.desc, [Colli x
Pedana]*[N0 Pedane]*[film]*[Lung]*[consume.desc]/1000 AS [consume film],
[order details query].orderdate
FROM [order details query] INNER JOIN Consume ON [order details
query].confezione = Consume.Confezione
GROUP BY [order details query].OrderID, [order details query].[Colli x
Pedana], [order details query].[N0 Pedane], [order details
query].confezione, [order details query].confezione, Consume.Film,
Consume.Lung, Consume.desc, [Colli x Pedana]*[N0
Pedane]*[film]*[Lung]*[consume.desc]/1000, [order details
query].orderdate;

consumenewtotals

SELECT Sum(([colli x Pedana]*[desc])) AS vas, consumenew.confezione,
Sum(consumenew.[consume film]) AS [SumOfconsume film],
consumenew.orderdate
FROM consumenew
GROUP BY consumenew.confezione, consumenew.orderdate;

calendar code

Private Sub ok_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = strField = "[OrderDate]"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub
Click to expand...
 
Thank you very much Allen,
When I enter Between Nz([Forms].[frmWhatDateConsume].[txtStartDate],
#1/1/1900#) And Nz([Forms].[frmWhatDateConsume].[txtEndDate], #1/1/2299#)
into my criteria, I get an error that highlights the comma after the
[txtStartDate], and the error reads, "The expression you entered contains
invalid syntax, you omitted an operand or operator, you entered an invalid
character or comma, or you entered text without surrounding it in quotation
marks:"
I am in Europe and I am not sure if that effects the comma in the expression
or not as it does do funny things to other functions of access.
thank you for taking the time to write all the code you created.
Michael



Allen Browne said:
Since the query is performing a GROUP BY and not returning a date, you
cannot use the WhereCondition of the OpenReport. Instead, you can add a
WHERE clause to your query, and have it read the text boxes from your form
directly.

1. Open your consumenewtotals query in deisng view.

2. Enter this into the Criteria row under your OrderDate field
(substituting your form name instead of Form1):
Between Nz([Forms].[Form1].[txtStartDate], #1/1/1900#)
And Nz([Forms].[Form1].[txtEndDate], #1/1/2299#)

3. Choose Parameters on the Query menu.
Enter 2 rows into the dialog, and tell it these are Date/Time parameters.
Use the name of your form, but it will end up like this:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time

4. Remove all code in your command button, and substitute just this:
DoCmd.OpenReport "consumenewtotal", acViewPreview

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

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

Michael said:
I know the subject line is confusing, that is because I am confused :)

I have one query the calculates the sum of plastic used in packageing an
order and the amount of trays. (see below consumenew query) and another
query to total the amounts used by container size. (see below
consumenewtotal query)

From the consumenewtotal I have made a report (consumenewtotal) that I
want to be able to search by date range and have made a calendar form
with the following code. (See calendar code).

The problem is that if I include the orderdate in the consumenewtotal
query, then when I get my list is shows a total for each day for each
product listed separately.

example

Confezione Vaschetta total
b2680 704 109120 01/11/2005
b2680 704 218240 01/11/2005

without the date I get the numbers I require as

b2680 1408 327360

How can I search by date and not have it separate the items by date?

Michael










consumenew query

SELECT [order details query].OrderID, [order details query].[Colli x
Pedana], [order details query].[N0 Pedane], [order details
query].confezione AS [order details query_confezione], [order details
query].confezione, Consume.Film, Consume.Lung, Consume.desc, [Colli x
Pedana]*[N0 Pedane]*[film]*[Lung]*[consume.desc]/1000 AS [consume film],
[order details query].orderdate
FROM [order details query] INNER JOIN Consume ON [order details
query].confezione = Consume.Confezione
GROUP BY [order details query].OrderID, [order details query].[Colli x
Pedana], [order details query].[N0 Pedane], [order details
query].confezione, [order details query].confezione, Consume.Film,
Consume.Lung, Consume.desc, [Colli x Pedana]*[N0
Pedane]*[film]*[Lung]*[consume.desc]/1000, [order details
query].orderdate;

consumenewtotals

SELECT Sum(([colli x Pedana]*[desc])) AS vas, consumenew.confezione,
Sum(consumenew.[consume film]) AS [SumOfconsume film],
consumenew.orderdate
FROM consumenew
GROUP BY consumenew.confezione, consumenew.orderdate;

calendar code

Private Sub ok_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = strField = "[OrderDate]"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub
Click to expand...
Click to expand...
 
It could be that the Access interface is misinterpreting the comma
separator.

Try switching the query to SQL View (View menu), and editing the SQL
statement there so it has the comma.

The Nz() only comes into play when the text boxes are blank, so just to get
this going, you could try:
Between [Forms].[Form1].[txtStartDate] And [Forms].[Form1].[txtEndDate]

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

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

Michael said:
Thank you very much Allen,
When I enter Between Nz([Forms].[frmWhatDateConsume].[txtStartDate],
#1/1/1900#) And Nz([Forms].[frmWhatDateConsume].[txtEndDate], #1/1/2299#)
into my criteria, I get an error that highlights the comma after the
[txtStartDate], and the error reads, "The expression you entered contains
invalid syntax, you omitted an operand or operator, you entered an
invalid character or comma, or you entered text without surrounding it in
quotation marks:"
I am in Europe and I am not sure if that effects the comma in the
expression or not as it does do funny things to other functions of access.
thank you for taking the time to write all the code you created.
Michael

Allen Browne said:
Since the query is performing a GROUP BY and not returning a date, you
cannot use the WhereCondition of the OpenReport. Instead, you can add a
WHERE clause to your query, and have it read the text boxes from your
form directly.

1. Open your consumenewtotals query in deisng view.

2. Enter this into the Criteria row under your OrderDate field
(substituting your form name instead of Form1):
Between Nz([Forms].[Form1].[txtStartDate], #1/1/1900#)
And Nz([Forms].[Form1].[txtEndDate], #1/1/2299#)

3. Choose Parameters on the Query menu.
Enter 2 rows into the dialog, and tell it these are Date/Time parameters.
Use the name of your form, but it will end up like this:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time

4. Remove all code in your command button, and substitute just this:
DoCmd.OpenReport "consumenewtotal", acViewPreview

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

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

Michael said:
I know the subject line is confusing, that is because I am confused :)

I have one query the calculates the sum of plastic used in packageing an
order and the amount of trays. (see below consumenew query) and another
query to total the amounts used by container size. (see below
consumenewtotal query)

From the consumenewtotal I have made a report (consumenewtotal) that I
want to be able to search by date range and have made a calendar form
with the following code. (See calendar code).

The problem is that if I include the orderdate in the consumenewtotal
query, then when I get my list is shows a total for each day for each
product listed separately.

example

Confezione Vaschetta total
b2680 704 109120 01/11/2005
b2680 704 218240 01/11/2005

without the date I get the numbers I require as

b2680 1408 327360

How can I search by date and not have it separate the items by date?

Michael










consumenew query

SELECT [order details query].OrderID, [order details query].[Colli x
Pedana], [order details query].[N0 Pedane], [order details
query].confezione AS [order details query_confezione], [order details
query].confezione, Consume.Film, Consume.Lung, Consume.desc, [Colli x
Pedana]*[N0 Pedane]*[film]*[Lung]*[consume.desc]/1000 AS [consume film],
[order details query].orderdate
FROM [order details query] INNER JOIN Consume ON [order details
query].confezione = Consume.Confezione
GROUP BY [order details query].OrderID, [order details query].[Colli x
Pedana], [order details query].[N0 Pedane], [order details
query].confezione, [order details query].confezione, Consume.Film,
Consume.Lung, Consume.desc, [Colli x Pedana]*[N0
Pedane]*[film]*[Lung]*[consume.desc]/1000, [order details
query].orderdate;

consumenewtotals

SELECT Sum(([colli x Pedana]*[desc])) AS vas, consumenew.confezione,
Sum(consumenew.[consume film]) AS [SumOfconsume film],
consumenew.orderdate
FROM consumenew
GROUP BY consumenew.confezione, consumenew.orderdate;

calendar code

Private Sub ok_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = strField = "[OrderDate]"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes
only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks Allen
Using the following SQL, I can limit the dates shown and that is helpful,
but when I open the range to include all the items or even only two days,
the items that have entries on 2 different days will still show the total
for those days and not one total for all consumed.
Michael

PARAMETERS [Forms].[frmWhatDateConsume].[txtStartDate] DateTime,
[Forms].[frmWhatDateConsume].[txtEndDate] DateTime;
SELECT Sum(([colli x Pedana]*[desc])) AS vas, consumenew.confezione,
Sum(consumenew.[consume film]) AS [SumOfconsume film]
FROM consumenew
GROUP BY consumenew.confezione, consumenew.orderdate
HAVING (((consumenew.orderdate) Between
[Forms].[frmWhatDateConsume].[txtStartDate] And
[Forms].[frmWhatDateConsume].[txtEndDate]));





Allen Browne said:
It could be that the Access interface is misinterpreting the comma
separator.

Try switching the query to SQL View (View menu), and editing the SQL
statement there so it has the comma.

The Nz() only comes into play when the text boxes are blank, so just to
get this going, you could try:
Between [Forms].[Form1].[txtStartDate] And [Forms].[Form1].[txtEndDate]

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

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

Michael said:
Thank you very much Allen,
When I enter Between Nz([Forms].[frmWhatDateConsume].[txtStartDate],
#1/1/1900#) And Nz([Forms].[frmWhatDateConsume].[txtEndDate], #1/1/2299#)
into my criteria, I get an error that highlights the comma after the
[txtStartDate], and the error reads, "The expression you entered contains
invalid syntax, you omitted an operand or operator, you entered an
invalid character or comma, or you entered text without surrounding it in
quotation marks:"
I am in Europe and I am not sure if that effects the comma in the
expression or not as it does do funny things to other functions of
access.
thank you for taking the time to write all the code you created.
Michael

Allen Browne said:
Since the query is performing a GROUP BY and not returning a date, you
cannot use the WhereCondition of the OpenReport. Instead, you can add a
WHERE clause to your query, and have it read the text boxes from your
form directly.

1. Open your consumenewtotals query in deisng view.

2. Enter this into the Criteria row under your OrderDate field
(substituting your form name instead of Form1):
Between Nz([Forms].[Form1].[txtStartDate], #1/1/1900#)
And Nz([Forms].[Form1].[txtEndDate], #1/1/2299#)

3. Choose Parameters on the Query menu.
Enter 2 rows into the dialog, and tell it these are Date/Time
parameters.
Use the name of your form, but it will end up like this:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time

4. Remove all code in your command button, and substitute just this:
DoCmd.OpenReport "consumenewtotal", acViewPreview

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

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

I know the subject line is confusing, that is because I am confused :)

I have one query the calculates the sum of plastic used in packageing
an order and the amount of trays. (see below consumenew query) and
another query to total the amounts used by container size. (see below
consumenewtotal query)

From the consumenewtotal I have made a report (consumenewtotal) that I
want to be able to search by date range and have made a calendar form
with the following code. (See calendar code).

The problem is that if I include the orderdate in the consumenewtotal
query, then when I get my list is shows a total for each day for each
product listed separately.

example

Confezione Vaschetta total
b2680 704 109120 01/11/2005
b2680 704 218240 01/11/2005

without the date I get the numbers I require as

b2680 1408 327360

How can I search by date and not have it separate the items by date?

Michael










consumenew query

SELECT [order details query].OrderID, [order details query].[Colli x
Pedana], [order details query].[N0 Pedane], [order details
query].confezione AS [order details query_confezione], [order details
query].confezione, Consume.Film, Consume.Lung, Consume.desc, [Colli x
Pedana]*[N0 Pedane]*[film]*[Lung]*[consume.desc]/1000 AS [consume
film], [order details query].orderdate
FROM [order details query] INNER JOIN Consume ON [order details
query].confezione = Consume.Confezione
GROUP BY [order details query].OrderID, [order details query].[Colli x
Pedana], [order details query].[N0 Pedane], [order details
query].confezione, [order details query].confezione, Consume.Film,
Consume.Lung, Consume.desc, [Colli x Pedana]*[N0
Pedane]*[film]*[Lung]*[consume.desc]/1000, [order details
query].orderdate;

consumenewtotals

SELECT Sum(([colli x Pedana]*[desc])) AS vas, consumenew.confezione,
Sum(consumenew.[consume film]) AS [SumOfconsume film],
consumenew.orderdate
FROM consumenew
GROUP BY consumenew.confezione, consumenew.orderdate;

calendar code

Private Sub ok_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = strField = "[OrderDate]"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes
only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Drag the OrderDate field into the grid a 2nd time.
In the Total row under this field, change Group BY to Where.
Remove the criteria from the earlier field, and put them here.

You should end up with a SQL statement that has a WHERE clause in the middle
instead of a HAVING clause at the end.
 
That did it! Thank you very much for taking the time to help me.
Michael
Allen Browne said:
Drag the OrderDate field into the grid a 2nd time.
In the Total row under this field, change Group BY to Where.
Remove the criteria from the earlier field, and put them here.

You should end up with a SQL statement that has a WHERE clause in the
middle instead of a HAVING clause at the end.

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

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

Michael said:
Thanks Allen
Using the following SQL, I can limit the dates shown and that is helpful,
but when I open the range to include all the items or even only two days,
the items that have entries on 2 different days will still show the total
for those days and not one total for all consumed.
Michael

PARAMETERS [Forms].[frmWhatDateConsume].[txtStartDate] DateTime,
[Forms].[frmWhatDateConsume].[txtEndDate] DateTime;
SELECT Sum(([colli x Pedana]*[desc])) AS vas, consumenew.confezione,
Sum(consumenew.[consume film]) AS [SumOfconsume film]
FROM consumenew
GROUP BY consumenew.confezione, consumenew.orderdate
HAVING (((consumenew.orderdate) Between
[Forms].[frmWhatDateConsume].[txtStartDate] And
[Forms].[frmWhatDateConsume].[txtEndDate]));
Click to expand...
Click to expand...
 
Back
Top