Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Date funtion as crieria problem (Acc 97 sr2 on 95b)

    I am trying to build a query that shows records dated in the current month

    SELECT tblCosts.CatID, Sum(tblCosts.Amount) AS SumOfAmount
    FROM tblCosts
    WHERE (((tblCosts.OrderDate)>=CurrentMonth()))
    GROUP BY tblCosts.CatID;

    where

    Function CurrentMonth()
    Dim MDate As Date
    MDate = CDate("01/" & Month(Date) & "/" & Year(Date))
    End Function

    But as long as the criteria has a > sign in it, I get all records regardless of date. If I use = or < I get none at all.

    OBVIOUSLY I have no idea of how to use funtions in criteria properly.

    Could someone kindly point me in the right direction and switch the light one for me please. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    "Heading for the deep end"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date funtion as crieria problem (Acc 97 sr2 on 95b)

    Try this - it doesn't need a user-defined function:

    SELECT tblCosts.CatID, Sum(tblCosts.Amount) AS SumOfAmount
    FROM tblCosts
    WHERE Year(tblCosts.OrderDate) = Year(Date()) And Month(tblCosts.OrderDate) = Month(Date())
    GROUP BY tblCosts.CatID;

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date funtion as crieria problem (Acc 97 sr2 on 95b)

    Note, just FYI: your never set the return value of your function. You must always assign a value to the function name:

    Function MyFunc(ArgsHere)
    ...
    MyFunc = ...
    End Function

    The CurrentMonth function (which is not needed for your query) could look like:

    Function CurrentMonth() As Date
    CurrentMonth = DateSerial(Year(Date()), Month(Date()), 1)
    End Function

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Date funtion as crieria problem (Acc 97 sr2 on 95b)

    Hans,

    Thanks for the "Quick Fix" and also for the explanation. I had wondered how the function value was returned.

    As my function call was not returning a value, was I actually asking for all values greater than nothing?
    This would explain why = and < did not return any records.
    "Heading for the deep end"

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date funtion as crieria problem (Acc 97 sr2 on 95b)

    SQL probably evaluates your function as 0, which as a date is equivalent to December 30, 1899.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •