Union query parameter problem 1

[barrysprout]

I can’t seem to get a union query to recognise a value in a form control. The code runs on the Click event of a button and basically reads all the month fields in the form and puts them in a single field in a table. I want the date in a specific format and when I run the code below using the Date() function, it works fine.

sqlDat = "SELECT DistID, ""01-"" & 'Jan' & ""-"" & Year(Date()) As [Month] FROM BudImp" & _
" UNION SELECT DistID, ""01-"" & 'Feb' & ""-"" & Year(Date()) FROM BudImp" & _
etc …..

However when I substitute the Date() function for the variable Yr (where Yr= Me!Yr), the variable is not recognised and I get a too few parameters error. Stepping throught the code shows that the value passed to Yr is correct. Is this a syntax problem?

 

[barrysprout]

Sorry I was a bit unclear above. I substituted the Year() function for the variable Yr like so:

sqlDat = "SELECT DistID, ""01-"" & 'Jan' & ""-"" & Yr As [Month] FROM BudImp" & _
" UNION SELECT DistID, ""01-"" & 'Feb' & ""-"" & Yr FROM BudImp" & _
etc …..

 

[PHV]

And what about this ?
sqlDat = "SELECT DistID, '01-Jan-" & Yr & "'As [Month] FROM BudImp" & _
" UNION SELECT DistID, '01-Feb-" & Yr & "' FROM BudImp" & _
etc …..

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886