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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ste5anConnect With a Mentor 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

Open in new window


as

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

Open in new window

0
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Dale FyeCommented:
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
 
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 FyeCommented:
@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 FyeCommented:
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 FyeCommented:
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
All Courses

From novice to tech pro — start learning today.