|RE: Crosstab query by date range
Group: microsoft.public.access.queries · Group Profile
Author: Jerry Whittle Date: Sep 22, 2008 22:38
Try building a select query with the parameters in it first. Then base the
crosstab on this initial query.
Or build a form with two text boxes and a button to run the crosstab query.
Put the dates in the text boxes and reference them in the criteria of the
query something like:
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> follow up - i got rid of the message but still have to enter the beginning
> and end dates 3 times ... other than that it works ...
> getting closer still
> thank you
> "Jerry Whittle" wrote:
>> Later versions of Access require that the parameters be defined when used in
>> a crosstab query. While in design view go up to Query, Parameters and type in
>> [Enter Beginning Date Range] exactly as it's in the criteria including the
>> . Make the data type Date/time.
>> Actually it's better to make a plain old select query first with the columns
>> and criteria that you want (including parameters). Then create a crosstab
>> query off of this select query.
>> In either case you need to define the data type of the parameters.
>> Jerry Whittle, Microsoft Access MVP
>> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>> "joseph" wrote:
>>> Good afternoon everyone,
>>> I was hoping someone could provide me some assistance. I have the following
>>> crosstab query:
>>> TRANSFORM Count(tblDefendants.[Defendant ID Number]) AS
>>> SELECT tblDefendants.[Date Received by DOS], Count(tblDefendants.[Defendant
>>> ID Number]) AS [Total Of Defendant ID Number]
>>> FROM tblDefendants
>>> GROUP BY tblDefendants.[Date Received by DOS]
>>> ORDER BY tblDefendants.[Date Received by DOS] DESC
>>> PIVOT tblDefendants.[Defendant ID Number];
>>> Which provides me with a total number of waivers received by date. Which is
>>> great, however, once this database is completely constructed there will be
>>> 10k records over 10+ years which would involve alot of scrolling to narrow
>>> down specific dates to be reviewed. I would like to be able to prompt the
>>> user for a date range that will produce results in the exact format as listed
>>> above. If i attempt to use the Where statement in the query I get the message
>>> that reads "The Microsoft Office Access database engine does not recognize
>>> '[Enter Beginning Date Range]' as a valid field name or expression ...
>>> I tried to run a crosstab query that was based on a date range query and got
>>> the same message when I tried to run it ...
>>> I'm looking for results that appear as follows: User clicks action button to
>>> search waivers received by date range, enters date range of 1/15/1999 through
>>> Date Received Waivers Received
>>> 1/15/1999 15
>>> 1/16/1999 12
>>> Total Received
>>> for Date Range Selected 27
>>> The best i've come up w/ so far is getting a report in that format for my
>>> entire database, can't get it to allow me to only report a user-defined date
>>> Any help would be most appreciated
>>> I love this place ... I found some similar scenarios in previous posts but
>>> the extent of those has got me this far
>>> thank you again