Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 723
  • Last Modified:

Date Diff

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
Ange1ia
Asked:
Ange1ia
  • 22
  • 10
  • 7
  • +1
2 Solutions
 
jo_mCommented:
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
 
Ange1iaAuthor Commented:
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
 
jo_mCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Ange1iaAuthor Commented:
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
 
jo_mCommented:

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
 
Ange1iaAuthor Commented:
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
 
Ange1iaAuthor Commented:
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
 
jo_mCommented:
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
 
Ange1iaAuthor Commented:
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
 
Ange1iaAuthor Commented:
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
 
jo_mCommented:
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
 
jo_mCommented:
Hi there

 what version of Access are you in?

jo
0
 
jo_mCommented:

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
 
jo_mCommented:
Angie

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

jo
back soon


0
 
Ange1iaAuthor Commented:
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
 
Ange1iaAuthor Commented:
GOT II!!!
0
 
Ange1iaAuthor Commented:
But wait...
some of the numbers are showing up as like -51.  Why would I get a neg number?
0
 
Ange1iaAuthor Commented:
It's the way they've entered the data I think.

Ar
0
 
Ange1iaAuthor Commented:
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
 
jo_mCommented:
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
 
Gustav BrockCIOCommented:
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
 
jo_mCommented:

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
 
Ange1iaAuthor Commented:
Ok I'm back, I'm going to try that.

a
0
 
GRayLCommented:
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
 
GRayLCommented:
BTW, cactus data is recognized by most of us in Access as the authority on how do do a variety of date calculations
0
 
Ange1iaAuthor Commented:
RunTime Error
Invalid use of null
Below is  highlighted in yellow

 NumWeeks = DateDiff("ww", BegDate, EndDate)
0
 
Ange1iaAuthor Commented:
Leaving work now but will check back from home for your comments
Thanks for all of your help
a
0
 
Gustav BrockCIOCommented:
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
 
Ange1iaAuthor Commented:
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
 
Ange1iaAuthor Commented:
Cactus Data?
0
 
Gustav BrockCIOCommented:
> 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
 
Ange1iaAuthor Commented:
I'm back at the desk now.  What does Nz mean?
0
 
Gustav BrockCIOCommented:
That's a function to replace Null values.
Please study the on-line help for extended info.

/gustav
0
 
Ange1iaAuthor Commented:
Ok looking into that now
0
 
Ange1iaAuthor Commented:
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
 
Gustav BrockCIOCommented:
That's SQL.
Just open a new query, go to SQL view, paste in the code.

/gustav
0
 
Ange1iaAuthor Commented:
Thanks, I will give it a try and let you know in the this evening or a.m.

a
0
 
Ange1iaAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
Ange1iaAuthor Commented:
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
 
Gustav BrockCIOCommented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 22
  • 10
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now