DateDiff Limitations

[Knicks]

I am using DateDiff to retrieve amount of Months. This number is later used to divide against a count of patients seen in the date range initially supplied through an input form.

The problem I am having is that by using "M" portion of datediff I can only retrieve whole numbers. It doesn't even round up or down. Is there anyway I can get non whole numbers from using datediff or will I need to use a different function?. I would love to be able to yield say 6.34 months as it would be more accurate for later division. The reason why I am using the "M" portion instead of days is that I have to have included a count of patients going back 15 months from the end date of the inputted date range, thus I think I'm committed to working with months and hopefully fractions of months.

Any help would be appreciated

 

[snyperx3]

if youre going to do fractions of months then you will have to go to the day level of datediff. then use the day level to calculate the fraction of months.


-Pete

 

[Knicks]

I suppose I could take 365 days and divide by 12 and then multiply by 15 to go back 15 months from the end date of my date range (-456.25) it wouldn't be exact but by doing so I could then use datediff in days - I guess ultimately that would be closer to reality than using the M portion of datediff.

Ultimately what I need is:

1. Using a user supplied End Date, I need to determine the beginning date going back 15 months to use as a criteria in an a query. This I believe I have right using:

Between DateAdd("m",-15,[Forms]![frmBCI]![Text6]) And [Forms]![frmBCI]![Text6]

2. Using DateDiff I need to determine the amount of months from user supplied beginning date and user supplied end date. I was using with some error ratio if not a complete inputted month

=DateDiff("m",[Forms]![frmBCI]![Text4],[Forms]![frmBCI]![Text6])

The number of "M" is then used later in to divide. There must be some datepart or other formatting to keep the Month portion? I may be able to convert everything to days....

 

[Knicks]

Would this be accurate?

DateDiff("d",DateAdd("m",-15,[Forms]![frmBCI]![Text6]), [Forms]![frmBCI]![Text6])

I could then use DateDiff("d",[Forms]![frmBCI]![Text4],[Forms]![frmBCI]![Text6])

and then perform my division with both sets in the same date format....

 

[snyperx3]

It SEEMS accurate. You should do the calculation on paper/calculator first and then use your program and verify that it is what you expected. If it isn't then continue this thread. =]


-Pete