Crosstab Date Parameters - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Crosstab Date Parameters

Debra Triolo

3 years ago

I created a crosstab report and am trying to limit the dates reported to a specific range. When I run the report it gives me the range, but then throws in a random day outside the range. For instance, my syntax is Between #06/15/2023# AND #06/23/2023# and it gives me those days, but prefaces them with a column containing the data for 06/02/2023 and the totals column (out of requested order), before the requested days are listed. I really did not want to even use a crosstab report, but for the information I need, if I can get it to report correctly, it would work best.

	Kevin Yip @Reply 3 years ago
The criteria should be inside the record source (i.e. the crosstab query) of the report. You can only filter columns inside a crosstab query, and nowhere else. The "WHERE" condition in a query can only filter rows -- except in a crosstab query.

Debra Triolo OP @Reply

3 years ago

So then how can I produce this report with the proper date range in the proper order? If its just a week's worth of dates its no big deal to go back in and manually move columns, but if its several months of data broken down by day, its very time consuming to manually move the column for 6/2 (as an example) so it appears between 6/1 and 6/3, rather than where it currently resides, between 4/28 and 4/29.

	Debra Triolo OP @Reply 3 years ago
Is it because I have the date listed as column headers rather than row headers?

	Kevin Robertson @Reply 3 years ago
Can you post some screenshots please?

Kevin Yip @Reply

3 years ago

Hi Debra, how is your report set up for the crosstab query? A crosstab query creates "dynamic" columns, meaning the column names and column count may be different every time you run the query. Can your report accommodate that kind of changes? If it can't, that may be why you see these mysterious columns in your report. Yes, a few screenshots showing how your report is set up would help.

Someone else asked about reports and crosstab queries on another forum thread, and I remember giving him a tricky VBA workaround. When I find that thread, I'll mention it here.

	Kevin Robertson @Reply 3 years ago
Kevin, is this the thread you mean? Crosstab as a report source

	Kevin Yip @Reply 3 years ago
Thanks, that's the one.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View