Subreport confusion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kathy - Lovullo - 20 Feb 2006 19:10 GMT

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?

Reply to this Message

Duane Hookom - 20 Feb 2006 19:59 GMT

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

Signature

Duane Hookom
MS Access MVP
--

>I am looking for some assistance with a report.
>
[quoted text clipped - 26 lines]
> what
> I can do to get this to work?

Reply to this Message

Kathy - Lovullo - 21 Feb 2006 16:46 GMT

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

> It sounds like your report solution should work. However, consider kicking
> your development up a notch and don't use parameter prompts. Use references
[quoted text clipped - 38 lines]
> > what
> > I can do to get this to work?

Reply to this Message

Duane Hookom - 21 Feb 2006 17:47 GMT

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 "

Signature

Duane Hookom
MS Access MVP
--

>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
[quoted text clipped - 62 lines]
>> > what
>> > I can do to get this to work?

Reply to this Message

Kathy - Lovullo - 21 Feb 2006 19:20 GMT

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

> Try add in some "#":
>
[quoted text clipped - 69 lines]
> >> > what
> >> > I can do to get this to work?

Reply to this Message

Duane Hookom - 21 Feb 2006 19:32 GMT

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] "

Signature

Duane Hookom
MS Access MVP
--

> This still does not correct my problem. I believe the conDateFormat was
> already taking care of the".
[quoted text clipped - 99 lines]
>> >> > what
>> >> > I can do to get this to work?

Reply to this Message