August 27th, 2011, 04:41 PM
IsNull in MS Access SQL Statement on a date
Iím trying to run a query on an Ms Access database in ASP 3.0 on IIS 7 (localhost).
The statement is as follows:
SELECT TOP 5 * FROM Posts RIGHT JOIN Users ON Posts.UserID = Users.UserID
WHERE Users.Valid = True AND Users.Banned = False AND Posts.Validated = -101
AND ISNULL(Posts.PostDate, #2000/01/01#) Between #2011/9/01# And #2011/9/31# ORDER BY Posts.LastModified DESC
The error message I get is ďWrong number of arguments used with function in query expressionĒ and Iím pretty sure it refers to the ISNULL function. If I take out the ď, #2000/01/01#Ē second argument Iíll still get a syntax error in case of no data returned or simply nothing in case there are some records to be returned.
Iíve also tried changing ISNULL with NZ, but it says that itís an undefined function and so is COALESCE!
Trying to clarify, I started out with ďAND Posts.PostDate Between #2011/9/01# And #2011/9/31#Ē and it will work if in the DB there are some records in the date period Iím querying about but if there arenít any records I get an error. Thatís why I added the IsNull (or NZ). Because I thought that it would fix the error.
Does anyone have any idea?
August 27th, 2011, 09:37 PM
Does ISNULL take arguments?
In older Access versions there was an available htmlhelp file with the complete access sql reference. And with older versions I also used COALESCE with Access. COALESCE is a SQL function, IsNull() is an Access VB function though. There is also a SQL Null function: "..WHERE something IS NULL".
I usually try to simplify the query by taking out parts and trying subset queries until the offending statement is found.
I've never been able to appreciate the sublime arrogance of folks who feel they were put on earth just to save other folks from themselves .." - Donald Hamilton
August 28th, 2011, 05:31 AM
Thanks for your help Doug.
Originally Posted by Doug G
Simplifying the query I found out that the issue resided somewhere else!
I'm terribly sorry to have wasted yours and everyone else's time.
Last edited by gpt; August 28th, 2011 at 08:01 AM.
August 29th, 2011, 10:46 AM
Can we Know what was the problem gpt?
August 29th, 2011, 11:46 AM
I'm a bit ashamed to say it but... I'm using a class I found on the net to manage the calendar on my page.
Originally Posted by dotnetmind
I used a function in the class to get the last day in a month but that wasn't properly synchronised with the current month and was generating the wrong number of days. Hence the error on the date, that wasn't clear. I assumed it depended on the fact that there was no data for that period and therefore the NULL issue (I actually had in the past)! Oops