Re: Crosstab query and report

From: Thomas Kroljic (tkroljic_at_covad.net)
Date: 02/27/04 

Date: Thu, 26 Feb 2004 23:30:03 -0500

Duane,
     I download your example database called CrossTab.mdb.
I noticed several things that are different:

        1. the use of a initial query to rebuild the data table
(qappEmpCust) using the start and end date from your
frmReportSelect form.
        2. In your query (qxtbEmpCustSales) I notice the use of the where
clause. What I found surprising was the absent of a parameter definition via
Query/Parameter and no "parameter" syntax within the SQL statement itself.
        Why doesn't this query need to define the parameters? If I remove
the parameter statement from my query I get an Microsoft error message "The
MS Jet database engine does not recognize
'[forms]![frmReportSelect]![txtstartdate]' as a valid field name or
expression.

        I think I've had enough headaches with this query. Tomorrow I plan
on doing the following to accomplish my end results: first, I'll execute a
query that creates a table with the appropriate data based on the start and
end dates from a user input form. Next, I'll create a general crosstab query
against this table that will be used by my original report (the one out of
the "Access 2000 Developers Handbook).

    I still can't comprehend why your query doesn't need a parameter
statement and mine does. Any thoughts on this?

    Again, thank you for all your help and input. I do appreciate it. And I
do eventually learn from my mistakes :)
And I do learn from examples such as CrossTab.mdb

Thank you,
Thomas J. Kroljic

