Access Crosstab Headers - Properties

I have created an Access crosstab query - where the column headers are month/year. In properties - column headings - I have manually entered the order I want mm/yyyy to appear since this is a text field.

The intent is to make this automated, so someone other than me can use this as well.

Therefore, in column headings I have entered values that may not be on the current crosstab output, but are included for future reports.

Unfortunately, the empty future columns appear as well.

Is there a way to limit the columns to those that have matching data values (and not all the column header values) when running the crosstab query output.

Thank you.
exp vgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

exp vgAuthor Commented:
I have also tried placing "Is Not Null" in the criteria without success.

Thank you.
exp vgAuthor Commented:
I have also tried to create an extra column for this text date, and indicate Where is Not Null
Rey Obrero (Capricorn1)Commented:
open the crosstab query in SQL view

change this part

Pivot [fieldname]


Pivot [fieldname] In("01/2015","02/2015","03/2015","04/2015","05/2015","06/2015","07/2015")
...and so on

if the format of your date field is not like this "01/2015", change it accordingly..
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
The only way I've been able to do this is through code.  Create a procedure that creates a distinct list of the values that should fall in the range you desire.

Your main query will look something like:

TRANSFORM Sum([SomeField]) as Expr1
SELECT RowHeaderField
FROM yourTable
PIVOT Format([DateField], "mm/yyyy")

What you need to do is append an IN clause to that SQL so that it looks like:

TRANSFORM Sum([SomeField]) as Expr1
SELECT RowHeaderField
FROM yourTable
WHERE [DateField] BETWEEN [StartDate] and [EndDate]
PIVOT Format([DateField], "mm/yyyy") IN ("01/2015", "02/2015", ...")

So, to create the list for the IN clause, you need to do something like:
Public Sub XTabColumnHeadings(StartDate as Date, EndDate as Date)

    Dim strSQL as string, strIn as string 
    Dim db as DAO.Database
    Dim rs as DAO.Recordset
    Dim qdf as DAO.Querydef
    strSQL = "SELECT Format([DateField], 'mm/yyyy') as ColHeads, Min([DateField]) as FirstDate " _
                 & "FROM yourTable " &
                 & "WHERE [DateField] BETWEEN #" & StartDate & "# AND #" & EndDate & "# "
                 & "GROUP BY Format([DateField], 'mm/yyyy')
                 & "ORDER BY Min([DateField])"
    set db = currentdb
    set rs = db.openrecordset(strsql, , dbfailonerror)

    While not rs.eof
        strIn = "," & chr$(34) & rs!ColHead & chr$(34)
    set rs = nothing

    strIn = Mid(strIn, 2)   'removes the leading comma

    set qdf = db.querydefs("YourQueryName")
    if instrrev(qdf.SQL, "In (") = 0 Then  
         strSQL = qdf.SQL
        strSQL = LEFT(qdf.SQL, instrrev(qdf.SQL, "In (" ) - 1)

    qdf.SQL = strSQL & " In (" & strIn & ")"
    set qdf = nothing

End Sub

Open in new window

You would then call this code immediately prior to running the query.  

However, this presents another problem (another question perhaps), in that the columns in your report will not have the correct ControlSource, so you will have to add code to the report which will change the control source, and probably the caption of each of the columns in the report that will vary as a result of the Crosstab.  You will also have to set the visible property of the columns that are in the report, but for which there is no data so that they don't display.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
exp vgAuthor Commented:
I tried the pivot code without success, with the dates formatted.
exp vgAuthor Commented:
This may be a very creative approach - but is there a way to attach a character/letter to the text date field, but not make this visible in output?

This would most likely solve all problems.
Dale FyeOwner, Developing Solutions LLCCommented:
What wasn't successful?

did you put break-points in the code to determine what was going on at each step and to determine the values of the various products as the code executed?


Without seeing your query SQL and the values of these intermediate steps, I really cannot help much further.  If you could post a sample database with limited (sanitized data), this query, the code I provided and the form or other method you are using to open the CrossTab, it would be significantly easier.
exp vgAuthor Commented:
I hope this helps - here is the SQL code:

TRANSFORM Count([ENC4_with EMP CLIENT_Structure].Encounter) AS CountOfEncounter
SELECT [ENC4_with EMP CLIENT_Structure].[Loc Name]
FROM [ENC4_with EMP CLIENT_Structure]
WHERE ((([ENC4_with EMP CLIENT_Structure].Encounter) Is Not Null))
GROUP BY [ENC4_with EMP CLIENT_Structure].[Loc Name]
PIVOT [ENC4_with EMP CLIENT_Structure].Date;
exp vgAuthor Commented:
This was the original before modification
exp vgAuthor Commented:
Please find the de-sensitized file attached.
John TsioumprisSoftware & Systems EngineerCommented:
a small sample depicting the problem would be most helpful
exp vgAuthor Commented:
Thank you everyone.
Dale FyeOwner, Developing Solutions LLCCommented:
I assume, that since you awarded points, you were able to get it to work.
exp vgAuthor Commented:
I am trying a different approach - I format the month year field as text, then I separate out this field to make each of these a month and a year in numeric, sort. The date was column field, but to do this had to make it my row field.

I am now working on transposing this table - may be posting soon if I cannot figure out this last step.

Thanks again for your help.
Dale FyeOwner, Developing Solutions LLCCommented:
OK, but if you want a working example, take a look at the attached database.

This uses a form to select start and end dates for the query.  The button checks to make sure that you have values selected in the two combos, and that the end date is greater than the start date.

It then calls the function I posted above, with several modifications to generate the In (    ) clause, and then opens the query.
exp vgAuthor Commented:
Awesome - thanks so much - I will definitely have a look.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.