CODEWITHSUNDEEP
sqlms-access
Nik
Nik

Reputation: 41

Date Range in an Access Crosstab

Trying to get a Crosstab to bring up a prompt when a query is opened to allow a date range (start and end) to be input (dd-mm-yyyy) so that only this data comes back when the query is ran.

Currently sitting on the following code;

TRANSFORM Count(AlphaData.[Invoice]) AS CountOfInvoice
SELECT AlphaData.[Reason], Count(AlphaData.[Invoice]) AS [Total Of Invoice]
FROM AlphaData
WHERE ((AlphaData.[DateRaised]) Between AlphaData.[DateRaised] And AlphaData.[DateRaised])
GROUP BY AlphaData.[Reason]
PIVOT Format([DateRaised],"Short Date");

But cannot for the life of me get around the "MS Access DB engine does not recognise 'Alphadata.[DateRaised:]' as a valid field name or expression" issue.

The "WHERE" portion of the query does work in other queries, but it just goes to pot when it's applied in a crosstab.

Any suggestions?

Upvotes: 0

Views: 2032

Answers (2)

Gustav
Gustav

Reputation: 55831

It seems a bit mixed up. How about:

PARAMETERS
    [From Date:] DateTime,
    [To Date:] DateTime;
TRANSFORM 
    Count(*) AS CountOfInvoice
SELECT 
    AlphaData.[Reason], 
    Sum(AlphaData.[Invoice]) AS [Total Of Invoice]
FROM 
    AlphaData
WHERE 
    AlphaData.[DateRaised] Between [From Date:] And [To Date:]
GROUP BY 
    AlphaData.[Reason]
PIVOT 
    Format([DateRaised],"Short Date");

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19767

You need to add the parameters to the query: 

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Count(AlphaData.Invoice) AS CountOfInvoice
SELECT AlphaData.Reason, Count(AlphaData.Invoice) AS [Total Of Invoice]
FROM AlphaData
WHERE (((AlphaData.DateRaised) Between [Start Date] And [End Date]))
GROUP BY AlphaData.Reason
PIVOT Format([DateRaised],"Short Date");

(note: parameter added as the first line, and then used in the Between statement).

If you're using the graphical interface you need to look for the Parameter option:
enter image description here

and enter your parameters in the dialog box:
enter image description here

I didn't realise this would happen with a cross-tab as you can just type the parameter in for a select query: 

SELECT Invoice, Reason, DateRaised
FROM AlphaData
WHERE DateRaised=[Start Date]

Upvotes: 1

Related Questions