Subreport confusion

[Guest]

I am looking for some assistance with a report.

I have a report that totals the number of items sold for in various categories
( Apples = 10, Oranges 15, Pears = 26). I am not interested in the
detail of the report so I have created a footer for the category field to
display the totals and then a report footer for the overall total. The
report prompts for a Start Date & End Date when running it.

I now need an almost identical report; however I want to take one specific
category out of the main section of the report and display it below the
report total since it should not be included within the main report total.
For example, the category of Grapes should not be included in the report
total but I need to display it on the same report beneath the total.

I thought I could create an unbound subreport in the report footer, placing
the Grapes category beneath the total and then exclude Grapes from the main
report. I didn’t think it would be a bound report since there is no real
link between the data. The only “link†I believe exists is that I want to
use the same Start Date & End Date to run the report. However, if I have no
links established in my subreport to my main report it prompts for the dates
4 times and then produces no data in the subreport.

Can anyone give me some guidance as to where I might be going wrong and what
I can do to get this to work?

 

[Duane Hookom]

It sounds like your report solution should work. However, consider kicking
your development up a notch and don't use parameter prompts. Use references
to controls on forms. This allows you to take advantage of:
1) combo and list boxes
2) check boxes
3) multiple selection
4) subforms
5) default values
6) validity checking
7) saving parameter values
8) providing a much more professional interface

 

[Guest]

I think I am on the right track. I have my user form set up to collect my
date fields. I am now just having some syntax problems with my SQL statement
for my subquery. I am receiving a "snytax error (missing operator) in
query expression" on the following and I can't seem to find the problem.

strSQL = "SELECT Type, SUM(No_of_Transactions) FROM Transactions " _
& "WHERE " & strField4 & " Between " & Format(Me.txtStartDate, _
conDateFormat) & " And " & Format(Me.txtEndDate, conDateFormat) _
& " And " & strField3 & " = 'Web' GROUP BY Type "

Thanks!
Kathy

 

[Duane Hookom]

Try add in some "#":

strSQL = "SELECT Type, SUM(No_of_Transactions) FROM Transactions " _
& "WHERE " & strField4 & " Between #" & Format(Me.txtStartDate, _
conDateFormat) & "# And #" & Format(Me.txtEndDate, conDateFormat) _
& "# And " & strField3 & " = 'Web' GROUP BY Type "

 

[Guest]

This still does not correct my problem. I believe the conDateFormat was
already taking care of the".

I have a msgbox to show me how my final SQL statement reads. Using the code
below places 2 "#" before and after eacy date. An interesting thing to note
is that the statement returned by my msgbox reads differently from the
statement appearing in the error. The Group By part of the statement is
missing.

Msgbox shows:
SELECT Type, SUM(No_of_Transactions) FROM Transactions WHERE Date Received
Between #02/01/2006# And #02/21/2006# And Department = 'Web' GROUP BY Type

Error message shows:
Syntax error(missing operator) in query expressions 'Date Received Between
#02/01/2006# And #02/21/2006# And Department = 'Web".

 

[Duane Hookom]

You didn't tell us that you allowed spaces in your field names. Try:
strSQL = "SELECT [Type], SUM([No_of_Transactions]) FROM Transactions " _
& "WHERE [" & strField4 & "] Between " & Format(Me.txtStartDate, _
conDateFormat) & " And " & Format(Me.txtEndDate, conDateFormat) _
& " And [" & strField3 & "] = 'Web' GROUP BY [Type] "