Results 1 to 6 of 6
  1. #1
    AccessFreak is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2010
    Posts
    17

    Crosstab Query Prompting twice for data?

    I have created a crosstab query, in which I would like the user to be prompted for data (ie a "date" or "date" range) - this seems to be working ok, however, it is prompting twice for the date???? I have read about this online, and all I could find is that 1. the parameter in the parameter window must be the same as the "criteria" in the query. (this is so) 2. No sorting or "order by" options must be selected in the datasheet view - I have made sure that all of this is as I read, but still - it is prompting me twice for this date? Please help



    thank you kindly

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    whats the statement?

    I don't think I've ever used a parameter in a cross tab, at least not for financials.

    are you sure it's giving the 'exact' same parameter? It could be giving one because there is one specified by you, and other because there is an unrecognizable field name in the sql. Also a possibility to be the same field for both instances, but not sure. Have you check the params list in the dialog to make sure that only one is listed?

  3. #3
    AccessFreak is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2010
    Posts
    17
    this is the statement. When I first make the parameter changes in the query, the very first time i run the query, it is correct. but when i run the report based on that query, it prompts 3 times for date, and after entering date 3 times, it says, cannot find value "" - then report wont open. then the when i check the query again, it has automatically made a new column in design view for "DateCalled", with a "where" option selected:

    PARAMETERS [Enter a date:] DateTime;
    TRANSFORM Count(tblCalls.CallId) AS CountOfCallId
    SELECT tblCalls.CallDate, tblLeads.CompanyName, tblCalls.LeadId, Count(tblCalls.CallId) AS TotalCallid
    FROM tblLeads INNER JOIN tblCalls ON tblLeads.LeadId = tblCalls.LeadId
    WHERE (((tblCalls.CallDate)=[Enter a date:]))
    GROUP BY tblCalls.CallDate, tblLeads.CompanyName, tblCalls.LeadId
    PIVOT tblCalls.Result;

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    I didn't know you were using this as a base for a rpt as well. Do you know those most popups like that come from invalid fields on the object, either the form or report that's being chosen?

    The statement looks OK to me. Why not check the rpt for any little green triangles? if you don't know what I mean, that is the default indicator for invalid fields in design view.

  5. #5
    AccessFreak is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2010
    Posts
    17

    Crosstab query prompting twice

    I have checked and double checked the field names. all are correct. and there are no green triangles.

    The prompting for date thing, as well as other data criteria works perfectly in a select query. is there no way of somehow rather using a select query, but then still being able to total how many "Yes " "No" and "N/A" results each client has per call?

    The report just needs to be able to show the totals of each result (yes, no, n/a) per day, week, and monthly report, and then I need to total the "YES', and the "NO" totals, to give a "CallsPitched' total, and then use that to calculate the "ClosingRatio"

    Any help would be greatly appreciated as I am running on a tight schedule for the completion of this db

    thank you so much to all who have assisted thus far

    Regards

    AccessFreak

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    well mr. freak,

    Noone else has responded here. I doubt I can help you get it all done via the posts, but if you want to post a sample of your table up here, and also:

    • all the calculations you want, if not mentioned already
    • where those calcs should be, either in the qry or rpt, or whether it doesn't matter for each


    I can help you out. It'll will take much less time to show you than to explain it away.

    Another thing...the timeline and tight schedule you mention doesn't mean a thing to me. If there was a $100 check in the mail, it might. That is worth saying because reading that is kind of deterring. Makes me thing I'm part of the project, which of course I'm not.

    But at any rate, that's good to know, so I mention. Post what you've got and I'll show you what I have meant in the last two posts, or at least what I've been thinking.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query prompting for values?? Why?
    By Schon731 in forum Queries
    Replies: 2
    Last Post: 12-23-2010, 01:19 PM
  2. Replies: 1
    Last Post: 07-30-2010, 10:28 AM
  3. Replies: 3
    Last Post: 05-13-2010, 08:18 PM
  4. Replies: 0
    Last Post: 03-31-2009, 02:05 AM
  5. Replies: 0
    Last Post: 04-26-2008, 09:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums