accessaspire219
Registered User.
- Local time
- Today, 16:02
- Joined
- Jan 16, 2009
- Messages
- 126
I have a form in which the user enters a StartDate and EndDate which determines the range of data he wants to view. I want to grab just the year (YYYY) part of the EndDate and use it as a parameter result for the YTD. i.e. if StartDate=01/2009 EndDate=08/2009 I want my query to read the EndDate that the user entered in the form and grab just the year part and use it as a criteria in the year-to-date query.
Here is what I have so far, but it doesn't work
Does anyone know what I could be doing wrong? or what could be done to make it work?
Here is what I have so far, but it doesn't work
Code:
SELECT SUPERVISOR, NAME, SCALENAME, SumOfINVDOLR, SumOfCONSDOLR, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX
FROM MIOH_MONTH
WHERE YTD = DatePart("YYYY",[Forms].[Form1]![StartDate])&" YTD"
UNION ALL
SELECT SUPERVISOR, NAME, SCALENAME, SumOfSALES, SumOfBOS, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX
FROM SERVICE_MONTH
WHERE YTD = DatePart("YYYY",[Forms].[Form1]![StartDate])&" YTD"
UNION ALL
SELECT SUPERVISOR, NAME, SCALENAME, SumOfSURPDOLL, DUMMY, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX
FROM SURPLUS_MONTH
WHERE YTD = DatePart("YYYY",[Forms].[Form1]![StartDate])&" YTD"
UNION ALL SELECT SUPERVISOR, NAME, SCALENAME, SumOfCHARGES, SumOfCONSDOLR, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX
FROM TRFALL_MONTH
WHERE YTD = DatePart("YYYY",[Forms].[Form1]![StartDate]&" YTD";