IF function to show result

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to to a query where if field a is greater than or equal to field b,
the result is a blank, otherwise the result is field b minus field a. Can
someone help me with the proper syntax? The field values are time, if that
makes a difference. Thanks!!
 
cursednomore said:
I need to to a query where if field a is greater than or equal to field b,
the result is a blank, otherwise the result is field b minus field a. Can
someone help me with the proper syntax? The field values are time, if that
makes a difference.
Click to expand...


Us a calculated field in the query:

Dif: IIf([field a] > [field b], Null, DateDiff("n", [field
a], [field b]))

Since the difference of two time values is a duration, you
need to specify what units the result should be. I took a
stab at it and used "n" (minutes).
 
My apologies for not being clearer. Yes, the difference should be shown in
minutes. I used your formula, but did not get the desired result. The two
fields I am using are named "In" and "Start". I need to show the difference
when the time in is greater than (i.e., later) than the start time, and blank
when it is not. Your help is greatly appreciated.

Marshall Barton said:
cursednomore said:
I need to to a query where if field a is greater than or equal to field b,
the result is a blank, otherwise the result is field b minus field a. Can
someone help me with the proper syntax? The field values are time, if that
makes a difference.
Click to expand...


Us a calculated field in the query:

Dif: IIf([field a] > [field b], Null, DateDiff("n", [field
a], [field b]))

Since the difference of two time values is a duration, you
need to specify what units the result should be. I took a
stab at it and used "n" (minutes).
Click to expand...
 
My apologies for not being clearer. Yes, the difference should be shown in
minutes. I used your formula, but did not get the desired result. The two
fields I am using are named "In" and "Start". I need to show the difference
when the time in is greater than (i.e., later) than the start time, and blank
when it is not. Your help is greatly appreciated.
Click to expand...

Please post the actual expression that you used, and what result it's
getting.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
The formula I used was the one posted by Marshall earlier in the thread: Dif:
IIf([field a] > [field b], Null, DateDiff("n", [field
a], [field b])), replacing field a with in, and field b with start. What I
got back was a response of 0:00 when the result was false, and a blank when
the result was true. What I need is the difference when the result is true,
and a blank when the result is false. Any help you can give would be greatly
appreciated.

Many thanks!!
 
cursednomore said:
The formula I used was the one posted by Marshall earlier in the thread: Dif:
IIf([field a] > [field b], Null, DateDiff("n", [field
a], [field b])), replacing field a with in, and field b with start. What I
got back was a response of 0:00 when the result was false, and a blank when
the result was true. What I need is the difference when the result is true,
and a blank when the result is false. Any help you can give would be greatly
appreciated.
Click to expand...


You should not be formatting the result as a time value so
get rid of the text box's Format property. You can not
format the number of minutes (integer) as if it were a
date/time value.

If that doesn't clear things up, please post a copy/paste of
the expression you actually used along with some of your
sample data and the result for each in and out.
 
Here is the actual formula that I used:

Dif: IIf([In]>[shdstrt],Null,DateDiff("n",[in],[shdstrt]))

Here are 5 records of results, which have the test data in them:

EENO FName LName Date ShdStrt ShdEnd ShdLnch ShdHrs In Out Dif
2087 Ken Labenz 10/4/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 8:03:00 AM 6:00:00
PM
2087 Ken Labenz 10/5/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 8:06:00 AM 6:01:00
PM
2087 Ken Labenz 10/6/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 8:03:00 AM 5:34:00
PM
2087 Ken Labenz 10/7/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 7:55:00 AM 5:31:00
PM 5
2087 Ken Labenz 10/8/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 8:03:00 AM 4:06:00
PM

As you may be able to tell, I am getting a "backwards" result. Once again,
many thanks for all your help.

Marshall Barton said:
cursednomore said:
The formula I used was the one posted by Marshall earlier in the thread: Dif:
IIf([field a] > [field b], Null, DateDiff("n", [field
a], [field b])), replacing field a with in, and field b with start. What I
got back was a response of 0:00 when the result was false, and a blank when
the result was true. What I need is the difference when the result is true,
and a blank when the result is false. Any help you can give would be greatly
appreciated.
Click to expand...


You should not be formatting the result as a time value so
get rid of the text box's Format property. You can not
format the number of minutes (integer) as if it were a
date/time value.

If that doesn't clear things up, please post a copy/paste of
the expression you actually used along with some of your
sample data and the result for each in and out.
Click to expand...
 
cursednomore said:
Here is the actual formula that I used:

Dif: IIf([In]>[shdstrt],Null,DateDiff("n",[in],[shdstrt]))

Here are 5 records of results, which have the test data in them:

EENO FName LName Date ShdStrt ShdEnd ShdLnch ShdHrs In Out Dif
2087 Ken Labenz 10/4/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 8:03:00 AM 6:00:00
PM
2087 Ken Labenz 10/5/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 8:06:00 AM 6:01:00
PM
2087 Ken Labenz 10/6/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 8:03:00 AM 5:34:00
PM
2087 Ken Labenz 10/7/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 7:55:00 AM 5:31:00
PM 5
2087 Ken Labenz 10/8/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 8:03:00 AM 4:06:00
PM

