Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Using Function to Provide Criteria to Query (2000)

    I am having difficulty getting a query to run that relies on a return value from a function. The function returns the correct value (in a msgbox) but the recordset does not return any records. When I enter the same number as criteria the query runs properly (having removed the parameter). This is the function I have written
    Function Weekdate(date1) As Integer
    date1 = DatePart("ww", date1)
    date1 = CInt(date1)
    End Function

    It simply takes the week number from a date supplied by the operator. The criteria in the query is =weekdate([date1]), the parmeter is date1 as a date. I figure that the problem is something to do with the data type but cannot resolve it.

    Thanks Peter

  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Function to Provide Criteria to Query (2000)

    You have to return the FUNCTION as the value, not the argument. So your function should have Weekdate=date1. (Really you should mess with the argument, instead set another variable too it.....just a good habit to get into).

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Using Function to Provide Criteria to Query (2000)

    As Drew noted, you aren't returning the value to name of function. Also, DatePart returns a Variant (Integer) so there is no need to use CInt too. I'd modify function to:

    Function Weekdate(Date1 As Date) As Integer
    Weekdate = DatePart("ww", Date1)
    End Function

    Actually, in this case, I wouldn't use function at all. User-defined functions can slow down a query. Why not just use DatePart function in query directly? It returns same results. Example:

    ? DatePart("ww", Date())
    38
    ? Weekdate(Date())
    38

    Also, how are you using this function in query? Here is example from Northwind.mdb using DatePart function with Date parameter for query criteria:

    PARAMETERS [Enter Date:] DateTime;
    SELECT Orders.OrderDate, Orders.OrderID, Orders.CustomerID, DatePart("ww",[OrderDate]) AS Expr1
    FROM Orders
    WHERE (((DatePart("ww",[OrderDate]))=DatePart("ww",[Enter Date:])) AND ((Year([OrderDate]))=Year([Enter Date:])))
    ORDER BY Orders.OrderDate, Orders.OrderID;

    Note that [Enter Date:] is specifically defined as a Parameter (DateTime data type). It's used twice in WHERE clause, to get both the week number and year component from date entered by user. Example: Entering 4/30/1998 for date in above query returns 17 records in my copy of Northwind, where week = 18 (returned by DatePart function) and year = 1998, with OrderDate ranging in value from 4/27/1998 to 5/1/1998.

    As noted using intrinsic VB functions in query will yield better performance than user-defined functions. I'd recommend avoiding user-defined functions in query where its possible to use intrinsic VB function to get same results. See this MSKB article for some additional info:

    ACC2000: Number of Times a Custom Function Runs in a Query

    HTH

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Using Function to Provide Criteria to Query (2000)

    Drew

    Thanks for your advice, I will try it although I am not sure how I use another variable surly that has to be the one specified in the function.

    Peter

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Using Function to Provide Criteria to Query (2000)

    Mark

    Thank you for your reply. The reason I was trying to use a function is that I needed both the week # and the year. So what I have is a calendar that runs the function on the after update event. The selection by the operator of the datein the calendar sets both the week # and year. Is there a way I can do this in the criteria of a query?

    Thanks again for your time and trouble, as you can see I am a novice at access.

    Regards

    Peter

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Function to Provide Criteria to Query (2000)

    You're going to have to use separate functions to return those values to a query. Queries don't have any way to handle ByRef arguments in functions.
    Charlotte

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Using Function to Provide Criteria to Query (2000)

    My previous reply provided an example. I'm not sure how the value returned by the calender (is this a custom control?) is being referenced by the query; I'd recommend assigning date value to textbox on form on calender's AfterUpdate event, and then referencing the textbox in query. Directly referencing a custom control property in a query doesn't work too good. Set textbox's Format property to "Short Date" or other valid date format so there is no ambiguity when its value is interpreted. As noted previously, you don't need a custom function, you can use the VB DatePart function to return both week number and year portion of a given date:

    ? DatePart("ww", Date())
    38
    ? DatePart("yyyy", Date())
    2003

    This can be used in query criteria expression by replacing Date() with parameter that references textbox on form used to capture date value selected in calender control:

    DatePart("ww", [Forms]![Form1]![TextDate])

    DatePart("yyyy", [Forms]![Form1]![TextDate])

    Note that Form1 (or whatever it's named) must be open when query runs. If you post the SQL you are using in query may be able to provide more useful and specifc reply.

    HTH

Posting Permissions

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