Querie Dilemma

[TEE45SEE]

I have a relatively simple SELECT query which when run it gives me "Runtime error 91"

strSQL = "SELECT * FROM tblMonthlyHomeExpenses" _
& " WHERE DatePart('m',[dteTransDate]) =" & intMonthNumber & ";"

When I take the same query and run it as SQL it does exactly what I would expect

SELECT * FROM tblMonthlyHomeExpenses WHERE DatePart('m',[dteTransDate]) =3;

Could anyone please explain to me what is going on. Thanks

 

[plog]

You've not given us enough information, please post all the VBA code. Is the VBA line above the one being highlighted when the error flags?

Also, how did you get the SQL that you posted above? Did you Debug.Print your VBA? or are you just assuming that's the SQL that the VBA is creating?

 

[tvanstiphout]

The statement is correct, but the way you're running it is not. Show us the VBA context.

Example of how this error can occur:
dim db as dao.database
dim rs as dao.recordset
strSql = "..."
set rs = db.OpenRecordset(strSql, ...) 'OOPS, db was never set. Error 91.

 

[MajP]

91 is an object not set error, not related to the syntax. Your problem is somewhere else.

 

[Gasman]

Please supply error description as well in the future.
Not all of us (certainly me) do not know the error codes to heart.

 

[The_Doc_Man]

On a different note, that was your first post. Hello and welcome to the forum.

Now, as to your problem... I think MajP has correctly pointed the discussion. Tom has given a likely example of a common error made when dealing with manually vs. automatically run queries.

 

[TEE45SEE]

Thanks to each of you for responding and pointing me in the right direction. Have found and corrected with everything working as it should.
Thank you to The_Doc_Man for the welcome
Till the next time.

 

[Mike Krailo]

Well you have to at least tell us what fixed it so that it can help other readers in the future.

 

[Pat Hartman]

@TEE45SEE The logic of your query is incorrect. It will only work if there is NEVER more than ONE year's worth of data in your table. You have to use Year and Month, not just month to select the correct data. Unless of course, you want April from all years in the table.