Go Back   Access World Forums > Apps and Windows > Crystal Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-25-2019, 11:06 AM   #1
jedder18
Just Livin the Dream!
 
Join Date: Mar 2012
Posts: 135
Thanks: 31
Thanked 9 Times in 9 Posts
jedder18 is on a distinguished road
formula help

using this formula in a report.
selecting 3/1/19 as beginadmitdate and 3/31/19 endadmitdate
if the fields in report have dates in those ranges in some fashion, the formula works,
however,
the last formula is not calculating the diff if the dates do not fall in any of the above arrays.
ex: 2/19/19 thru 4/15/19 should display 31 days since it's b4 the begindate and after the enddate...
it is calculating the whole time frame as 56 days.
any help, really appreciated.


if isnull ({history_bed_assignment.end_date_of_bed_assignmen t}) or
{history_bed_assignment.end_date_of_bed_assignment }>{?EndAdmitDate}and
{history_bed_assignment.date_of_bed_assignment}>={ ?BeginAdmitDate} then
Datediff("D",{history_bed_assignment.date_of_bed_a ssignment},{?EndAdmitDate}+1)
else
if ({history_bed_assignment.date_of_bed_assignment})< {?BeginAdmitDate} and
{history_bed_assignment.end_date_of_bed_assignment } in {?BeginAdmitDate} to {?EndAdmitDate} then
Datediff("D",{?BeginAdmitDate},{history_bed_assign ment.end_date_of_bed_assignment}+1)
else
if ({history_bed_assignment.date_of_bed_assignment})< ={?EndAdmitDate} and
{history_bed_assignment.end_date_of_bed_assignment }>={?BeginAdmitDate} then
Datediff("D",{history_bed_assignment.date_of_bed_a ssignment},{history_bed_assignment.end_date_of_bed _assignment}+1)
else
if {history_bed_assignment.date_of_bed_assignment}<{? BeginAdmitDate}and
isnull ({history_bed_assignment.end_date_of_bed_assignmen t} )or
{history_bed_assignment.end_date_of_bed_assignment } > {?EndAdmitDate}
then
Datediff("D",{?BeginAdmitDate},{?EndAdmitDate}+1)

jedder18 is offline   Reply With Quote
Old 07-25-2019, 01:59 PM   #2
kevlray
Newly Registered User
 
Join Date: Apr 2010
Location: Central California
Posts: 658
Thanks: 9
Thanked 53 Times in 51 Posts
kevlray will become famous soon enough
Re: formula help

I gave a response on another forum.
kevlray is offline   Reply With Quote
Reply

Tags
arrays , formula , nestedif , parameters

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel formula converted to access formula RobTuby Queries 9 12-01-2014 08:06 AM
Converting an excel formula into access query formula yaoi28lover Queries 15 04-23-2014 12:05 PM
Converting Excel Formula to Access Formula Minock General 1 12-11-2012 02:59 PM
Make array formula from non-adjacent cells to have other formula functionality inside maeda Excel 0 03-04-2009 06:07 AM
Convert formula into formula result halem2 Queries 7 04-06-2007 08:36 AM




All times are GMT -8. The time now is 04:02 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World