"Duane Hookom" <duanehookom@nospamhotmail.com> wrote in message
news:eeqRjeL$DHA.2180@TK2MSFTNGP09.phx.gbl...
> No offense to Getz, Litwin, and Gilbert but I don't like their solution. I
> created and use the much more flexible (less code) solution for crosstab
> reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.
>
> --
> Duane Hookom
> Microsoft Access MVP
> Please direct any questions to News Groups
>
>
> "Thomas Kroljic" <tkroljic@covad.net> wrote in message
> news:4c84c$403e397d$44a671ea$6470@msgid.meganewsservers.com...
> > Duane,
> > First off, thanks for taking the time to help me out. I do
appreciate
> > it.
> >
> > I have a book called "Access 2000 Developers handbook Vol 1" by Ken
> > Getz, Paul Litwin and Mike Gilbert. This is my bible.
> >
> > In chapter 9 there is an example of a crosstab report. This is
the
> > report I am using. It also contains some VBA code which I do understand.
> >
> > I've copied (imported) his report from the CD that came with the
> > book into my database. I added four additional labels, text, and sum
text
> > fields to the report. I then added my query see below:
> >
> > PARAMETERS [forms]![myform]![beginningcalldate]
DateTime;
> > TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
> > SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS
> > totalcount
> > FROM Calls INNER JOIN Store ON Calls.StoreID =
> Store.StoreId
> > ***** WHERE
(((Calls.CallDate)>[forms]![myform]![beginningcalldate]))
> > GROUP BY Store.StoreDesc
> > ORDER BY Store.StoreDesc
> > PIVOT Calls.CallCodeId;
> >
> >
> > Duane, If I remove the where clause, the report runs great. If I include
> the
> > where clause (and I added the parameter definition to the query), I get
an
> > empty report. No data. If I use the debugger to follow the code this is
> what
> > I noticed. At the time the VBA code assign the record source to a new
rst
> > object (see code segment below) , when the code his the line
"intColCount
> =
> > rst.Fields.Count", the value is 0. Again, this works perfectly if I take
> out
> > the where clause. At this point, I'm lost. I've search google looking
for
> > clues. I think I have my blinders on because I can not see what is
causing
> > this problem. Hopefully this data will help.
> >
> > rst.Open _
> > Source:=Me.RecordSource, _
> > ActiveConnection:=CurrentProject.Connection, _
> > Options:=adCmdTable
> >
> > intColCount = rst.Fields.Count
> > intControlCount = Me.Detail.Controls.Count
> >
> > Thank you,
> > Thomas Kroljic
> >
> >
> > "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
> > news:egDfhxC$DHA.392@TK2MSFTNGP12.phx.gbl...
> > > Printing a report based on queries may cause the report to "requery"
> thus
> > > asking for the same values again. That is one of the reasons that I
> always
> > > get values from controls on forms. This has always worked for me.
> > >
> > > Did you set the column headings property?
> > >
> > > --
> > > Duane Hookom
> > > MS Access MVP
> > >
> > >
> > > "Thomas Kroljic" <tkroljic@covad.net> wrote in message
> > > news:12daf$403d8168$44a671ea$31236@msgid.meganewsservers.com...
> > > > Duane,
> > > > I've tried several times to use a form with controls the user
can
> > > > fill-in before running the report with the crosstab query, but I get
> an
> > > > error message stating the forms!myform!txt is not a field or
> expression.
> > > > I have the correct parameters defined in the query.
> > > >
> > > > Again, all works fine when I open the query from the list of
queries.
> > It's
> > > > when I open the report and try to run the report that I have
problems.
> > > >
> > > > Even if I stay with parameters in the query, why would the system
> prompt
> > > me
> > > > double the amount of parameters that I've have listed. It's as if
> during
> > > > each phase of the report opening up I get prompted for all
parameters.
> > > What
> > > > would cause this?
> > > >
> > > > Thanks for your input and help,
> > > > Thomas J. Kroljic
> > > >
> > > > "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
> > > > news:Og5p1QB$DHA.4012@tk2msftngp13.phx.gbl...
> > > > > I would get the criteria from controls on a form rather than
> parameter
> > > > > prompts. ie:
> > > > > Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtEndDate
> > > > > Make sure you update your query parameters. If you have a static
set
> > of
> > > > > CallCodeIDs then you should enter them into the Column Headings
> > > property.
> > > > >
> > > > > --
> > > > > Duane Hookom
> > > > > MS Access MVP
> > > > >
> > > > >
> > > > > "Thomas Kroljic" <tkroljic@covad.net> wrote in message
> > > > > news:c5a58$403d20db$44a671ea$23152@msgid.meganewsservers.com...
> > > > > > All,
> > > > > > I have a crosstab query that works OK when I manually run
it.
> > Here
> > > > is
> > > > > > the code:
> > > > > >
> > > > > > PARAMETERS [Enter Starting Date] DateTime, [Enter Ending
Date]
> > > > > DateTime;
> > > > > > TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
> > > > > > SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS
TotalCalls
> > > > > > FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
> > > > > > WHERE (((Calls.CallDate) Between [Enter Starting Date] And
> [Enter
> > > > > Ending
> > > > > > Date]))
> > > > > > GROUP BY Store.StoreDesc
> > > > > > ORDER BY Store.StoreDesc
> > > > > > PIVOT Calls.CallCodeId;
> > > > > >
> > > > > > My problem come in when I assign this query as the record
> source
> > > to
> > > > a
> > > > > > report. First off, I get prompted
> > > > > > four time for the input fields instead of just two. Then I get
> this
> > > > > message
> > > > > > from Access 2002:
> > > > > >
> > > > > > "The Microsoft Jet database engine does not recognize " as a
valid
> > > field
> > > > > > name or expression"
> > > > > >
> > > > > > Again, if I run the query manually, it prompts me for two input
> > values
> > > > and
> > > > > > display the correct data, but when I
> > > > > > try to assign it to a new report, I get errors.
> > > > > >
> > > > > > Any suggestions as to what I might be doing wrong or missing?
> > > > > >
> > > > > > Thank you,
> > > > > > Thomas Kroljic
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Creating a directory
    ... I tried to adapt this code to my database. ... Allen Browne - Microsoft MVP. ... In another report I use the On Format event for the Detail Section to trigger an option to add a border to a field's value "if IsNull" is true or no border if false. ... I tried something similar but there were no events to trigger the VBA code I want to use which would open the second table and find a record/name. ...
    (microsoft.public.access.reports)
  • Re: Import Form or Report, and Autoatically Delete the old Form or Rep
    ... in Help file to get you started on how the VBA code would be set up to do ... I have my database split into front end and backend, ... new forms or report as they arise with their requirements. ... existing forms, queries etc that has the same name, and then leave it good ...
    (microsoft.public.access.externaldata)
  • RE: Export to Snapshot with parameters
    ... "Dale Fye" wrote: ... was passed thru WHERE clause, wich worked for previewing and printing to MS ... I run the report by either of 2 buttons with acPreview and acNormal VBA ... How can I fix the Macro or maybe put VBA code instead to export the report ...
    (microsoft.public.access.gettingstarted)
  • What is Date/Time format in SQL string statements?
    ... I am trying to search my access database by date, to determine who has first aid credentials and what date they received them. ... I am building an SQL query string in VBA code, and can't figure out the format to use for date searching. ... When I open a report from within VBA, the where clause contains the following: ... And it returns no results in the report even though somebody in the db table has the date 12/12/2004. ...
    (microsoft.public.access.queries)
  • Re: How can I send a form through e-mail
    ... I have created a report ... >>be filled out by employees. ... > displaying data from within the database. ... > VBA code to parse the *text* of an EMail message and enter it into ...
    (microsoft.public.access.forms)