Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2008
    Posts
    25

    Unanswered: SQL statement help

    Having toruble doing this in access 2003:

    SELECT......., IIf([Days Opened]>[SLA Agreement],"Yes","No") AS [Has Meet SLA]
    FROM tbleName.....

    Specifically my issue is with:

    IIf([Days Opened]>[SLA Agreement],"Yes","No") AS [Has Meet SLA]

    I am getting an 'out of disk space in temp location error' I know I am not out of disk space. Does it matter if there are no values in some of the 'Days Opened' or 'SLA Agreement' fields? Any assitance getting this working while allowing empty field values would be great. NOTE they are both number data types.

    TYIA

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You could try this to dodge Nulls:

    IIf(Nz([Days Opened],0)>Nz([SLA Agreement],0),"Yes","No") AS [Has Met SLA]

    If it works, it means that nulls were the problem.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2008
    Posts
    25
    HI StarTrekker I have been struggling with this for a while now. I ended up getting this working:

    SWITCH
    (
    [Days Opened] <= TBL_SLA_Activity.[Number of Days] , 'YES',
    [Days Opened] > TBL_SLA_Activity.[Number of Days] , 'NO',
    TRUE, 'N/A'
    ) AS [Has Meet SLA]

    Thankyou for your time. May I trouble you for a question...

    I have this in my query (you may remember helping me with the mmmm.maxdate part of it):

    datediff("d",[DateTime],[mmmm.maxdate]) AS [Days Opened]

    I would like to exclude any sat or sun in the date range. Any ideas on this?

    TYIA

  4. #4
    Join Date
    Dec 2008
    Posts
    25
    So I found a post which sugests creating a module with the following code:

    Function CalcWorkdays(StartDate, EndDate) As Integer

    Dim LTotalDays As Integer
    Dim LSaturdays As Integer
    Dim LSundays As Integer

    On Error GoTo Err_Execute

    CalcWorkdays = 0

    If IsDate(StartDate) And IsDate(EndDate) Then
    If EndDate <= StartDate Then
    CalcWorkdays = 0
    Else
    LTotalDays = DateDiff("d", StartDate - 1, EndDate)
    LSaturdays = DateDiff("ww", StartDate - 1, EndDate, 7)
    LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)

    'Workdays is the elapsed days excluding Saturdays and Sundays
    CalcWorkdays = LTotalDays - LSaturdays - LSundays

    End If
    End If

    Exit Function

    Err_Execute:
    'If error occurs, return 0
    CalcWorkdays = 0

    End Function

    I saved it as 'CalcWorkdays' and called it like so:

    CalcWorkdays([DateTime],[mmmm.maxdate]) AS [Week Days Opened]

    It worked very nicely. After lunch I am getting:

    Undefined function 'CalcWorkdays' in expression

    So frustrating, amy ideas??

    TYIA

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Mods would say, new problem, new topic

    Does it compile ok?

    Also try changing it from Function CalWorkDays() to Public Function CalcWorkDays()
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Dec 2008
    Posts
    25
    Understadnt his should have been a noew topic, sorry bout that.

    I placed 'Public' in there however same result. Here is my full query:

    SELECT Format([DateTime],"dd/mm/yyyy") AS [Date Time], TBL_Support_Call.Ref, TBL_Support_Call.[Issue Summary], TBL_Support_Call.Comments, TBL_FollowUp.Comments AS [Follow up Comments], TBL_Support_Call.[User Name], TBL_Support_Call.BU, TBL_Support_Call.Issue, TBL_Support_Call.Link, TBL_Support_Call.Category, TBL_Support_Call.[Functional Area], TBL_Support_Call.[Contact Source], TBL_Support_Call.Status, Format([mmmm.maxdate],"dd/mm/yyyy") AS [Closed Date], TBL_Support_Call.[Logged BY], TBL_Support_Call.Owner, CalcWorkdays([DateTime],[mmmm.maxdate]) AS [Week Days Opened], SWITCH
    (
    [Week Days Opened] <= TBL_SLA_Activity.[Number of Days] , 'YES',
    [Week Days Opened] > TBL_SLA_Activity.[Number of Days] , 'NO',
    TRUE, 'N/A'
    ) AS [Has Meet SLA]
    FROM ((TBL_Support_Call LEFT JOIN TBL_FollowUp ON TBL_FollowUp.[Original Call Ref] = TBL_Support_Call.Ref) LEFT JOIN TBL_SLA_Activity ON TBL_SLA_Activity.ID = TBL_Support_Call.SLA) LEFT JOIN [SELECT TBL_Call_Staus_History.refID
    , MAX(TBL_Call_Staus_History.CloseDate) AS maxdate
    FROM TBL_Call_Staus_History
    GROUP
    BY refID ]. AS mmmm ON mmmm.refID = TBL_Support_Call.Ref
    WHERE TBL_Support_Call.Status<> 'Delete'
    ORDER BY TBL_Support_Call.Ref DESC;


    NOTE if I remove 'CalcWorkdays([DateTime],[mmmm.maxdate]) AS [Week Days Opened' the query runs fine
    Would be so useful to get working.

    TYIA

Posting Permissions

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