Solved

Date Diff

Posted on 2011-02-16
41
656 Views
Last Modified: 2012-08-13
Some of you may recognize this: I recently asked a question about how to define how many days from a patient's referral date to their appointment date, (I'm talking week days excluding weekends).  I didn't understand the answer.  They recommended using the Date Diff but I didn't understand how to write that out in the qry.  Maybe I'm not asking it correctly or maybe I need to add something to the question in order to get the answer I need.
0
Comment
Question by:Ange1ia
  • 22
  • 10
  • 7
  • +1
41 Comments
 
LVL 2

Expert Comment

by:jo_m
Comment Utility
Hello there

 in your query

Cntwkdays:Datediff("w" ,ReferraldateField Name,ApptDateFieldName)

 the "w"  indicates to the  function that it only wants to count the days between mMonday to Friday

hope this helps

jo
0
 

Author Comment

by:Ange1ia
Comment Utility
my fields are:

Referral Date  and a field for Appointment Date
Where do I write that? Am I putting this in the criteria area?

Angie


 
0
 
LVL 2

Expert Comment

by:jo_m
Comment Utility
Angie  hi

 in your query grid on the top line of one of the columns
Cntwkdays:Datediff("w" ,[Referral Date ],[Appointment Date])


the Field names are in '['  because you have   spaces in  your field names

Just  a quick thought for the future  DATE is a reserved word    so an abbreviation to dt   eg  Appt_dt  will make your life a lot easier

0
 

Author Comment

by:Ange1ia
Comment Utility
So the qry grid shows me this

Fiedl:     dtmRefDate
Table:    TblPtDemographics
Sort:      (it's blank)
Show:    (theres a check mark so this column shows up)
Criteria:  (currently blank... is this where you  mean for me to insert this?)
0
 
LVL 2

Expert Comment

by:jo_m
Comment Utility

Angie  
  hello again  

Could you put this      Cntwkdays:Datediff("w" ,dtmRefDate,  <apptDatefield name goeshere>)
         on the top line   -  Field  

because the result of the function will be in the Cntwkdays  column in your resulting data
BTW. what is the name of your appt Date field

queryGrid.GIF
0
 

Author Comment

by:Ange1ia
Comment Utility
If I replace the field name

Field:  dtmRefDate or if I replace one of the dtmApptDates...

it comes back asking for a Parameter Value for Referral Date then asks for a Parameter Value for Appt Date and I put in 01-01-11 for Ref Date and say... 2-28-11 for Appt Date it gives me an Exp Box in the design view and on the other side of the view it gives me a field for each record reading 1/7/1900.

I'm obviously doing something wrong
0
 

Author Comment

by:Ange1ia
Comment Utility
The name of my data fields are:
Referral Date
and
dtmGITSMDApptDate

dtm2GITSMDApptDate

dtm3GITSMDApptDate

They may be referred to up to 3 MDs for appts

But if I can get one to work I can get them all to work
0
 
LVL 2

Expert Comment

by:jo_m
Comment Utility
Ok

If I have understood you correctly   you are trying to find the  number of working days between referral and appt

 if you go into Sql   view in  new query and copy the text below

SELECT DateDiff("w",[dtmRefDate],[dtmGITSMDapptDate]) AS CntDaysRefToAppt1, DateDiff("w",[dtmRefDate],[dtm2GITSMDapptDate]) AS CntDaysRefToAppt2, DateDiff("w",[dtmRefDate],[dtm2GITSMDapptDate]) AS CntDaysRefToAppt3
FROM tblRefs;

if you then return to your query grid it should look something like



qg2.bmp
0
 

Author Comment

by:Ange1ia
Comment Utility
Below is a file showing you what I get in the Cntwkdays field.

I think I understand now that that Cntwkdays goes in a column all by it's self and then I will replace the [dtmRefDate] & [name of the appt field] which ever one I need to figure.  Right?  But why am I getting the "date (12/30/1899)" in the Cntwkdays field?
DateDiff1.doc
0
 

Author Comment

by:Ange1ia
Comment Utility
Ok, we are crossing over eachother.  
Let me try your last post.  I sent my last post at the same time. So let me try that and I will let you know.  Thanks for your patience.

Angie
0
 
LVL 2

Expert Comment

by:jo_m
Comment Utility
Sorry  about this

  my preview  buttonis submitting  

            the above should  provide you with the   no of days between referral and appt.
            I had to bob a table together and populate it  so  i knew the result would be accurate and the
            field names would  be correct on the screen shot.

 the  Field names row in queries can be used to  create  values when  the query is run   so  

 the Datediff , Dateadd ,  instr,  format  and  many of the other intrinsic functions access has  plus any user defined functions  can be called and  used here.

BTW  when you call a function it returns a  value  that value  in this instance  is the  number of days between Ref and Appt.

 Hope this makes things a little clearer

jo


0
 
LVL 2

Expert Comment

by:jo_m
Comment Utility
Hi there

 what version of Access are you in?

jo
0
 
LVL 2

Expert Comment

by:jo_m
Comment Utility

hello again
Here's the little mdb with  1 tbl and 1 query   this  shows what I am getting

 hope this helps

jo
dbDateDif.mdb
0
 
LVL 2

Expert Comment

by:jo_m
Comment Utility
Angie

 I've just got to feed the   mutlet,   putlet &  wublet  
 AKA   dog ,cat, and husband
;-)

