Defining SQL parameter for Access Query

[Ashe]

I have a fairly complex query where the parameter [Starting Year] is a user input and used in a WHERE statement. I'd like for the [Starting Year] to be picked up from a form and have this statement in the sql:

WHERE ((([Starting Year])=[forms]![frm_startYear]![txtYear] And ([Starting Year]) Between Year([qry_ProjectTracker].[DATE_START]) And Year(IIf(IsDate([qry_ProjectTracker].[DATE_END])

The query is still prompting for [Starting Year], even though the form is open and available. Is there a syntax that I'm missing here? This has worked for me in the past, but my WHERE statements have never been so complicated.

I've also tried putting the call for [Starting Year] ([Starting Year])=[forms]![frm_startYear]![txtYear]) at the beginning of the code, before the SELECT function, but this hasn't worked either.



I'm going to include the whole code down here, in case that helps. Thank you for any hints and ideas you can give me....

PARAMETERS [Starting Year] Short;
SELECT qry_ProjectTracker.PROJECT_NAME, qry_ProjectTracker.DATE_START, qry_ProjectTracker.DATE_END, qry_ProjectTracker.OBJ_HEADING, qry_ProjectTracker.OBJ_TXT, (IIf([Starting Year]+1>=Year(qry_ProjectTracker.DATE_START) And [Starting Year]+1<=Year(qry_ProjectTracker.DATE_START)+4,IIf([Avg2YR]>2,"> 90% complete",IIf([Avg2YR]=2,"66-90% complete",IIf([Avg2YR]<2,"< 65% complete"))))) AS 2YearProjTxt, (IIf([Starting Year]+2>=Year(qry_ProjectTracker.DATE_START) And [Starting Year]+2<=Year(qry_ProjectTracker.DATE_START)+4,IIf([Avg3YR]>2,"> 90% complete",IIf([Avg3YR]=2,"66-90% complete",IIf([Avg3YR]<2,"< 65% complete"))))) AS 3YearProjTxt, (IIf([Starting Year]+3>=Year(qry_ProjectTracker.DATE_START) And [Starting Year]+3<=Year(qry_ProjectTracker.DATE_START)+4,IIf([Avg4YR]>2,"> 90% complete",IIf([Avg4YR]=2,"66-90% complete",IIf([Avg4YR]<2,"< 65% complete"))))) AS 4YearProjTxt, (IIf([Starting Year]+4>=Year(qry_ProjectTracker.DATE_START) And [Starting Year]+4<=Year(qry_ProjectTracker.DATE_START)+4,IIf([Avg5YR]>2,"> 90% complete",IIf([Avg5YR]=2,"66-90% complete",IIf([Avg5YR]<2,"< 65% complete"))))) AS 5YearProjTxt, qry_ProjectTracker.UNIQUE_OBJ_LINK
FROM tbl_perf_measure, qry_ProjectTracker
WHERE ((([Starting Year])=[forms]![frm_startYear]![txtYear] And ([Starting Year]) Between Year([qry_ProjectTracker].[DATE_START]) And Year(IIf(IsDate([qry_ProjectTracker].[DATE_END]),[qry_ProjectTracker].[DATE_END],Now()))) AND (([Starting Year]+4) Between Year([qry_ProjectTracker].[DATE_START]) And Year(IIf(IsDate([qry_ProjectTracker].[DATE_END]),[qry_ProjectTracker].[DATE_END],Now()))))
GROUP BY qry_ProjectTracker.PROJECT_NAME, qry_ProjectTracker.DATE_START, qry_ProjectTracker.DATE_END, qry_ProjectTracker.OBJ_HEADING, qry_ProjectTracker.OBJ_TXT, (IIf([Starting Year]+1>=Year(qry_ProjectTracker.DATE_START) And [Starting Year]+1<=Year(qry_ProjectTracker.DATE_START)+4,IIf([Avg2YR]>2,"> 90% complete",IIf([Avg2YR]=2,"66-90% complete",IIf([Avg2YR]<2,"< 65% complete"))))), (IIf([Starting Year]+2>=Year(qry_ProjectTracker.DATE_START) And [Starting Year]+2<=Year(qry_ProjectTracker.DATE_START)+4,IIf([Avg3YR]>2,"> 90% complete",IIf([Avg3YR]=2,"66-90% complete",IIf([Avg3YR]<2,"< 65% complete"))))), (IIf([Starting Year]+3>=Year(qry_ProjectTracker.DATE_START) And [Starting Year]+3<=Year(qry_ProjectTracker.DATE_START)+4,IIf([Avg4YR]>2,"> 90% complete",IIf([Avg4YR]=2,"66-90% complete",IIf([Avg4YR]<2,"< 65% complete"))))), (IIf([Starting Year]+4>=Year(qry_ProjectTracker.DATE_START) And [Starting Year]+4<=Year(qry_ProjectTracker.DATE_START)+4,IIf([Avg5YR]>2,"> 90% complete",IIf([Avg5YR]=2,"66-90% complete",IIf([Avg5YR]<2,"< 65% complete"))))), qry_ProjectTracker.UNIQUE_OBJ_LINK, (IIf([Starting Year]>=Year(qry_ProjectTracker.DATE_START) And [Starting Year]<=Year(qry_ProjectTracker.DATE_START)+4,IIf([Avg1YR]>2,"> 90% complete",IIf([Avg1YR]=2,"66-90% complete",IIf([Avg1YR]<2,"< 65% complete")))));

[pbaldy]

Then you'd want the form reference in PARAMETERS, not the old bracketed criteria. You'd also want:

FieldName = [forms]![frm_startYear]![txtYear]

where FieldName is the field in the data you want the criteria on.

[Ashe]

I've tried setting this up in the beginning, but I don't think I have the correct syntax.

Here are a couple options I tried:

PARAMETERS [Starting Year] Short AND [Starting Year]=[forms]![frm_StartYear]![txtYear];

or PARAMETERS [Starting Year] Short;
Set [Starting Year]=[forms]![frm_StartYear]![txtYear];


Thanks!

[pbaldy]

You don't want [Starting Year] in there at all, if I'm understanding correctly. Wherever you had that in your original query, you'd replace it with the form reference. Then instead of prompting for the value it will get it from the form.

[Ashe]

[Starting Year] was never in another query or table. This is the first place this parameter is used (or created).

The original data uses a project start date and year 1, year 2, etc. thru year 5. The user can say "I want to look at all projects going on in 2003" (2003 = example [Starting Year]) and the query will do the math to figure out if any of the projects were going on during this time and spit out results for 2003.

Am I making any sense? Sorry that this is so complicated.

[pbaldy]

Oh, it's like a variable you're trying to set? I don't think you'll be able to do that. Try this type of thing:

[forms]![frm_StartYear]![txtYear] Between Year([qry_ProjectTracker].[DATE_START]) And Year(IIf(IsDate([qry_ProjectTracker].[DATE_END]),[qry_ProjectTracker].[DATE_END],Now()))

[Ashe]

That will take me a bit (it's referenced 10-15 times spottily in the code).

Is there a way I could declare it as a variable in the code? Surely sql will allow that. It knows to prompt the user for it when it runs the query...

[pbaldy]

It prompts the user because it's bracketed and not a field in the table. I know of no way to declare it as a variable in SQL. Changing it shouldn't be too hard though. Copy the SQL into Wordpad or something, do a Find/Replace, and copy it back.

[Ashe]

Hi pbaldy,
I made a form for the query to retrieve the Starting Year variable, and reference it in the SQL as [Forms]![frm_StartYear]![txtYear], but it doesn't pick up any of the values. I suspect this is because it is picking up the year as a text string instead of a numeric value. Is there anyway I can declare it as a number or a year in the form or in the SQL statement?

[Ashe]

I'm terribly sorry. It does work, I'm just having some trouble updating the value like I should.

Thank you for your help!

[pbaldy]

Happy to help! You have to remember to tab off the textbox (or hit enter) during testing. Otherwise the value isn't available to the query. I still have a flat spot on my head from that one.