As you may be able to tell, I am getting a "backwards" result.
Click to expand...


I don't think "backwards" is what's going on here. I
suspect that your real data values for In and ShdStrt
contain more than just the formatted values displayed in
your sample data.

Is it possible that those time values also contain a date
part? It looks like In Time might be for a different day
than the ShdStrt time?? I'm guessing that you set the
vakues using the Now() function. If you're uncertain about
how those values are set, try removing the Format property
so you can see the entire value.
 
The data was physically input by importing it from an Excel spreadsheet. I
tried formatting the fields as "Short Time", but received the same result.
What I am getting for a result in the query field is the difference between
"In" and "ShdStrt" when "In" is earlier, i.e., if In is 7:58 a.m., and
ShdStrt is 8:00 a.m., I am getting a result of 2. I need it to work the other
way around. Also, is there a way to format the result in the "Short Time"
format, for times when the in is more than an hour later than the Shdstrt?

Thanks again for all the help!

Marshall Barton said:
cursednomore said:
Here is the actual formula that I used:

Dif: IIf([In]>[shdstrt],Null,DateDiff("n",[in],[shdstrt]))

Here are 5 records of results, which have the test data in them:

EENO FName LName Date ShdStrt ShdEnd ShdLnch ShdHrs In Out Dif
2087 Ken Labenz 10/4/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 8:03:00 AM 6:00:00
PM
2087 Ken Labenz 10/5/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 8:06:00 AM 6:01:00
PM
2087 Ken Labenz 10/6/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 8:03:00 AM 5:34:00
PM
2087 Ken Labenz 10/7/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 7:55:00 AM 5:31:00
PM 5
2087 Ken Labenz 10/8/2004 8:00:00 AM 4:00:00 PM 1:00 7:00 8:03:00 AM 4:06:00
PM

As you may be able to tell, I am getting a "backwards" result.
Click to expand...


I don't think "backwards" is what's going on here. I
suspect that your real data values for In and ShdStrt
contain more than just the formatted values displayed in
your sample data.

Is it possible that those time values also contain a date
part? It looks like In Time might be for a different day
than the ShdStrt time?? I'm guessing that you set the
vakues using the Now() function. If you're uncertain about
how those values are set, try removing the Format property
so you can see the entire value.
Click to expand...
 
Dear NoMore:

The first thing is to get some consistency if possible.

Would you prefer for the result in this column to be numeric or text?
You said want b - a in one case, and blank in the other case. You
cannot put both text and numeric values in a column. It will change
the numeric values to be text values if you do. Then, if you ever
sort the column, it won't sort the way you would probably expect.

The thing can be done with an IIf statement:

IIf(a >= b, "", b - a)

I occurs to me perhaps you didn't mean blank, but null. These are
certainly not the same thing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
so you just want to see who clocks in late, and how late they are, in
minutes? try

Dif: IIf([In]>[shdstrt],DateDiff("n",[shdstrt],[in]), Null)

hth


cursednomore said:
The formula I used was the one posted by Marshall earlier in the thread: Dif:
IIf([field a] > [field b], Null, DateDiff("n", [field
a], [field b])), replacing field a with in, and field b with start. What I
got back was a response of 0:00 when the result was false, and a blank when
the result was true. What I need is the difference when the result is true,
and a blank when the result is false. Any help you can give would be greatly
appreciated.

Many thanks!!


John Vinson said:
Please post the actual expression that you used, and what result it's
getting.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
Click to expand...
 
cursednomore said:
The data was physically input by importing it from an Excel spreadsheet. I
tried formatting the fields as "Short Time", but received the same result.
What I am getting for a result in the query field is the difference between
"In" and "ShdStrt" when "In" is earlier, i.e., if In is 7:58 a.m., and
ShdStrt is 8:00 a.m., I am getting a result of 2. I need it to work the other
way around. Also, is there a way to format the result in the "Short Time"
format, for times when the in is more than an hour later than the Shdstrt?
Click to expand...


Ah ha! We reversed the order of the if's results and the
DateDiff args. Try this:

Dif: IIf([in]>[shdstrt], DateDiff("n",[shdstrt],[in]), Null)
 
That did the trick! I actually caught that myself, but thanks for all the
help received from everyone.

Marshall Barton said:
cursednomore said:
The data was physically input by importing it from an Excel spreadsheet. I
tried formatting the fields as "Short Time", but received the same result.
What I am getting for a result in the query field is the difference between
"In" and "ShdStrt" when "In" is earlier, i.e., if In is 7:58 a.m., and
ShdStrt is 8:00 a.m., I am getting a result of 2. I need it to work the other
way around. Also, is there a way to format the result in the "Short Time"
format, for times when the in is more than an hour later than the Shdstrt?
Click to expand...


Ah ha! We reversed the order of the if's results and the
DateDiff args. Try this:

Dif: IIf([in]>[shdstrt], DateDiff("n",[shdstrt],[in]), Null)
Click to expand...
 
Back
Top