variable in query doesn't work

[aquinox]

Hi,

I got problem with the following query. The problem is that it returns nothing. It's a long query. The one shows below is not the complete one.

strSQL = "SELECT ProjectQuery.PN_Order, ProjectQuery.Proj_ID, ProjectQuery.PS_Name, ProjectQuery.FS_Name, ProjectQuery.[" & year1 & " ], ProjectQuery.[" & year2 & "], ProjectQuery.[" & year3 & "], ProjectQuery.[" & year4 & "], ProjectQuery.[" & year5 & "], ProjectQuery.[" & year6 & "], ProjectQuery.[" & year7 & "], ProjectQuery.[" & year8 & "], ProjectQuery.[" & year9 & "], ProjectQuery.[" & year10 & "], ProjectQuery.Reason_Code, ProjectQuery.Completion_Date, Reason.ReasonDescription, Projects.Complete_Status, Projects.Reason_Code2, Projects.NA_Code, Projects.Reason_Code3, Projects.NA_Site_Code"
strSQL = strSQL & " FROM (((PSQuery INNER JOIN Projects ON PSQuery.PS_ID = Projects.PS_ID) INNER JOIN [Plan Names] ON PSQuery.PN_Code = [Plan Names].PN_Code) INNER JOIN ProjectQuery ON (Projects.Proj_ID = ProjectQuery.Proj_ID) AND ([Plan Names].PN_Code = ProjectQuery.PN_Code)) INNER JOIN Reason ON ProjectQuery.Reason_Code = Reason.ReasonCode"
strSQL = strSQL & " GROUP BY ProjectQuery.PN_Order, ProjectQuery.Proj_ID, ProjectQuery.PS_Name, ProjectQuery.FS_Name, ProjectQuery.[" & year1 & " ], ProjectQuery.[" & year2 & " ], ProjectQuery.[" & year3 & " ], ProjectQuery.[" & year4 & " ], ProjectQuery.[" & year5 & " ], ProjectQuery.[" & year6 & " ], ProjectQuery.[" & year7 & " ], ProjectQuery.[" & year8 & " ], ProjectQuery.[" & year9 & " ], ProjectQuery.[" & year10 & " ], ProjectQuery.Reason_Code, ProjectQuery.Completion_Date, Reason.ReasonDescription, Projects.Complete_Status, Projects.Reason_Code2, Projects.NA_Code, Projects.Reason_Code3, Projects.NA_Site_Code"
strSQL = strSQL & " HAVING (((ProjectQuery.PN_Code) Like '" & myPNID & "') And ((ProjectQuery.[Project Code]) Like '" & myPCode & "') And ((ProjectQuery.Project_Name) Like '" & myPName & "') And ((ProjectQuery.System_Name) Like '" & mySysData & "') And ((ProjectQuery.J_ID) Like '" & myJID & "') And ((ProjectQuery.Project_ID) Like '" & myPID & "') And ((ProjectQuery.Reason_Code) Like '" & myReasonData & "') And ((Projects.Complete_Status) = No) And ((Projects.NA_Code) Like '" & myNA_Acc & "'))"
strSQL = strSQL & " ORDER BY ProjectQuery.PN_Order, ProjectQuery.PN_Code, ProjectQuery.[Project Code], ProjectQuery.Project_Name, ProjectQuery.System_Name, ProjectQuery.Project_ID"

Sorry, it's quite a long query. Anyway, i think it might be due to the fact that i have variables set for years. If i hardcoded the years for example, rather than putting "& year1 &", i put in 2007/8, it works fine. But i want the years to be as flexible as possible without any human intervention. Is there something else that i should try out in order to get this to work?

Thank you in advance

 

[dhookom]

I see lots of place where you have unwanted spaces:

& "[COLOR=Red Yellow] [/color]], ProjectQuery

Do you really have years as field names or are these the results of a crosstab query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aquinox]

Thanks Dhookoom for your reply.

No, years are not field names. But they are obtained thru a calculation as follows

CurrentYear$ = YearInit$ + Right$("00" + Trim(Str$(YearValue)), 2) + "/" + Right$(Trim(Str$(YearValue + 1)), 2)
year1 = CurrentYear$

This is only for year1. In the same way, all the other years are produced.

And they are done before the query takes place.

Any ideas?

Thank you in advance

 

[dhookom]

Did you correct the errors that I pointed out to you?

I have no idea where you are using the expressions with the equal signs from your recent post.

Have you considered creating a crosstab query to get aggregate values by year?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]