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

Topic

New Reply

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.

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:

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

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 [time_out] field, which in turn produces an error in my query. I have attempted to add an additional IIF statement to test and see if the [time_out] field has been populated. I could not get it to work out– too complicated for me. I don’t think a SWITCH statement will work, either, since this isn’t your typical nested IIF statement.

Any ideas?

Reply | Quote

Replies

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?)

Reply | Quote

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.

Reply | Quote

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 time_out field defaults to ” ” (8 spaces) when empty.

Hans, I think I’ve answered your questions: I want to ignore those records with no time_out 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.

Reply | Quote

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.

Reply | Quote

Hans–

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

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]))))))

It works, but yours is not as complex. Nested IIF statements make me

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

hunh! I would’ve expected you [of all people!] to have an Access solution to this “problem”

Reply | Quote

Ah, but I use the most appropriate tool for the job. 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. I’ve seen Access apps built as a developer’s journal, but they required too much maintenance and customization for my purposes.

Reply | Quote

I take it that Time_Out is not a date field. Silly me, I just assumed it was.

Reply | Quote