Select Where variable error

[LarryViles]

In Access 2003 I have the following statement as part of a report. I wish to change the literal '8' to a more user friendly variable (like ThisMonth). Every attempt to do so results in an error message.

rst.Open "SELECT [Birthday Query Sub].[1stResident], DatePart('d',[1stBirthday]) AS Birthdate FROM [Birthday Query Sub] WHERE (((DatePart('m', [1stBirthday])) = 8)) ORDER BY DatePart('d',[1stBirthday]);"

Larry

 

[GingerR]

dim intMonth as integer
intMonth = datepart("m",Date())

substitute intMonth for 8

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PHV]

rst.Open "SELECT [1stResident],Day([1stBirthday]) AS Birthdate FROM [Birthday Query Sub] WHERE Month([1stBirthday])=" & ThisMonth & " ORDER BY 2"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886