Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    passing variables to a function (2000)

    In my query i had.. <font color=blue>Numdays: DeltaDays([Forms]![Switchboard]![Begin_Date],[Forms]![Switchboard]![Finish_Date])</font color=blue>

    and in my Function, i had > <font color=blue>Public Function Deltadays(StartDate As Date, EndDate As Date) As Integer</font color=blue>

    I need to pass one more date, and a text string to the function, so i added... >

    Numdays: <font color=red>DeltaDays(Forms!Switchboard!Begin_Date,F orms!Switchboard!Finish_Date,[datelc],[discounted])</font color=red>

    <font color=red>Public Function Deltadays(StartDate As Date, EndDate As Date, LcDate As Date, disc As String) </font color=red> (I removed the As integer, which gave the same error)




    The first statements work, the added bits returns this error:

    This expression is typed incorrectly, or is too complect to be evaluated etc etc etc

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

    Re: passing variables to a function (2000)

    Can you post the new DeltaDays function? Without knowing the code, it is hard to say what is happening.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing variables to a function (2000)

    Its the one i was working on for days available Hans, as below. I haven't used these arguements passed to it yet in the module.
    I need to use LcDate (date) and disc (text field)

    <font color=blue>Public Function Deltadays(StartDate As Date, EndDate As Date, LcDate As Date, disc As String)

    Dim rstHolidays As Recordset
    Dim Idx As Long
    Dim MyDate As Date
    Dim Numdays As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1
    Dim dbs As Database

    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

    NumSgn = Chr(35)

    MyDate = Format(StartDate, "Short Date")

    For Idx = CLng(StartDate) To CLng(EndDate)

    Select Case (Weekday(MyDate))
    Case Is = 1 ' Sunday
    ' do nothing
    Case Is = 7 ' Saturday
    ' do nothing
    Case Else ' Normal Workday
    strCriteria = "[HoliDate] = " & NumSgn & MyDate & NumSgn
    rstHolidays.FindFirst strCriteria
    If (rstHolidays.NoMatch) Then
    Numdays = Numdays + 1
    Else
    ' do nothing
    End If
    End Select

    MyDate = DateAdd("d", 1, MyDate)
    Next Idx

    Deltadays = Numdays

    End Function</font color=blue>

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

    Re: passing variables to a function (2000)

    Hmm, there's nothing there to make me suspicious. If you're willing to experiment, could you try calling the function with constant arguments in the query?

    Numdays: DeltaDays(Forms!Switchboard!Begin_Date,Forms!Switc hboard!Finish_Date,0,"")

    Does this raise an error?

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing variables to a function (2000)

    Hans,
    If i use, <font color=blue>0,"" </font color=blue> at the end , i don't get an error. the query works. but if i try this i get an error:-

    <font color=blue>Numdays: DeltaDays([Forms]![Switchboard]![Begin_Date],[Forms]![Switchboard]![Finish_Date],[datelc],"")</font color=blue>

    [datelc] is a field in the query, its not calculated, its taken from a table.

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

    Re: passing variables to a function (2000)

    OK, and what if you use

    Numdays: DeltaDays(Forms!Switchboard!Begin_Date,Forms!Switc hboard!Finish_Date,0,[discounted])

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing variables to a function (2000)

    <font color=blue>Numdays: DeltaDays([Forms]![Switchboard]![Begin_Date],[Forms]![Switchboard]![Finish_Date],0,[discounted])</font color=blue>

    works Hans ! <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> no error...

    I have checked Datelc, and it is a Date/Time field in the table.

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

    Re: passing variables to a function (2000)

    Strange. I'll have to think about it, but I won't be back until later tonight. Anyone else with bright ideas?

  9. #9
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing variables to a function (2000)

    One thing that might be worth a try is to change the "LcDate As Date" in the Function to a variant data type and step through it to double check what value is actually being passed.

    HTH

    Peter

  10. #10
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing variables to a function (2000)

    Peter, as suggested, i changed the data type to Variant, and ran it... it came back ok, no error was generated.

    dunno if this will help, this is the SQL of the query....

    <font color=magenta>SELECT</font color=magenta><font color=blue> [fname] & " " & [sname] AS Name, Sum(nz([sumOfsshift])) AS Sickness, DeltaDays([Forms]![Switchboard]![Begin_Date],[Forms]![Switchboard]![Finish_Date],[datelc],[discounted]) AS Numdays, [Sickness]/[Numdays]*100 AS Sick, 100- <img src=/S/sick.gif border=0 alt=sick width=15 height=15> AS Attend, MEMOID.mr, MEMOID.deptcode, MEMOID.shiftcode, MEMOID.discounted, MEMOID.datelc, MEMOID.discounted, MEMOID.memoid, MEMOID.ies, MEMOID.fname, MEMOID.sname
    <font color=magenta>FROM</font color=magenta> Sick_Totals_Feed1 RIGHT JOIN MEMOID ON Sick_Totals_Feed1.memoid = MEMOID.memoid
    <font color=magenta>GROUP BY </font color=magenta> [fname] & " " & [sname], DeltaDays([Forms]![Switchboard]![Begin_Date],[Forms]![Switchboard]![Finish_Date],[datelc],[discounted]), MEMOID.mr, MEMOID.deptcode, MEMOID.shiftcode, MEMOID.discounted, MEMOID.datelc, MEMOID.discounted, MEMOID.memoid, MEMOID.ies, MEMOID.fname, MEMOID.sname
    <font color=magenta>HAVING</font color=magenta> (((MEMOID.discounted)=No) AND ((MEMOID.ies)<>"NONE"))
    <font color=magenta>ORDER BY </font color=magenta> Sum(nz([sumOfsshift])) DESC;</font color=blue>

  11. #11
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing variables to a function (2000)

    looks an appropriate smiley in the middle of your SQL :-)

    Did you put a break point in the function and check the value of Date passed?

  12. #12
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing variables to a function (2000)

    I will have a go... no real experience of using them..

    Update:

    If i place a stop in the code, just after the Public Function bit... and then see what i get...

    LcDate (as variant) is 10 characters long, but it looks like a date
    StartDate and EndDate are 8 characters long, and they are dates also.

    [discounted] which i have as a string, is actually a YES/NO type of field in the table.

    I checked the MEMOID table again, and datelc is definately of the DATE/TIME variety, (Long Date)

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: passing variables to a function (2000)

    This is besides the point, but at the end of your function, you should set to Nothing the variables dbs and rstHolidays.

    Pat

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

    Re: passing variables to a function (2000)

    Peter (Bat17) has come up with the same suggestion I was going to give. If LcDate is null (empty) in one or more records, the function will balk, because it doesn't recognise Null as a valid date. I suppose that caused the error message. Making the argument a Variant instead of a Date works around this, because Null *is* a valid Variant value.

    When you are going to use this argument inside the function, you may want to handle the Null case separately:

    ...
    If IsNull(LcDate) Then
    ' Do something
    Else
    ' Do something else
    End If
    ...

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing variables to a function (2000)

    And shouldn't these:
    ----------------------------------------------
    Dim rstHolidays As Recordset
    Dim dbs As Database
    ---------------------------------------------------
    --------------------------------------------------
    Be written like this?
    --------------------------------------------------
    Dim rstHolidays As DAO.Recordset
    Dim dbs As DAO.Database
    ------------------------------------------------

Page 1 of 2 12 LastLast

Posting Permissions

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