473,320 Members | 2,092 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Where IIf and Datediff Statement

I'm pretty new at writing SQL on Access and I need some help writing a
datediff query. Say I have a table called tb_Dates with the following
records:

A B C
1 02/01/05 02/15/05 02/25/05
2 02/02/05 01/31/05 02/15/05
3 02/05/05 -------- 02/16/05
4 02/05/05 02/18/05 --------
5 03/01/05 02/01/05 03/16/05

If A < B then calculated the difference in the number of days between B
and C.
If A > B then calculated the difference in the number of days between A
and C.

The result should be:

1 10
2 13
3 --
4 --
5 15

Any assistance would be greatful.

Edwin

Nov 13 '05 #1
1 2444
SELECT tb_Dates.a, tb_Dates.b, tb_Dates.c,
IIf([a]<[b],DateDiff("d",[b],[c]),DateDiff("d",[a],[c])) AS Diff
FROM tb_Dates;
a b c Diff
01/02/2005 15/02/2005 25/02/2005 10
02/02/2005 31/01/2005 15/02/2005 13
05/02/2005 16/02/2005
05/02/2005 18/02/2005
01/03/2005 01/02/2005 16/03/2005 15
Mark

<ed**************@citigroup.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
I'm pretty new at writing SQL on Access and I need some help writing a
datediff query. Say I have a table called tb_Dates with the following
records:

A B C
1 02/01/05 02/15/05 02/25/05
2 02/02/05 01/31/05 02/15/05
3 02/05/05 -------- 02/16/05
4 02/05/05 02/18/05 --------
5 03/01/05 02/01/05 03/16/05

If A < B then calculated the difference in the number of days between B
and C.
If A > B then calculated the difference in the number of days between A
and C.

The result should be:

1 10
2 13
3 --
4 --
5 15

Any assistance would be greatful.

Edwin
Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: CJM | last post by:
I have an ASP page that lists files and folders in a directory. I'm using a cookie to record the last time this page was visited, and I intend to show links that are created/modified from that date...
8
by: inamori | last post by:
I face that problems 07/01/2003 06/30/2006 ---------> it should be 3 01/01/2003 02/28/2005 --------->could i get 2 years and 2 months 01/01/2003 03/01/2005 ...
6
by: Lofty | last post by:
Hi all. I have to write an app that interacts with mySQL (I really must have done some evil, evil stuff in a previous life to be landed with this!) I need to work out the difference in days...
1
by: intl04 | last post by:
I'm trying to set up a query that will include a new field ('Days until completion') whose value is derived from the DateDiff function. I think I have the syntax correct but am not sure. Days...
4
by: Paolo | last post by:
I am having some problem with a Year Function. I have form on which I have 4 field which indicate dates and an additional form which sums those dates: These are the fields: YEARS...
1
by: PMBragg | last post by:
ORINGINAL Post >Thank you in advance. I'm trying to pull all inventory items from December >of the previous year back to 4 years for my accountant. I know this can be >done, but I'm drawing a...
6
by: kevinjwilson | last post by:
I am trying to get the date difference between two dates but I don't want the function to include weekends in the calculation. Does anyone have an idea on how to make this work?
0
enfuego
by: enfuego | last post by:
The code below give an error of Msg 170, Level 15, State 1, Line 5 Line 5: Incorrect syntax near '='. Msg 170, Level 15, State 1, Line 8 Line 8: Incorrect syntax near '='. I Am not sure why. Plz...
2
by: muddasirmunir | last post by:
i am using vb 6 , i had place two datepicker in form now i want to calcuate differcen of month in two date for this i used the function datediff i had try it withh many syntax but getting error...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.