| Author |
Topic |
|
freezeb
Starting Member
7 Posts |
 Posted - 2007-08-26 : 14:20:11
|
| I have a query that I have been using datediff. This is the code so far.Days With QA: IIf(IsNull([QA Review]),"0",DateDiff("d",[FE Review],[QA Review]))The code works fine as long as there is a date in both fields. What I would like it to do is if there is no date use the current date to subtract from thus telling me how long my Turnover Books have been with a certain division in the company. I tried adding ELSE and reworking the code as follows.Days With QA: IIf(IsNull([QA Review]),"0",DateDiff("d",[FE Review],[QA Review]) Else DateDiff("d", [QA Review], Date())This has not worked. Any help would be appreciated? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-26 : 14:26:57
|
QA: IIf(idate([QA Review]) and isdate([FE Review]),DateDiff("d",[FE Review],[QA Review]), "0")
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
freezeb
Starting Member
7 Posts |
Posted - 2007-08-26 : 14:54:10
|
quote:
Originally posted by Peso
QA: IIf(idate([QA Review]) and isdate([FE Review]),DateDiff("d",[FE Review],[QA Review]), "0")
E 12°55'05.25"N 56°04'39.16"
I get an error that idate is not a defined function. I checked my version of VB and it is VB 6.3 and it doesn't list that as a function or reserved word. Is there any other way of doing this without idate? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
freezeb
Starting Member
7 Posts |
|
|
freezeb
Starting Member
7 Posts |
Posted - 2007-08-28 : 12:00:00
|
quote:
Originally posted by freezeb
quote:
Originally posted by jsmith8858
it's a typo, he meant "isdate" .- Jeffhttp://weblogs.sqlteam.com/JeffS
Thanks for the help.
 Having had the time to go through the data after inserting the new code there is still a problem. What I was looking for was a way of taking todays date use the the date() call in and else statement if the field in one of the tables was null. The way the table is setup is as follows. I have 4 columns tracking dates in which my turnover book arrives and is sent to the next group. [TO Coord Review1] [FE Review] [QA Review] [Operations]As the book leaves each department I place a date under that column. What I need is to know how long each dept. is keeping my books. this code only does part of it.Days with ENG: IIf(IsDate([FE Review]) And IsDate([TO Coord Review1]),DateDiff("d",[TO Coord Review1],[FE Review]),"0")What I don't know how to do is add and ELSE statement in here and if the FE column in blank to use Date() to use current date and datediff that from [To Coord Review1] Thus giving me the days so far that have had my book. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-28 : 12:01:39
|
Use nested IIF's.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
freezeb
Starting Member
7 Posts |
Posted - 2007-08-28 : 16:48:50
|
quote:
Originally posted by Peso
Use nested IIF's.
E 12°55'05.25"N 56°04'39.16"
Very new to this, could you explain? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-28 : 16:59:26
|
Are you sure want to pursue a career in programming?IIf(IsDate([TO Coord Review1]), DateDiff("d", [TO Coord Review1], IIf(IsDate([FE Review], [FE Review], NOW)), "0")
E 12°55'05.25"N 56°04'39.16" |
|
|
|
freezeb
Starting Member
7 Posts |
Posted - 2007-08-28 : 17:19:28
|
quote:
Originally posted by Peso
Are you sure want to pursue a career in programming?IIf(IsDate([TO Coord Review1]), DateDiff("d", [TO Coord Review1], IIf(IsDate([FE Review], [FE Review], NOW)), "0")
E 12°55'05.25"N 56°04'39.16"
I tried this but thought I had to use Date(). I am working through what you just sent. Getting and error telling me there a function has the wrong number of aguments. Thanks for the help. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-28 : 17:40:42
|
Sorry, missed a )IIf(IsDate([TO Coord Review1]), DateDiff("d", [TO Coord Review1], IIf(IsDate([FE Review]), [FE Review], NOW)), "0")
E 12°55'05.25"N 56°04'39.16" |
|
|
|
freezeb
Starting Member
7 Posts |
Posted - 2007-08-28 : 18:07:44
|
quote:
Originally posted by Peso
Sorry, missed a )IIf(IsDate([TO Coord Review1]), DateDiff("d", [TO Coord Review1], IIf(IsDate([FE Review]), [FE Review], NOW)), "0")
E 12°55'05.25"N 56°04'39.16"
It gave me something to look at and find anyway. Thank you for all your help. |
|
|
|
|
DateDiff in a Query with else statement