Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Test for empty field (AXP (2002) SP-1)

    I have a rather complex aggregate query field that I need to make even more comlex. It is becoming too complex for my inexperienced programming mind. <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>

    I use Access to report out of a SQL Server-based database application. I cannot alter the application and therefore my queries need to overcome the many idiosyncrases that exist within this application. This application is used to document incoming and outgoing phone calls in our call center. The query in question produces data on call lengths. Consider the following aggregate expression within this query:

    <font color=blue>Call Time (min): Sum(IIf([Caller_Cd2]="",(DateDiff("s",[time_in],[time_out])/60),(DateDiff("s",[time_in],[time_out])/60)+CInt([caller_cd2])))</font color=blue>

    This field determines whether Caller_Cd2 is populated or not (we use this field to enter a positive or negative value to manually alter the total call time), then calculates the total call time in minutes, summing the entire date range. The problem is that sometimes the application does not populate the <font color=blue>[time_out]</font color=blue> field, which in turn produces an error in my query. I have attempted to add an additional <font color=blue>IIF</font color=blue> statement to test and see if the <font color=blue>[time_out]</font color=blue> field has been populated. I could not get it to work out-- <img src=/S/confused.gif border=0 alt=confused width=15 height=20> too complicated for me. I don't think a <font color=blue>SWITCH</font color=blue> statement will work, either, since this isn't your typical nested <font color=blue>IIF </font color=blue> statement.

    Any ideas?

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

    Re: Test for empty field (AXP (2002) SP-1)

    What would you like to happen if the time_out field is not populated?
    (And if it isn't, is it Null, or "" as seems to be the case with Caller_Cd2, or 0?)

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Test for empty field (AXP (2002) SP-1)

    The problem is, if time_out is not populated, what do you want to do? You could just use the Time_In value by doing this whenever time_out is to be used:
    NZ( time_out, time_in)

    This also eliminates alot of IIF statements.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for empty field (AXP (2002) SP-1)

    Mark--

    I liked your solution, since the only thing I could do would be to ignore the record and not count the time for that call. Your solution would give me a 0:00 call length for that call. However, it does not work since the <font color=blue>time_out</font color=blue> field defaults to " " (8 spaces) when empty.

    Hans, I think I've answered your questions: I want to ignore those records with no <font color=blue>time_out </font color=blue> recorded and the field is neither NULL nor "", but rather " ".

    Thanks for your time. If you have any other suggestions, I am eager to try them.

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

    Re: Test for empty field (AXP (2002) SP-1)

    Try

    Call Time (min): Sum(IIf([time_out]=" ",0,DateDiff("s",[time_in],[time_out])/60)+IIf([Caller_Cd2]="",0,CInt([caller_cd2])))

    If time_out is " ", the value summed is 0; otherwise, the difference between time_in and time_out is taken, plus the integer value of Caller_Cd2 if that is different from a space.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Test for empty field (AXP (2002) SP-1)

    I take it that Time_Out is not a date field. Silly me, I just assumed it was.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for empty field (AXP (2002) SP-1)

    Hans--

    Thanks for you help. Before I got your response, I worked out the following logic:

    <font color=blue>Call Time (min): Sum(IIf([time_out]="",0,(IIf([caller_cd2]="",(DateDiff("n",[time_in],[time_out])),(DateDiff("n",[time_in],[time_out])+CInt([caller_cd2]))))))</font color=blue>

    It works, but yours is not as complex. Nested IIF statements make me <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  8. #8
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for empty field (AXP (2002) SP-1)

    FWIW, i've found Excel useful as a tool to develop complex queries. Expression 1 in column C, expression 2 in D, etc. Then I combine them such that I have one stmt that gives the same answer as the series of simpler stmts. Unfortunately, I havent found a way to adequately document what winds up in the app...

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

    Re: Test for empty field (AXP (2002) SP-1)

    I keep a log in TextPad and detail object by object what I did. I break each log up into sections for MODULES, TABLES, QUERIES, FORMS and REPORTS and the stick in the details for each object in that section that I worked on. I create a separate log file for each day and save them all as a workspace. I like the fact that TextPad has a search feature that allows you to search through all the text files in a folder to find a particular string and returns the results as a document. Makes it very handy for going back and finding out what you did in a hurry. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for empty field (AXP (2002) SP-1)

    hunh! I would've expected you [of all people!] to have an Access solution to this "problem" <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

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

    Re: Test for empty field (AXP (2002) SP-1)

    Ah, but I use the most appropriate tool for the job. <img src=/S/yep.gif border=0 alt=yep width=15 height=15> I don't need this in Access, I like the loose structure of the text file, and I don't have time to create an Access app that would give me what I would require of it. With the text files, I can print them out and store them in a file folder, put them in the indiivudal directories with the projects I work on, or do whatever else makes me happy at the moment. I don't need statistics or pretty reports, and I like the low overhead in terms of resources. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I've seen Access apps built as a developer's journal, but they required too much maintenance and customization for my purposes. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

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