#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    41
    Rep Power
    11

    IsNull in MS Access SQL Statement on a date


    Hi,
    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?
    Thanks.
    gpt
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,449
    Rep Power
    4539
    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.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    41
    Rep Power
    11
    Originally Posted by Doug G
    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.
    Thanks for your help Doug.

    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.

    gpt
    Last edited by gpt; August 28th, 2011 at 09:01 AM.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    46
    Rep Power
    5
    Can we Know what was the problem gpt?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    41
    Rep Power
    11
    Originally Posted by dotnetmind
    Can we Know what was the problem gpt?
    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.
    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

IMN logo majestic logo threadwatch logo seochat tools logo