Hi,
I am a little stuck with a query I am trying to build. Attemting to calculate the delivery time for a material.
I have managed to utilise the DateDiff function, but have some additional complexities.
1. The dates I am using are to calculate a more correct delivery time is not always populated, therefore I need to utilise a different date field. - DateDiff("d",DTMORD,DATREC) AS Expr1
-
DateDiff("d",DTMORL,DATREC) AS Expr2
I attempted to use a IIf function with this so that if DTMORL is Null, then use DTMORD. But I kept getting errors.
2. I also need to AVG and Group the results by Material, therefore my current query is a little messy. I have 2 columns, 1 for each calculation but really only want to AVG a single result as NULL entries confuse the mix.
Any help appreciated.
Stevo
9  2804 
Hi,
I am a little stuck with a query I am trying to build. Attemting to calculate the delivery time for a material.
I have managed to utilise the DateDiff function, but have some additional complexities.
1. The dates I am using are to calculate a more correct delivery time is not always populated, therefore I need to utilise a different date field.
- DateDiff("d",DTMORD,DATREC) AS Expr1
-
DateDiff("d",DTMORL,DATREC) AS Expr2
I attempted to use a IIf function with this so that if DTMORL is Null, then use DTMORD. But I kept getting errors.
2. I also need to AVG and Group the results by Material, therefore my current query is a little messy. I have 2 columns, 1 for each calculation but really only want to AVG a single result as NULL entries confuse the mix.
Any help appreciated.
Stevo
1) It would help to know what iif statement you tried to use and what error you were getting.
2) The solution to this depends on getting the iif statement correct.
1) It would help to know what iif statement you tried to use and what error you were getting.
2) The solution to this depends on getting the iif statement correct.
Hi, thanks for the quick response. - IIf(IsNull(DTMORL,DateDiff("d",DTMORD,DATREC),DateDiff("d",DTMORL,DATREC),Null) AS DelTime
The error message I receive is: Syntax error (missing operator) in query expression 'IIf...
I think I've resolved my own issue by adding () around first object in statement: - IIf(IsNull([DTMORL]),DateDiff("d",DTMORD,DATREC),DateDiff("d",DTMORL,DATREC)) AS DelTime
Yes, that indeed was the problem, with that fixed you should have no problem making the query.
Yes, that indeed was the problem, with that fixed you should have no problem making the query.
This resolved my original problem, but now I encounter another... some date entries were loaded from converted data from a previous legacy system & the DateDiff calculation is correctly reporting negative result.
How can I include the Null values (via the IIf statement) so that if DTMORL is Null, then calculate with DTMORD, but also exclude negative results? If I use a criteria such as >0, then the Null values are already excluded.
My thoughts are to create a subsequery and calculate a DateDiff result for both DTMORL & DTMORD, group result by material number and AVG the DateDiff calculations. Only problem is, I don't really know how to create subquery's!
This resolved my original problem, but now I encounter another... some date entries were loaded from converted data from a previous legacy system & the DateDiff calculation is correctly reporting negative result.
How can I include the Null values (via the IIf statement) so that if DTMORL is Null, then calculate with DTMORD, but also exclude negative results? If I use a criteria such as >0, then the Null values are already excluded.
My thoughts are to create a subsequery and calculate a DateDiff result for both DTMORL & DTMORD, group result by material number and AVG the DateDiff calculations. Only problem is, I don't really know how to create subquery's!
I don't understand. If DTMORL is null and it uses DTMORD instead, you shouldn't have any null values anyways. So there should be no problem using > 0 as the criteria.
I don't understand. If DTMORL is null and it uses DTMORD instead, you shouldn't have any null values anyways. So there should be no problem using > 0 as the criteria.
Sorry for any confusion, where DTMORL has a value the DateDiff function is sometimes calculating a negative result. So, in this case I want to exclude these negative results and use DTMORD for DateDiff calculation instead.
I see, use:
IsNull([DTMORL]) OR DTMORD > DATREC
I see, use:
IsNull([DTMORL]) OR DTMORD > DATREC
Thanks all resolved!!
Although code should actually read: - IsNull(DTMORL) OR (DTMORL>DATREC)
Not a problem, good luck.
Sign in to post your reply or Sign up for a free account.
Similar topics
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 ...
|
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...
|
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...
|
by: Kevin |
last post by:
Group By - Client
Sum - Past:IIF(DateDiff("d",,Date())>30,,0)
Sum - Current:IIF(DateDiff("d",,Date())<31,,0)
Works for positive numbers, but if InvoiceNetTotal = ($4.00) and
InvoiceNetTotal =...
|
by: Adrian |
last post by:
I hit on this problem converting a VB.NET insurance application to C#.
Age next birthday calculated from date of birth is often needed in
insurance premium calculations.
Originally done using...
|
by: Brian Henry |
last post by:
I am trying to find someones age within 2 months of their birthday on the
given date for the run of the application
Dim d_runOn As DateTime = #4/4/2005#
Dim d_bday As DateTime = #6/26/1986#
...
|
by: TofuTheGreat |
last post by:
I did a group search and found hundreds of cases of problems with
DateDiff(). I read through several dozen but they didn not apply to
my situation. Not having the time to read through all of them...
|
by: BUmed |
last post by:
I want to set up a Query to show the diffence between 2 dates and the output to be in months and weeks. So the child is 15months and 2weeks (15/2). I have been try to modify DateDiff Age:...
|
by: ndeeley |
last post by:
Hello!
I have a `job` database, where users add a job (or record) with a start date and end date.
I need to run a query that will show me the number of uncompleted jobs by user - which I'm...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
