Can user define date criteria when running Crosstab query?

[bmurch71]

I have a crosstab query which tabulates sales data by date and by department (department name is Row Header, date is column header), and is turn based on a query that selects the date range. In the select query, under the Date column, my criteria is set to "between [enter start date:] and [enter end date:], which prompts the user at runtime for the date range. Once I try and run the crosstab query, however, I get an error: "The Microsoft Jet database engine does not recognize '[enter start date:]' as a valid field name or expression." I've run into this before, and just cheated by manually changing the date range in the select query at each runtime, but there's got to be a better way! As you might have guessed, I'm not a VB guy (yet! but I'm working on it!), so I'm hoping that someone has a great solution or even a simple fix that I just haven't hit upon yet. Thanks in advance for any suggestions.
Brian

 

[bmurch71]

Re: Can user define date criteria when running Crosstab quer

Found the answer from Andrew93 in this post. It pays to search first - thanks for your help in absentia Andrew!
Brian

 

[bmurch71]

Re: Can user define date criteria when running Crosstab quer

. . . but now I have a follow up! I edited the parameters section in the design view of my crosstab query to show that "enter start date:" and "enter end date:" were Date/Time fields (I first used integer as the data type, as Andrew did, but that didn't work for the values that I enter as dates), and I was able to run the cross-tab query correctly and get the results I expected. However, I now want to create a report based on this cross-tab query, and I'm having problems. If I choose report wizard and select my crosstab, no field display in the wizard to add to my report. If I start from scratch, each time I try to display the field list, or add a field to my report, I get the "enter start date:" and "enter end date:" prompts. When I try and run the report, I get a "Microsoft Jet database engine does not recognize " as a valid field name" error message. If I backtrack and just manually enter my date ranges and remove the entries from the parameters of the crosstab query, it will run just fine - but again, that makes it too complicated for my users. Any ideas? The crosstab format is ideal for this report - it shows sales projections per department per day for one week at a time, so I have the days of the week as column headers and dept's as row headers.

On another note, I'd love to display on my report the dates for which the report is run, but as I had to format my dates on the crosstab query as "ddd" (for day of the week), the report doesn't see the actual date field from the query - it sees the column names. How can I also show the dates for which the report is run?

Man, I feel like I'm making this waaaay complicated, and so close to Christmas too! I hope everyone has a happy holiday season, and thanks for any advice.
Sincerely,
Brian

 

[bmurch71]

Re: Can user define date criteria when running Crosstab quer

I'm talking to myself, but perhaps that's a good thing. A word to the wise: when in doubt, look through Andrew93's posts! I think the problems with the wizard were arising because I kept going back and forth, modifying query structures, and probably trying to do too many things in one query. I went back and simplified it all a bit, and have been able to get it to work. I also found the answer to my question about displaying the date range in another Andrew post - I just had to add an unbound text box to the report header with [enter start date:], and voila! There is my date range. Even when I'm writing to no one but me, I sure am glad this place exists!
Brian

 

[bmurch71]

Re: Can user define date criteria when running Crosstab quer

ok, this is it, and then I leave for a little vacation: Access is weird. I created my nice shiny report based on that crosstab query, and then I exported the report and queries to another workstation. Oddly enough, when i exported the report, i was prompted to enter the dates for my crosstab query (enter start date:, enter end date. And when I then tried to open the report on the new workstation, every time I ran it, it prompted me to enter the date parameters twice! What the . . . ? I fiddled with the crosstab query, deleting and then re-creating the settings in the parameters window, and then fiddled with the report, deleting my unboind text boxes that have the same text as the date parameters from the crosstab query. Each time I made a change to the report and tried to save it, i was prompted for the date parameters again. After I had pretty much given up, I ran the report once more - and it only asked for the date parameters once! So I guess it's fixed, but how weird is that? Anyone seen that sort of behavior before?
Brian

 

[mdmilner]

Re: Can user define date criteria when running Crosstab quer

It may have something to do with Access not identifying your fieldnames as such - you might be using reserved words or it's losing stuff.

Try putting [] brackets around fieldnames.

That's as close as I can guess without actually seeing your SQL

Mike

 

[Andrew Fergus]

Re: Can user define date criteria when running Crosstab quer

Hi Brian and thanks for your positive comments.

I went back and read that post and I became very confused very fast! So you did well to apply it to your situation.

thanks for your help in absentia Andrew!

I'm not really in absentia - I am on holiday in the UK at the moment and don't have MS Access on this PC (nor my uesful links) so I am unable to make any meaningful contributions. Until I return to NZ I might just 'lurk' in the Access forum. In answer to your question about Access mis-behaving, I too have seen that on occasion and usually rebuilding the query or report fixes the problem.

Thanks again
Andrew

 

[bmurch71]

Re: Can user define date criteria when running Crosstab quer

Hey Andrew - I didn't mean "in absentia" in a literal sense, I just sort of was goofing on myself for not searching a little more diligently for the answer to my question! I hope your holiday in the UK is/was nice - I've been a few times, mostly to London but have also spent time in Cambridge, York, and up north in Scotland. What a great place. Anyway, glad to know I'm not crazy when it comes to Access acting weird - I tend to get over-reliant on it because of all the cool things it does, so when it acts nutty I tend to think it's something I've done.

Happy Holidays!
Brian