jo
back soon


0
 

Author Comment

by:Ange1ia
Comment Utility
I'm using Access 2003

I'm still getting the date in the Ctn field instead of a single number.  It's showing 12/30/1899 instead of one single number for how many days.  What's up with that.  I've done a copy paste w your formula and I still get it.

a
0
 

Author Comment

by:Ange1ia
Comment Utility
GOT II!!!
0
 

Author Comment

by:Ange1ia
Comment Utility
But wait...
some of the numbers are showing up as like -51.  Why would I get a neg number?
0
 

Author Comment

by:Ange1ia
Comment Utility
It's the way they've entered the data I think.

Ar
0
 

Author Comment

by:Ange1ia
Comment Utility
ok no it's not giving me the right amount of days between ref and appts.  Jan 17th to Jan 26th is not 1 day.
Still working on it.  Maybe I've entered something wrong.  Going back to your DB.  I pretty much just copied and pasted from your db though... I had to change a few data field names but ...

a
0
 
LVL 2

Expert Comment

by:jo_m
Comment Utility
Hi Angie

Stop pulling your hair out   its a bug in 2003 the W returns weeks  so  I am now working on a workaround for you  

talk to you soon

jo
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
Comment Utility
DateDiff alone can in no way do this on its own for you.
Use a function like this:
Public Function ISO_WorkdayDiff( _
  ByVal datDateFrom As Date, _
  ByVal datDateTo As Date, _
  Optional ByVal booExcludeHolidays As Boolean) _
  As Long

' Purpose: Calculate number of working days between dates datDateFrom and datDateTo.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' May be freely used and distributed.

' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen
' 2000-10-03. Constants added.
'             Option for 5 or 6 working days per week added.
' 2008-06-12. Option to exclude holidays from the count of workdays.

  Const cbytWorkdaysOfWeek  As Byte = 5
  ' Name of table with holidays.
  Const cstrTableHoliday    As String = "tblHoliday"
  ' Name of date field in holiday table.
  Const cstrFieldHoliday    As String = "HolidayDate"

  Dim bytSunday             As Byte
  Dim intWeekdayDateFrom    As Integer
  Dim intWeekdayDateTo      As Integer
  Dim lngDays               As Long
  Dim datDateTemp           As Date
  Dim strDateFrom           As String
  Dim strDateTo             As String
  Dim lngHolidays           As Long
  Dim strFilter             As String
  
  ' Reverse dates if these have been input reversed.
  If datDateFrom > datDateTo Then
    datDateTemp = datDateFrom
    datDateFrom = datDateTo
    datDateTo = datDateTemp
  End If
  
  ' Find ISO weekday for Sunday.
  bytSunday = WeekDay(vbSunday, vbMonday)
  
  ' Find weekdays for the dates.
  intWeekdayDateFrom = WeekDay(datDateFrom, vbMonday)
  intWeekdayDateTo = WeekDay(datDateTo, vbMonday)
  
  ' Compensate weekdays' value for non-working days (weekends).
  intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
  intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
  
  ' Calculate number of working days between the two weekdays, ignoring number of weeks.
  lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
  ' Add number of working days between the weeks of the two dates.
  lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))
  
  If booExcludeHolidays And lngDays > 0 Then
    strDateFrom = Format(datDateFrom, "yyyy\/mm\/dd")
    strDateTo = Format(datDateTo, "yyyy\/mm\/dd")
    strFilter = cstrFieldHoliday & " Between #" & strDateFrom & "# And #" & strDateTo & "# And Weekday(" & cstrFieldHoliday & ", 2) <= " & cbytWorkdaysOfWeek & ""
    lngHolidays = DCount("*", cstrTableHoliday, strFilter)
  End If
  
  ISO_WorkdayDiff = lngDays - lngHolidays

End Function

Open in new window


It works correct under any condition.
So, in your query:

SELECT *, ISO_WorkdayDiff([dtmRefDate],[dtmGITSMDapptDate]) AS CntDaysRefToAppt1
FROM TblPtDemographics
0
 
LVL 2

Assisted Solution

by:jo_m
jo_m earned 250 total points
Comment Utility

Hello again
 
I've had to work around the bug so

there's the  function which you must copy into a module

to do this
  press F11        or   click module

  from the top menu  click insert  and then select  MODULE
  you should have a window which has Option Compare Database in it

beneath this  copy the Function below  
Nb  once you have copied click the drop down where it says (Declarations)  this is where you select
your function once it is written



Function DateDiffW(BegDate, EndDate)
   Const SUNDAY = 1
   Const SATURDAY = 7
   Dim NumWeeks As Integer

   If BegDate > EndDate Then
      DateDiffW = 0
   Else
      Select Case Weekday(BegDate)
         Case SUNDAY: BegDate = BegDate + 1
         Case SATURDAY: BegDate = BegDate + 2
      End Select
      Select Case Weekday(EndDate)
         Case SUNDAY: EndDate = EndDate - 2
         Case SATURDAY: EndDate = EndDate - 1
      End Select
      NumWeeks = DateDiff("ww", BegDate, EndDate)
      DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
   End If
End Function

Open in new window

       
Press the save  and close the the Visual Basic Window  

now go to your query
 and change the  column headings  
CntDaysRefToAppt1:DateDiffW([dtmRefDate],[dtmGITSMDapptDate])
CntDaysRefToAppt2,:DateDiffW([dtmRefDate],[dtm2GITSMDapptDate])
CntDaysRefToAppt3:DateDiffw([dtmRefDate],[dtm2GITSMDapptDate])

 I have attached the mdb againwith the details

Hope this sorts it  out

jo



 


dbDateDif.mdb
0
 

Author Comment

by:Ange1ia
Comment Utility
Ok I'm back, I'm going to try that.

a
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
w has always returned weeks.  It is not a bug.  There is no Access function that will return just the number of weekdays between two dates.  You need a user defined function (UDF) for that or a separate table with just numbers and a special query.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
BTW, cactus data is recognized by most of us in Access as the authority on how do do a variety of date calculations
0
 

Author Comment

by:Ange1ia
Comment Utility
RunTime Error
Invalid use of null
Below is  highlighted in yellow

 NumWeeks = DateDiff("ww", BegDate, EndDate)
0
 

Author Comment

by:Ange1ia
Comment Utility
Leaving work now but will check back from home for your comments
Thanks for all of your help
a
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
NumWeeks = DateDiff("ww", BegDate, EndDate)

