Start Free Trial

asked on

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.

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

ASKER

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

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

ASKER

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?)

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

ASKER

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

ASKER

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

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

ASKER

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

ASKER

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

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

Hi there

what version of Access are you in?

jo

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

Angie

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

jo
back soon

ASKER

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

ASKER

GOT II!!!

ASKER

But wait...
some of the numbers are showing up as like -51. Why would I get a neg number?

ASKER

It's the way they've entered the data I think.

Ar

ASKER

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

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Ok I'm back, I'm going to try that.

a

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.

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

ASKER

RunTime Error
Invalid use of null
Below is highlighted in yellow

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

ASKER

Leaving work now but will check back from home for your comments
Thanks for all of your help
a

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

ASKER

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

ASKER

Cactus Data?

> 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

ASKER

I'm back at the desk now. What does Nz mean?

That's a function to replace Null values.
Please study the on-line help for extended info.

/gustav

ASKER

Ok looking into that now

ASKER

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?

That's SQL.
Just open a new query, go to SQL view, paste in the code.

/gustav

ASKER

Thanks, I will give it a try and let you know in the this evening or a.m.

a

ASKER

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

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

ASKER

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

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