Grabbing the year from a date entered as a parameter

[accessaspire219]

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

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";

Does anyone know what I could be doing wrong? or what could be done to make it work?

 

[SimonB1978]

Hi,

Here is what I would do:

Place an input mask on [Forms].[Form1]![StartDate] to make sure the date is entered in the expected format (mm/yyyy).

Then in your SQL:
...
WHERE YTD = Right([Forms].[Form1]![StartDate], 4) & " YTD"
...

HTH

Simon B.

 

[accessaspire219]

Here is what I have

SELECT SUPERVISOR, NAME, SCALENAME, SumOfINVDOLR, SumOfCONSDOLR, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX 
FROM MIOH_MONTH
WHERE YTD = Right([Forms].[Form1]![EndDate], 4) & " YTD"
UNION ALL
SELECT SUPERVISOR, NAME, SCALENAME,  SumOfSALES, SumOfBOS, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX
FROM SERVICE_MONTH
WHERE YTD = Right([Forms].[Form1]![EndDate], 4) & " YTD"
UNION ALL
SELECT SUPERVISOR, NAME, SCALENAME, SumOfSURPDOLL, DUMMY, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX
FROM SURPLUS_MONTH
WHERE YTD = Right([Forms].[Form1]![EndDate], 4) & " YTD"
UNION ALL SELECT SUPERVISOR, NAME, SCALENAME, SumOfCHARGES, SumOfCONSDOLR, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX
FROM TRFALL_MONTH
WHERE YTD = Right([Forms].[Form1]![EndDate], 4) & " YTD";

It still gives me a dialogue box "Enter Parameter Value" for YTD. It does not seem to be getting it from the form.

 

[SimonB1978]

Hmmm....

Everything seems right to me... Might sound stupid but is your form open?

Maybe others can help you out on this one.

Simon B.

 

[boblarson]

Need to change

[Forms].[Form1]

to

[Forms]![Form1]

 

[accessaspire219]

I changed [Forms].[Form1]![EndDate] to [Forms]![Form1]![EndDate] But it still asks me for the YTD parameter?

 

[SimonB1978]

Is YTD a field in your table or is it calculated "on the fly"? Sounds to me as it is asking for the YTD in the SELECT line...

 

[MSAccessRookie]

Here is what I have

SELECT SUPERVISOR, NAME, SCALENAME, SumOfINVDOLR, SumOfCONSDOLR, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX 
FROM MIOH_MONTH
WHERE YTD = Right([Forms].[Form1]![EndDate], 4) & " YTD"
UNION ALL
SELECT SUPERVISOR, NAME, SCALENAME,  SumOfSALES, SumOfBOS, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX
FROM SERVICE_MONTH
WHERE YTD = Right([Forms].[Form1]![EndDate], 4) & " YTD"
UNION ALL
SELECT SUPERVISOR, NAME, SCALENAME, SumOfSURPDOLL, DUMMY, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX
FROM SURPLUS_MONTH
WHERE YTD = Right([Forms].[Form1]![EndDate], 4) & " YTD"
UNION ALL SELECT SUPERVISOR, NAME, SCALENAME, SumOfCHARGES, SumOfCONSDOLR, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX
FROM TRFALL_MONTH
WHERE YTD = Right([Forms].[Form1]![EndDate], 4) & " YTD";

It still gives me a dialogue box "Enter Parameter Value" for YTD. It does not seem to be getting it from the form.

Two things:

1. What is the DataType of EndDate. It would appear to be a Date by its name, but you are treating it like a String. If it is a date, try converting it with cStr() before taking off the last 4 characters.
2. Try using Forms!Form1!EndDate or [Forms]![Form1]![EndDate] instead of [Forms].[Form1]![EndDate]

Note: I see Bob Larson already beat me to Point #2. I guess that makes it a good suggestion.

 

[boblarson]

What are possible values of YTD in the table?

 

[accessaspire219]

YTD is a field and at the moment it only contains 2009 YTD.
In the parameters tab I have added StartDate and EndDate as Text -- would this be an issue?

 

[accessaspire219]

Does anyone know how to define parameters in a union query? (Like in a normal select query we go into Query>Parameters and then define each parameter and its data type?)
I have a crosstab query that is based of this union query and when I run the cross tab query I get error 3070.
Thanks!

 

[Big Pat]

I think your problem is that you can't use parameters in a crosstab query. I don't know WHY that is, but I seem to recall that it's the case. I had a similar issue once and someone on this site (I forget who) suggested that what you need to do is make your crosstab a make-table query, then run a NEW query, which CAN have parameters, on your new table.

It may mean you have to keep ruuning that make-table query as your data changes but that's how I was advised to get round it and it worked for me.

 

[gemma-the-husky]

not sure if this is germane, but normally a YTD figure would apply to an account statement

so say you select period 4, and you want to see figures for the year to date P1-4

all you need is a totals query to select the relevant records

-----------
if you also need period 4 on its own, as well as the year to date then you end up with a similar thing just for period 4

----------------
then have a third query that brings in the periodquery, and the ytdquery, and joins them on the account number.

---------------
if you want to get extra careful, include another query that lists ALL your accounts, and include this, to make sure that you get all accounts, as some might have transactions in P1-3, but not in P4.


so you end up with a visual query that looks like

mainaccountslistquery leftjoin ytdquery and leftjoin periodquery


------------
this stuff will apply in a transaction review - either your ytd value translates into a date, in which case use the normal dates, or you are storing both a year and period value in your transaction table, in which case use those.