Query filter question (attempting crosstab query)

[joave]

Hi:

I am attempting to filter out the month of a query but don't seem to be
having any luck. I can successfully use a date range but want to just enter
the month and year. Is this possible? I built a crosstab query but it comes
up empty:

TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
WHERE (("mmm"="Jul" AND "yyyy" = 2003)) 'doesn't matter what I put here or
how I put it; it still comes up empty
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm");

Thank you in advance for your help.

 

[Duane Hookom]

Try:
TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
WHERE Month(CheckOutDate)=7 AND Year(CheckOutDate) = 2003
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm");

OR
TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm") IN ("2003 JUL");

OR
TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
WHERE Format(CheckOutDate,"yyyymm")="200307"
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm");

 

[joave]

WOW thank you sooo much for this in-depth response!! You are the MAN!

Dave

Try:
TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
WHERE Month(CheckOutDate)=7 AND Year(CheckOutDate) = 2003
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm");

OR
TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm") IN ("2003 JUL");

OR
TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
WHERE Format(CheckOutDate,"yyyymm")="200307"
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm");


--
Duane Hookom
Microsoft Access MVP

Hi:

I am attempting to filter out the month of a query but don't seem to be
having any luck. I can successfully use a date range but want to just enter
the month and year. Is this possible? I built a crosstab query but it comes
up empty:

TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
WHERE (("mmm"="Jul" AND "yyyy" = 2003)) 'doesn't matter what I put here or
how I put it; it still comes up empty
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm");

Thank you in advance for your help.