Cross tab query not recognising field name.

[swell]

Added a report and massaged the code.
If it can't work directly in a Xtab query is it possible to select the date range with one query and then apply the Xtab query to the result?

 

[swell]

I have managed to fix the date range problem, made a query to create an interim table of results from the date range and use that data in the Xtab query. The report on a full years data (last year) now displays correctly.
BUT if I only use a partial year for the data I get an error which I suspect comes from the report, the Xtab query uses (DatePart("m",[Date of Activity]))
and the report takes the numbers generated by the Xtab query (which are now sorted in a numerical chronological order on the month) , the first month that there is no data for access produces an error like.

If there is no Data for April the error is does not recognize "[4]" as a valid field name.
I am unsure what would happen if there was a years data with no data in some months?

 

[isladogs]

Hi
Just spotted this.
I probably need to read the whole thread again but to save me time it would help if you explained which query and report you are referring to.
To fix the issue where there is no data for certain months, use fixed column headers in the crosstab query property sheet.
In this case, probably either "Jan","Feb", "Mar" etc or possibly 1,2.3 etc.

For more info, see http://allenbrowne.com/ser-67.html

Also, you may find my Incident Analysis example app useful if you haven't already looked at it. It has a similar date range form to yours.
See http://www.mendipdatasystems.co.uk/multiple-group-filter/4594454290. Scroll down the page to find that example.

 

[swell]

To fix the issue where there is no data for certain months, use fixed column headers in the crosstab query property sheet.
In this case, probably either "Jan","Feb", "Mar" etc or possibly 1,2.3 etc.

I thought that I was achieving this with

(DatePart("m",[Date of Activity]))

The report has the Month abbreviation that corresponds with the numerical representation.
I wanted the month name displayed whether there was data for the column or not.
I will study the suggestions in the pointers you have given.

As I mentioned I have now created a query that produces an interim table that gives a date range for the Xtab query with which to work .
This query is "qryActivitybyDateRange" and produces table "ActivityRange".
the Xtab query is "xtbqryAnnualMonthlyKmsHrs" the form that actions this is "frmGetDates" and the report name is "rptAnnualMonthlyDistance" it is action from the Button labelled " Annual Monthly KM's".

 

[isladogs]

Crosstab queries only produce data where it exists.
The purpose of using fixed column headings is indeed to ensure all month names are included whether data exists or not.

The monthly distance report is fine for last year's data but fails for all others as you haven't specified the column headers in the crosstab.
I've now added these for you in both crosstabs and the monthly distance report now works for all date ranges.

The monthly hours report has other issues with both the crosstab itself and incorrect control names in the report detail and footer sections.
I've left this for you to sort out.

Please look at my incident analysis example app

 

[swell]

Crosstab queries only produce data where it exists.
The purpose of using fixed column headings is indeed to ensure all month names are included whether data exists or not.

The monthly distance report is fine for last year's data but fails for all others as you haven't specified the column headers in the crosstab.
I've now added these for you in both crosstabs and the monthly distance report now works for all date ranges.

The monthly hours report has other issues with both the crosstab itself and incorrect control names in the report detail and footer sections.
I've left this for you to sort out.

Please look at my incident analysis example app

Thank you for that, I knew Monthly hours report had incorrect parameters, was waiting till the Monthly distance was working then going to correct the Xtab queries to reflect those changes.
I have, since looking at the changes, fixed the other Xtab queries. Initially I did have the Xtab with the pivot as you have. With all the changes I was doing and not being good with SQL it obviously got eliminated, my bad.

I have been looking at your App, at first glance it doesn't look complicated but the more I looked the more complex it is. It will take me some time to understand everything.
I find the more I learn the less I know.
I bought a book off the net in the hope of gaining a bit more knowledge but it turned out to be nearly 20 years out of date and not very much info anyway.
Do you have a suggestion for a good book?
Once again thank you very much for your assistance.
I will now go ahead and tidy all my code.

 

[isladogs]

My app is designed to be simple for the end user but behind the scenes it is indeed complex.
It shows how to build up SQL strings for various filter criteria which I do 'step by step' when developing.

For info, I tend to use dynamic column headings in reports based on crosstab queries though there aren't any in that example
If interested, there is an example report showing that approach in this example app: http://www.mendipdatasystems.co.uk/extended-file-properties/4594398115

The best books will depend on your level of knowledge
Many people really like the Access Inside Out books (for various versions of Access- I have the 2013 book but rarely use it.
My top pick is the two volume set Access 2000 Developers Handbook (a 2002 version also exists)
Although 20 years old, most of the code is still applicable though the user interface is obviously now very different
Both of those books are long since out of print but available secondhand online at a low price .... but these are high level books

If you like learning from videos, I recommend the set of Access 2013 videos by Steve Bishop. Over 100 in all, totally free and covering everything from beginners through to advanced

 

[swell]

The best books will depend on your level of knowledge
Many people really like the Access Inside Out books (for various versions of Access- I have the 2013 book but rarely use it.
My top pick is the two volume set Access 2000 Developers Handbook (a 2002 version also exists)
Although 20 years old, most of the code is still applicable though the user interface is obviously now very different
Both of those books are long since out of print but available secondhand online at a low price .... but these are high level books

If you like learning from videos, I recommend the set of Access 2013 videos by Steve Bishop. Over 100 in all, totally free and covering everything from beginners through to advanced

I personally prefer paper books for learning. I have watched a couple of Steve Bishop's videos I find he is a good presenter.
Regards