# MS ACCESS QUERY CALC DATE DIFF

Hello,

I have a query that gathers start and end dates from several work cells.  Below, I only list two.

I need to record the dwell time days it takes to get from one cell to another.  For example on the first line, from RELEASED TO FAB to CUTTING START would be 72 days.
However, on the second line, it skips the cutting cell, therefore from RELEASED TO FAB to  MACHINING START would be 56 days.

I only listed two cells.  There are six cells total and not every cell is used for every job.

I'm trying to figure out how to approach this perhaps with an iif statement but it's making my head spin.

Any help would be appreciated,
Joel
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior DeveloperCommented:
The query itself shows the data in an unnormalized form. So the question is: How are these values stored? How does your data model look like?
0
Author Commented:
I'm not sure how to answer that.  But if I could something like this, I can solve the issue:

DateFieldIWant:  Max([fielda],[fieldb],[fieldc],[fieldd],[fielde])

and then it would return whichever field had the greatest date.  Does something like this exist?
0
Owner, Developing Solutions LLCCommented:
These two functions might help you accomplish what you are trying to do:

``````Public Function fnMin(ParamArray ArrayOfValues() As Variant) As Variant

Dim varMin As Variant
Dim intLoop As Integer

10        For intLoop = LBound(ArrayOfValues) To UBound(ArrayOfValues)
20            If IsNull(ArrayOfValues(intLoop)) Then
'do nothing
30            ElseIf IsNull(varMin) Or IsEmpty(varMin) Then
40                varMin = ArrayOfValues(intLoop)
50            ElseIf ArrayOfValues(intLoop) < varMin Then
60                varMin = ArrayOfValues(intLoop)
70            End If
80        Next
90        fnMin = varMin

End Function

Public Function fnMax(ParamArray ArrayOfValues() As Variant) As Variant

Dim varMax As Variant
Dim intLoop As Integer

10        For intLoop = LBound(ArrayOfValues) To UBound(ArrayOfValues)
20            If IsNull(ArrayOfValues(intLoop)) Then
'do nothing
30            ElseIf IsNull(varMax) Or IsEmpty(varMax) Then
40                varMax = ArrayOfValues(intLoop)
50            ElseIf ArrayOfValues(intLoop) > varMax Then
60                varMax = ArrayOfValues(intLoop)
70            End If
80        Next
90        fnMax = varMax

End Function
``````
They accept an array of values, ignore NULLs and return either the minimum or maximum value (any data type) from the parameters passed to it.  So:

DateFieldIWant: fnMax([Fielda], [Fieldb], FieldC], {FieldD], [FieldE])

Unfortunately, this is what you are left with as options when your data is not normalized.
0
Senior DeveloperCommented:
Use Coalesce() to get the first non-null column:

``````Option Compare Database
Option Explicit

Public Function Coalesce(ParamArray AValues() As Variant) As Variant

Dim Count As Long

Coalesce = AValues(LBound(AValues()))
For Count = LBound(AValues()) To UBound(AValues())
If Not IsNull(AValues(Count)) Then
Coalesce = AValues(Count)
Exit Function
End If
Next Count

End Function
``````

as

``````DaysSinceRelease: DateDiff("d", [RELEASED TO FAB], Coalesce([CUTTING START], [MACHINING START]))
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
This was the most simple option and it worked.  Thanks for your help.
0
Senior DeveloperCommented:
btw, as long as only two values are involved you should use the built-in Nz() function.
0
Owner, Developing Solutions LLCCommented:
@Genius123

Don't forget to close-out the question by selecting the response that resolved your issue.
0
Author Commented:
This was the most simple option and it worked.  Thanks for your help.
0
Owner, Developing Solutions LLCCommented:
I'm confused, Coalesce doesn't work in Access, and that is the topic area you posted in, so, how can that be the right solution?
0
Senior DeveloperCommented:
It's the custom function in my post :)
0
Owner, Developing Solutions LLCCommented:
Almost no different than my custom functions, which, BTW mimicked was almost exactly what the OP asked for.  He didn't ask for first non null value, he asked for the maximum among an array of values.

DateFieldIWant:  Max([fielda],[fieldb],[fieldc],[fieldd],[fielde])
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.