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.

cell dates
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
Genius123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

ste5anSenior 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
Genius123Author 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
Dale FyeOwner, 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

Open in new window

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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ste5anSenior 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

Open in new window


as

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Genius123Author Commented:
This was the most simple option and it worked.  Thanks for your help.
0
ste5anSenior DeveloperCommented:
btw, as long as only two values are involved you should use the built-in Nz() function.
0
Dale FyeOwner, Developing Solutions LLCCommented:
@Genius123

Don't forget to close-out the question by selecting the response that resolved your issue.
0
Genius123Author Commented:
This was the most simple option and it worked.  Thanks for your help.
0
Dale FyeOwner, 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
ste5anSenior DeveloperCommented:
It's the custom function in my post :)
0
Dale FyeOwner, 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.