Crosstab Date Criteria needed 2

[knuckelhead]

the following sql is my crosstab query.


TRANSFORM Count(QryJobDetail.CusLocID) AS CountOfCusLocID
SELECT QryJobDetail.LocFullName, QryJobDetail.LotBlock, QryJobDetail.LocFullAddress
FROM QryJobDetail
WHERE (((QryJobDetail.JobStatus)="appt"))
GROUP BY QryJobDetail.LocFullName, QryJobDetail.LotBlock, QryJobDetail.LocFullAddress
PIVOT Format([JobDate],"Short Date");

i want to have on the criteria line or somewhere, like the following.

Between [Begin Date] and [Ending Date]

i get an error message.
thanks

 

[Remou]

I think PHV covers this in:
Crosstab error when using a [start date] criteria
thread701-1197749

 

[knuckelhead]

Remou. I see from that old thread that i had a similar question a few months ago. I now added the Parameters deal.

At first, this Xtab Qry date range deal appeared to work. i was asked the question of begin and end dates.

but then i tried a different range and i got the same answer every time. so my xtab is having trouble getting my date range. it seems to only listen to my APPT criteria only.

can you see my error somewhere??

a second question. i have this Qry inside a Form button. all my forms have both Popup and Modals set to YES. Since the below is a Qry, i do not see a chance to set Popup/Modals. The form button works but the answer is MINimized at the bottom of the tray. how do i fix this so that the Xtab Qry will appear nice and big? see button code below.

so that is 2 questions.

Question #1, the xtab Sql code below:

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Count(QryJobDetail.JobDate) AS CountOfJobDate
SELECT QryJobDetail.LocFullName, QryJobDetail.CusLocID, QryJobDetail.LocFullAddress, QryJobDetail.LotBlock, QryJobDetail.JobStatus
FROM QryJobDetail
WHERE (((QryJobDetail.JobStatus)="APPT"))
GROUP BY QryJobDetail.LocFullName, QryJobDetail.CusLocID, QryJobDetail.LocFullAddress, QryJobDetail.LotBlock, QryJobDetail.JobStatus
PIVOT Format([JobDate],"Short Date");


question #2, the button on frmJunction which seems affected by all my forms popup/modals YES:

Private Sub APPOINTMENTS_Click()
DoCmd.Maximize
DoCmd.OpenQuery "QryAppointmentsXtab"
End Sub


i moved the maximize down but it did no good either.
Knuclehead

 

[lespaul]

no where in this query are you applying the parameters in the query. You need to add the criteria as shown below:

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Count(QryJobDetail.JobDate) AS CountOfJobDate
SELECT QryJobDetail.LocFullName, QryJobDetail.CusLocID, QryJobDetail.LocFullAddress, QryJobDetail.LotBlock, QryJobDetail.JobStatus
FROM QryJobDetail
WHERE (((QryJobDetail.JobStatus)="APPT")) AND SomeDateField Between [start date] and [end date]
GROUP BY QryJobDetail.LocFullName, QryJobDetail.CusLocID, QryJobDetail.LocFullAddress, QryJobDetail.LotBlock, QryJobDetail.JobStatus
PIVOT Format([JobDate],"Short Date");

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Count(QryJobDetail.JobDate) AS CountOfJobDate
SELECT QryJobDetail.LocFullName, QryJobDetail.CusLocID, QryJobDetail.LocFullAddress, QryJobDetail.LotBlock, QryJobDetail.JobStatus
FROM QryJobDetail
[!]WHERE JobStatus='APPT' AND (JobDate Between [start date] And [end date])[/!]
GROUP BY QryJobDetail.LocFullName, QryJobDetail.CusLocID, QryJobDetail.LocFullAddress, QryJobDetail.LotBlock, QryJobDetail.JobStatus
PIVOT Format([JobDate],"Short Date");

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[knuckelhead]

I used the PHV method. it works wonders. Thanks so much to you fellows. i will donate a 2nd time today.
that solves Question #1.

Do you have any way to fix my 2nd problem.
when i am in my frmJunction, i hit a button that runs the Qry that you just fixed. my code is noted in an earlier message above.

i am using Popups/Modals on all my forms.

so when i run this Qry, it goes minimized to the tray below.
how can i make it appear like all my other Forms. meaning maximized screen. what am i doing wrong?

 

[knuckelhead]

i will make this latter question #2 as a new request. so consider this one filled.
knucklehead