That will return a difference in weeknumbers, not the difference in (full) weeks.
If the last is what you wish, a function like this can be used:
Public Function DaysSevenDiff( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date) _
  As Long

  ' Returns positive or negative number of intervals
  ' (each interval one week long) from datDate1 to datDate2.
  '
  ' 1999-07-08. Gustav Brock, Cactus Data ApS, Copenhagen.
  
  ' No special error handling.
  On Error Resume Next
  
  DaysSevenDiff = Fix(DateDiff("d", datDate1, datDate2) / 7)

End Function

Open in new window


The error is caused by Null values (empty fields) of your date fields. Exclude these or try with a modification like this:

NumWeeks = IIf(IsNull(BegDate+EndDate), Null, DateDiff("ww", BegDate, EndDate))

/gustav

0
 

Author Comment

by:Ange1ia
Comment Utility
To exclude null values in date fields??? How would I do that? Is there a default that can be set for null fields? Like 00/00/0000?

a
0
 

Author Comment

by:Ange1ia
Comment Utility
Cactus Data?
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
> To exclude null values in date fields??? How would I do that?

Select * From tblYourTable
Where YourStartDateField Is Not Null And YourEndDateField Is Not Null

> Is there a default that can be set for null fields?

Yes, use
  Nz(YourDateField, #somedatevalue#)
but I wouldn't know which date to use for somedatevalue.

/gustav
0
 

Author Comment

by:Ange1ia
Comment Utility
I'm back at the desk now.  What does Nz mean?
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
That's a function to replace Null values.
Please study the on-line help for extended info.

/gustav
0
 

Author Comment

by:Ange1ia
Comment Utility
Ok looking into that now
0
 

Author Comment

by:Ange1ia
Comment Utility
Each pt can be assigned up to 3 MDs so for the quick and easy solution I simply created more than one qury to handle each appointment oppertunity.

qry RefDate vs dtmGITSMD1ApptDate
qry RefDate vs dtmGITSMD2ApptDate
qry RefDate vs dtmGITSMD3ApptDate

I used not null in the appointment field criteria.  That solved that or so it seems but  that's probobly like gum and string i.e. it will work for now but I want to do it the best way and I bet that's a lot more involved and I want to know what that is even though it is involved.  

Heres the issue with that... you all are so smart and know so much I can't wrap my head around the answers sometimes (easy for you difficult for me).  So I think I'm going to award points then go back thru this and break out what I didn't understand, and some of it will be small stuff and you'll probobly roll you eyes but... If I don't understand  I need to ask so I can understand.  For example...

SELECT *, ISO_WorkdayDiff([dtmRefDate],[dtmGITSMDapptDate]) AS CntDaysRefToAppt1
FROM TblPtDemographics    

I didn't understand this.  SELECT what from where and do what with it?  
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
That's SQL.
Just open a new query, go to SQL view, paste in the code.

/gustav
0
 

Author Comment

by:Ange1ia
Comment Utility
Thanks, I will give it a try and let you know in the this evening or a.m.

a
0
 

Author Comment

by:Ange1ia
Comment Utility
Cactus and Jo M, I will repost realated questions becasue I didn't completely understand what to do w the errors or even working w modules.  I've never worked w a Module before.

Cactus I will look online for extended info on Null Value functions.  Do you have any links you suggest?

Thank you for your help and please keep an eye out for future questions from me.  

Oh, what I did was put "not Null" in the Appt Dates that way it only pulled up pts who actually had appts.  

Thanks again.

Angie
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Great!

For Nz, just look up the on-line help.
The major thing to care of is, that if you don't pass a value to replace Null, Nz will return a string even if the field or value it wraps is a numeric.

/gustav
0
 

Author Comment

by:Ange1ia
Comment Utility
cactus data & Jo m,

Becasue I'm not firmiliar with working w Moduals I'm going to re open another question based on the selected solutions.  Will you please look for that.  I'm asking more specifics (in layman's terms) on where and how to place the code you've written out. Then exactly where to put it in the qury.  I think that's an entire question or two in its' self.  Thanks again for all your help.

Angie
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Just open a new module, copy and paste the code into this.
Pick from menu Compile and then Save. Save as, say, basUtils or whatever fits your temper.

That's all.

/gustav
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now